Common Table Expressions (CTEs)

Wenn deine SQL-Abfragen länger werden und mehrere Verschachtelungsebenen haben, wird der Code schnell unleserlich. Common Table Expressions (CTEs) – im Deutschen oft „gemeinsame Tabellenausdrücke" oder kurz „WITH-Klausel" – sind das wichtigste Werkzeug, um komplexe Abfragen in lesbare Bausteine zu zerlegen.

Eine CTE ist im Wesentlichen eine benannte Subquery, die du am Anfang deiner Abfrage definierst und danach wie eine normale Tabelle ansprichst. Diese Lektion zeigt dir die Syntax, den Unterschied zu Subqueries und das Highlight: rekursive CTEs für hierarchische Daten.

1) Die Grundidee

Statt eine Subquery zu verschachteln:

SELECT ort, AVG(kundenwert)
FROM (SELECT k.ort, k.id, SUM(b.summe) AS kundenwert
       FROM kunden k
       LEFT JOIN bestellungen b ON k.id = b.kunde_id
       GROUP BY k.ort, k.id) AS t
GROUP BY ort;

… schreibst du sie mit CTE:

WITH kundenwerte AS (
  SELECT k.ort, k.id, SUM(b.summe) AS kundenwert
  FROM kunden k
  LEFT JOIN bestellungen b ON k.id = b.kunde_id
  GROUP BY k.ort, k.id
)
SELECT ort, AVG(kundenwert)
FROM kundenwerte
GROUP BY ort;

Beide Abfragen liefern dasselbe Ergebnis. Aber die CTE-Variante liest sich von oben nach unten wie ein Rezept: „Erstelle erst die Tabelle kundenwerte, dann mach damit Folgendes." Verschachtelte Subqueries muss man von innen nach außen lesen – mühsam.

2) Syntax und Aufbau

Die grundsätzliche Struktur:

WITH cte_name AS (
  -- beliebige SELECT-Abfrage
)
SELECT ... FROM cte_name ...;

Wichtig:

• Das WITH kommt vor der eigentlichen Hauptabfrage
• Die CTE wird durch das Schlüsselwort AS und Klammern definiert
• Innerhalb der CTE kann jedes gültige SELECT stehen – inklusive JOINs, GROUP BY, Subqueries
• Nach WITH muss ein SELECT, INSERT, UPDATE oder DELETE folgen – eine CTE allein ist keine vollständige Abfrage
• Die CTE existiert nur für die Dauer dieser einen Abfrage. Sie wird nicht gespeichert.

3) Beispiel-Daten

Für diese Lektion nutzen wir Kunden und ihre Bestellungen sowie eine Mitarbeiter-Hierarchie (für die rekursive CTE später):

4) Erstes praktisches Beispiel

Frage: „Welche Kunden haben einen Gesamtumsatz über 700 €?" In zwei klar getrennten Schritten via CTE:

WITH umsatz_pro_kunde AS (
  SELECT kunde_id, SUM(summe) AS gesamt
  FROM bestellungen
  GROUP BY kunde_id
)
SELECT k.name, k.ort, u.gesamt
FROM kunden k
JOIN umsatz_pro_kunde u ON k.id = u.kunde_id
WHERE u.gesamt > 700
ORDER BY u.gesamt DESC;

5) Mehrere CTEs nacheinander

Du kannst in einer WITH-Klausel mehrere CTEs definieren, durch Komma getrennt. Spätere CTEs können auf frühere zugreifen:

WITH umsatz_pro_kunde AS (
  SELECT kunde_id, SUM(summe) AS gesamt
  FROM bestellungen
  GROUP BY kunde_id
),
top_kunden AS (
  SELECT kunde_id, gesamt
  FROM umsatz_pro_kunde
  WHERE gesamt > 500
)
SELECT k.name, t.gesamt
FROM kunden k
JOIN top_kunden t ON k.id = t.kunde_id;

Das ist wie eine kleine Pipeline:

6) CTE vs. Subquery vs. View

Drei verwandte Konzepte, die oft verwechselt werden:

Subquery und CTE sind funktional fast identisch – beide sind Einmal-Konstrukte. Eine View dagegen ist eine in der Datenbank gespeicherte virtuelle Tabelle, die immer wieder verwendet werden kann.

7) Wann CTE statt Subquery?

8) Performance-Hinweise

Theoretisch könnte eine CTE bei mehreren Verwendungen langsamer sein als eine wiederholte Subquery – Stichwort „Materialisierung". In modernen Datenbanken (PostgreSQL ab 12, MySQL ab 8.0, SQL Server, Oracle) entscheidet der Optimizer aber selbst, ob das Zwischenresultat materialisiert oder inline ersetzt wird. Praktisch: der Unterschied ist meist klein, und Lesbarkeit ist wichtiger.

Ausnahme: rekursive CTEs (gleich) sind notwendigerweise materialisiert und können bei tiefen Hierarchien teurer werden.

9) Rekursive CTEs – Hierarchien aufrollen

Das Highlight: CTEs können sich selbst aufrufen. Das ist die einzige saubere Möglichkeit in SQL, eine unbekannt tiefe Hierarchie abzufragen – etwa „Alle Mitarbeiter unter Eva, beliebig viele Ebenen tief".

Aufbau einer rekursiven CTE:

WITH RECURSIVE hierarchie AS (
  -- Anker-Schritt: Startpunkt der Rekursion
  SELECT id, name, chef_id, 1 AS ebene
  FROM mitarbeiter
  WHERE name = 'Eva (CEO)'

  UNION ALL

  -- Rekursiver Schritt: nimm die nächste Ebene dazu
  SELECT m.id, m.name, m.chef_id, h.ebene + 1
  FROM mitarbeiter m
  JOIN hierarchie h ON m.chef_id = h.id
)
SELECT * FROM hierarchie
ORDER BY ebene, name;

10) So funktioniert die Rekursion

Eine rekursive CTE besteht aus zwei Teilen, verbunden durch UNION ALL:

1. Anker-Schritt: liefert die Startzeilen (hier: Eva). Diese werden in das Zwischenresultat eingetragen.
2. Rekursiver Schritt: nimmt das bisherige Zwischenresultat, joint es mit mitarbeiter und findet die nächste Ebene. Das wird so lange wiederholt, bis keine neuen Zeilen mehr dazukommen.

Das Schlüsselwort RECURSIVE ist nicht in allen Datenbanken nötig – in PostgreSQL und MySQL schon, in SQL Server nicht.

11) Typische Anwendungen rekursiver CTEs

• Organigramm: alle Untergebenen eines Vorgesetzten, beliebig tief
• Kategorien-Baum: alle Unterkategorien eines Bereichs (z. B. Shop-Navigation)
• Stückliste: alle Einzelteile eines zusammengesetzten Produkts (Bauteil → Unterbauteile → Schrauben)
• Pfad-Berechnung: Routen in Graphen-Daten, kürzeste Verbindungen
• Datumsreihen erzeugen: „Liste alle Tage zwischen heute und in 30 Tagen" – ohne dass es eine Tabelle tage braucht

12) Datumsreihe per rekursiver CTE

Ein elegantes Idiom: ohne Hilfstabelle eine Reihe von Datumswerten erzeugen:

WITH RECURSIVE tage AS (
  SELECT CAST('2026-01-01' AS DATE) AS tag
  UNION ALL
  SELECT tag + INTERVAL '1 day'
  FROM tage
  WHERE tag < '2026-01-31'
)
SELECT * FROM tage;

Liefert die 31 Tage im Januar 2026. Praktisch für Reports, in denen auch Tage ohne Bestellungen vorkommen sollen (LEFT JOIN auf tage).

13) CTEs in INSERT, UPDATE, DELETE

Wie Subqueries können CTEs auch DML-Statements vorgeschaltet werden:

WITH inaktive_kunden AS (
  SELECT k.id
  FROM kunden k
  LEFT JOIN bestellungen b ON k.id = b.kunde_id
  WHERE b.kunde_id IS NULL
)
DELETE FROM kunden
WHERE id IN (SELECT id FROM inaktive_kunden);

Erst inaktive Kunden ermitteln, dann löschen. Klar getrennt, leicht zu testen (während der Entwicklung kannst du das DELETE durch SELECT * ersetzen, um zu sehen, was gelöscht würde).

14) Häufige Fehler

• Vergessenes WITH: ohne das Schlüsselwort ist die Abfrage Syntax-Müll
• Fehlende Hauptabfrage: nach WITH … AS (…) muss ein SELECT oder DML-Statement folgen – die CTE allein ist nicht ausführbar
• Komma vergessen zwischen mehreren CTEs (kein AND, kein ;, sondern ,)
• Rekursion ohne Abbruchbedingung: WHERE-Filter im rekursiven Schritt vergessen → DB rennt sich fest, Server bricht ab
• UNION statt UNION ALL bei Rekursion: UNION entfernt Duplikate und kann gewünschte Zeilen verschlucken
• Datentyp-Inkompatibilität zwischen Anker- und rekursivem Schritt: beide müssen exakt dieselben Spaltentypen liefern

15) MySQL-Hinweis

CTEs gibt es in MySQL erst ab Version 8.0. Davor musste man Subqueries oder temporäre Tabellen nutzen. In aktuellen Lehrbüchern wird CTE oft auch nur kurz erwähnt – in der Praxis ist sie aber inzwischen Standard. Bei IHK-Aufgaben mit MySQL solltest du sicherstellen, dass die verwendete Version 8+ ist.

Zusammenfassung

Eine Common Table Expression (CTE) ist eine benannte Subquery, die mit WITH cte_name AS (…) vor der Hauptabfrage definiert wird. Sie macht komplexe Abfragen lesbar, weil sie sich von oben nach unten lesen lassen statt verschachtelt. Mehrere CTEs lassen sich per Komma aneinanderreihen und aufeinander aufbauen. Mit WITH RECURSIVE können CTEs sich selbst referenzieren – das ist die saubere Lösung für hierarchische Daten (Organigramme, Kategoriebäume, Stücklisten). Funktional ist eine CTE einer Subquery ähnlich; ihre Stärke ist Klarheit, nicht Geschwindigkeit.