Common Table Expression – Was ist das?
Das Ding mit dem WITH am Anfang heißt eigentlich Common Table Expression oder kurz CTE. Sie sind ein – wie ich finde – oft sehr performantes, unterschätztes Mittel in SQL.
Was ist eine CTE?
Zum Verständnis kann man eine CTE mit einer temporären Tabelle oder einer Tabellenvariable vergleichen. Sie ist erstmal wie eine Tabelle die ich nur für eine einzelne Anfrage verwenden kann. Eine Tabellenvariable hat als Gültigkeit einen kompletten Batch, eine temporäre Tabelle sogar darüber hinaus auch die Lebenszeit der Connection in der sie erstellt wurde. Da stellt sich dann natürlich die Frage: Warum soll ich eine CTE verwenden wenn auch eine Tabellenvariable oder temporäre Tabelle geht? Die erste Antwort wäre Bequemlichkeit. Eine CTE muss ich nicht erst deklarieren oder erstellen und dann inserten bevor ich sie verwenden kann. Sie ist ein Teil meines SQLs. Dadurch wird natürlich wieder deutlich, dass sie auch nur für das SQL verwendbar ist, zu dem sie auch gehört. Der zweite Punkt ist die Performance. CTEs sind meistens sehr performant. Natürlich gilt auch hier, man muss den Einzelfall testen. Bei mir persönlich kommt noch ein dritter Punkt hinzu, wenn ich komplexere SQLs schreiben muss, teile ich die gerne in kleine Häppchen auf, wenn das möglich ist. Die „Zwischenergebnisse“ kann man dann meistens sehr gut mit CTEs weiter verwenden.
Wie verwende ich eine CTE?
Die Syntax an sich ist einfach, es sind nur ein paar Regeln zu beachten:
- In einem Batch muss das Statement vor der CTE immer mit einem Semicolon abgeschlossen werden.
- Nach der CTE muss ein SELECT, UPDATE, DELETE oder INSERT kommen.
- Eine CTE Ist immer unsortiert.
- Eine CTE Ist immer ein SELECT. Ein OUTPUT von Daten ist nicht möglich.
- Es können mehrere CTEs für eine Abfrage definiert werden.
Wie ist nun eine CTE aufgebaut:
WITH NameDerCTE AS(Spalte1, Spalte2, ...) AS ( SELECT Spalte1, Spalte2,... FROM Schema.Tabelle ) SELECT * FROM NameDerCTE;
Die CTE beginnt immer mit einem WITH, gefolgt vom Namen. Werden mehrere CTEs in einer Anfrage genutzt müssen die Namen eindeutig sein, da über den angegebenen Namen in der eigentlichen Query die CTE referenziert wird. Die Spalten der CTE werden in Klammern angegeben. Man kann den Teil der Definition auch weglassen. Was aber auf jeden Fall erforderlich ist, ist das AS. In Klammern wird dann der Ausdruck verwendet, der der eigentliche Kern der CTE ist. Im Nachfolgenden SQL kann dann auf die CTE zugegriffen werden wie auf jede andere Tabelle. Natürlich ist nur ein lesender Zugriff möglich.
Wann wird jetzt eine CTE in der Realität wirklich verwendet?
Ich kann da nur für die Fälle sprechen in denen ich CTEs verwende. Der häufigste Fall ist bei mir tatsächlich, dass ich ein Zwischenergebnis weiter verwenden will.
Eine weitere Möglichkeit ist auch ein vorfiltern von Ergebnissen in einer CTE. Wenn ich eine komplexe Abfrage habe und dort filtern will, kann es manchmal schneller sein innerhalb einer CTE die Keys meiner Ergebnisse zu ermitteln und dann mit diesen Keys die Einschränkung in der komplexen Abfrage vorzunehmen. Das genaue Vorgehen dazu werde ich in einem extra Post nochmal zeigen.
Wenn ich eine Teilergebnis mehrfach in einer Abfrage brauche kann ich das in einer CTE vordefinieren und dann mehrfach damit joinen in der eigentlichen Abfrage.
CTEs können auch in Views verwendet werden. Wenn ich also in einer View mit sowas ähnlichem wie einer temporären Tabelle oder Tabellenvariable arbeiten will, bleibt mir nur die CTE.
Wenn ihr noch weitere Einsatzszenarien habt schreibt die gerne in die Kommentare!
Alles das was ich jetzt beschrieben habe kann man auch anders lösen, beispielsweise mit Subqueries. CTEs sind hier einfach oft weniger Schreibarbeit und einfacher zu lesen. Meiner Erfahrung nach sind sie auch oft performanter. Die größte Stärke der CTEs ist aber eigentlich die Rekursion.
Was sind rekursive CTEs?
Mit CTEs kann man rekursiv arbeiten. Ich will das mal an einem ganz einfachen Beispiel zeigen. Als Ergebnis sollen die Zahlen von 1-20 ausgegeben werden. In jeder Zeile soll immer eine Zahl stehen. Dafür gibt es viele Lösungswege. Wir schauen es uns an Hand einer rekursiven CTE an.
WITH RekursiveCTE(Zahl) AS ( SELECT 1 AS Zahl UNION ALL SELECT Zahl + 1 AS Zahl FROM RekursiveCTE WHERE Zahl < 20 ) SELECT Zahl FROM RekursiveCTE;
Was passiert hier. Im ersten SELECT legen wir die Wurzel unserer Rekursion fest. In unserem Fall die 1 als erste Zahl. Über ein UNION ALL verknüpfen wir das mit dem nächsten Ergebnis. Das zweite SELECT bezieht sich sich dabei immer auf das Ergebnis des Vorgängers. In unserem Fall wird einfach plus eins gerechnet. Damit wir nicht in eine endlose Rekursion laufen wird das ganze über die WHERE Bedingung beendet. Ohne die WHERE Bedingung würden wir auch nicht in eine endlose Rekursion laufen, da die maximale Rekursionstiefe auf 100 begrenzt ist. Mit OPTION (MAXREKUCURSION [Zahl]) kann die Rekursionstiefe erhöht oder verringert werden. Der Code muss dabei am Ende der gesamten Abfrage eingefügt werden.
Im Gegensatz zu einfachen CTEs kann nur eine rekursive CTE pro Abfrage verwendet werden. Der hier gezeigte Fall ist natürlich nicht so realitätsnah, zeigt aber relativ einfach auf wie eine rekursive CTE funktioniert. Ein typisches Einsatzszenario für rekursive CTEs ist es Pfade in Bäumen aufzulisten.
Wenn Daten im einem Baum gespeichert werden, also ein Element Kinder und einen Vater haben kann braucht man häufig den Pfad vom Wurzelknoten bis zum Element X oder genau den umgekehrten Weg. Ein typisches Beispiel sind Kategorien. Sie werden häufig in folgender Form gespeichert.
| KeyKategorie | KeyOberkategorie | NameKategorie |
|---|---|---|
| 1 | NULL | Wurzelkategorie |
| 2 | 1 | Kategorie Ebene 1 |
| 3 | 1 | Kategorie Ebene 1 |
| 4 | 2 | Kategorie Ebene 2 |
Wir haben hier also einen Baum. Kategorie 1 ist der Wurzelknoten. Auf der nachfolgenden Ebene kommen dann die Kategorien 2 und 3. Kategorie 2 hat noch einen Kindknoten mit Kategorie 4. Mit Hilfe einer rekursiven CTE wollen wir jetzt ermitteln zu welchen Oberkategorien Kategorie 4 gehört. Im Beispiel ist das natürlich sehr kompakt. In der Realität sind die Verschachtelungen und die Komplexität der Bäume normalerweise deutlich größer.
WITH KategoriePfad (KeyKategorie, KeyOberkategorie, KatgorieName) AS ( -- Kategorie auf Ebene 2 -- als Wurzel der Rekursion SELECT KeyKategorie, KeyOberkategorie, KategorieName FROM Schema.Kategorie WHERE Kategorie.KeyKategorie = 4 UNION ALL -- Rekursion über JOIN SELECT KeyKategorie, KeyOberkategorie, KategorieName FROM Schema.Kategorie JOIN KategoriePfad ON KategoriePfad.KeyOberkategorie = Kategorie.KeyKategorie ) SELECT * FROM KategoriePfad;
Im ersten Schritt wird die Wurzel der Rekursion definiert. Wichtig hierbei: Wurzel der Rekursion ist nicht gleich die Wurzelkategorie. Als Wurzel der Rekursion wählen wir hier die Kategorie deren Oberkategorien wir ermitteln wollen.
Die Rekursion erfolgt dann über einen JOIN unserer eigentlichen Kategorietabelle mit der CTE. Joinbedingung ist dabei die Verknüpfung von Oberkategorie aus der CTE mit dem Key der Kategorie aus der Kategorietabelle. Die Rekursion läuft so lange bis wir an dem Punkt angekommen sind, dass in der Oberkategorie NULL steht. Damit haben wir die Wurzelkategorie erreicht.
Fazit
Sowohl in der rekursiven Variante als auch einfache CTEs sind häufig eine große Hilfe wenn man SQLs schreibt. Viele Lösungen sind auch ohne CTEs machbar, aber meistens strukturieren CTEs den Code besser und machen ihn performanter und lesbarer.