CTE in SQL: Common Table Expressions einfach erklärt

Eine CTE (Common Table Expression) ist ein benanntes Abfrageergebnis, das nur innerhalb eines einzelnen SQL-Statements existiert. Deklariert wird eine CTE mit dem Operator WITH, danach kann man sie über ihren Namen wie eine gewöhnliche Tabelle ansprechen:

MySQL 8.1
WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

Im Kern ist eine CTE eine Unterabfrage, die einen Namen bekommen hat und an den Anfang der Abfrage gezogen wurde. Dieser einfache Kniff verändert die Lesbarkeit grundlegend: Eine komplexe Abfrage wird zu einer Folge verständlicher Schritte.

Eine Unterabfrage, die einen Namen bekam

Suchen wir in der Datenbank eines Lieferdienstes die Bestellungen über dem durchschnittlichen Bestellwert. Mit einer Unterabfrage sieht das so aus:

MySQL 8.1
SELECT order_id, total_amount
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders)
ORDER BY total_amount DESC, order_id
LIMIT 5;

Und nun dasselbe mit einer CTE:

MySQL 8.1
WITH avg_check AS (
    SELECT AVG(total_amount) AS avg_amount FROM orders
)
SELECT order_id, total_amount
FROM orders
WHERE total_amount > (SELECT avg_amount FROM avg_check)
ORDER BY total_amount DESC, order_id
LIMIT 5;

Bei einer so kurzen Abfrage ist der Gewinn noch nicht offensichtlich. Er zeigt sich, sobald es mehr als einen Schritt gibt.

Mehrere CTEs in einer Abfrage

CTEs lassen sich als Kette durch Kommas getrennt deklarieren, und jede folgende darf die vorherigen verwenden. Suchen wir Produkte, die mehr Umsatz bringen als der Durchschnitt ihrer Kategorie:

MySQL 8.1
WITH product_revenue AS (
    SELECT p.category, p.name, SUM(o.total_amount) AS revenue
    FROM orders o
    JOIN products p ON p.product_id = o.product_id
    GROUP BY p.category, p.name
),
category_avg AS (
    SELECT category, AVG(revenue) AS avg_revenue
    FROM product_revenue
    GROUP BY category
)
SELECT pr.name, pr.category, pr.revenue, ROUND(ca.avg_revenue, 2) AS category_avg
FROM product_revenue pr
JOIN category_avg ca ON ca.category = pr.category
WHERE pr.revenue > ca.avg_revenue
ORDER BY pr.category, pr.revenue DESC;

Die Abfrage liest sich von oben nach unten wie ein Rezept: erst den Umsatz pro Produkt berechnen, dann den Kategoriedurchschnitt, am Ende vergleichen. Dieselbe Abfrage mit verschachtelten Unterabfragen müsste man von innen nach außen lesen — und den zweimal benötigten Produktumsatz zweimal ausschreiben. Die Abfrage lässt sich direkt auf der Seite ändern: Drehen Sie den Vergleich im WHERE auf < — und Sie sehen die Produkte, die hinter ihrem Kategoriedurchschnitt zurückbleiben.

CTE, Unterabfrage oder temporäre Tabelle

Alle drei Werkzeuge lösen eine ähnliche Aufgabe: „ein Zwischenergebnis irgendwo ablegen". Der Unterschied liegt in Lebensdauer und Möglichkeiten:

Praktische Faustregel: Unterabfrage für die einmalige Kleinigkeit, CTE für Lesbarkeit und Wiederverwendung innerhalb der Abfrage, temporäre Tabelle, wenn mehrere Abfragen nacheinander das Ergebnis brauchen oder ein Index nötig ist.

Was unterscheidet eine CTE grundsätzlich von einer mit CREATE TEMPORARY TABLE erstellten temporären Tabelle?

Rekursive CTEs

Eine rekursive CTE ist im Standard-SQL der einzige Weg, eine Struktur unbekannter Tiefe zu durchlaufen: ein Organigramm, einen Kategoriebaum, einen Kommentar-Thread. Ein Klassiker in Interviews auf Middle-Niveau.

Beginnen wir mit dem Minimalbeispiel — wir erzeugen die Zahlen 1 bis 5:

MySQL 8.1
WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT n FROM numbers;

Eine rekursive CTE besteht immer aus drei Teilen:

• Anker — SELECT 1 AS n: die Startzeile, wird einmal ausgeführt.
• Rekursiver Teil — SELECT n + 1 FROM numbers: verweist auf die CTE selbst und läuft immer wieder, wobei er jedes Mal die Zeilen des vorherigen Schritts als Eingabe erhält.
• Abbruchbedingung — WHERE n < 5: Sobald der rekursive Teil ein leeres Ergebnis liefert, endet der Durchlauf. Ohne diese Bedingung würde die Abfrage endlos laufen.

Was passiert, wenn man aus einer rekursiven CTE die Abbruchbedingung entfernt?

Eine Hierarchie durchlaufen: das Organigramm

Nun eine echte Aufgabe. Es gibt eine Mitarbeitertabelle, in der jeder auf seine Führungskraft verweist:

MySQL 8.1
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

Die Frage „liste alle Mitarbeiter mit ihrer Hierarchieebene" lässt sich mit einem gewöhnlichen JOIN nicht beantworten: Wir wissen nicht, wie viele Ebenen die Hierarchie hat. Eine rekursive CTE löst sie in einer Abfrage:

MySQL 8.1
WITH RECURSIVE hierarchy AS (
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.employee_id
)
SELECT employee_id, name, level
FROM hierarchy
ORDER BY level, employee_id;

Der Anker fand die Chefin (manager_id IS NULL), der erste Rekursionsschritt ihre direkten Mitarbeiter, der zweite deren Mitarbeiter und so weiter bis ganz unten. Der Abbruch geschieht hier von selbst: Wenn eine Ebene keine Untergebenen hat, liefert der JOIN ein leeres Ergebnis.

Dasselbe Muster funktioniert für Produktkategorien, verschachtelte Menüs, Kommentarbäume — jede Tabelle, in der eine Zeile auf ihren Elternknoten verweist.

Wann eine CTE die Abfrage verlangsamen kann

CTEs dienen der Lesbarkeit, nicht der Geschwindigkeit. Meistens löst der Optimizer eine CTE genauso auf wie eine Unterabfrage, und es gibt keinen Performance-Unterschied. Ein paar Nuancen sollte man aber kennen:

• Materialisierung. Das DBMS kann die CTE komplett in einen temporären Bereich berechnen und erst danach äußere Filter anwenden. PostgreSQL vor Version 12 tat das immer: Eine WHERE-Bedingung außerhalb der CTE wurde nicht nach innen „durchgereicht", und eine CTE über Millionen Zeilen wurde vollständig berechnet. Seit PostgreSQL 12 werden einfach genutzte CTEs in die Abfrage eingebettet; steuern lässt sich das mit MATERIALIZED / NOT MATERIALIZED.
• Mehrfachnutzung. Wird eine CTE in der Abfrage mehrmals referenziert, kann das DBMS sie einmal berechnen und wiederverwenden — ein Plus. Es kann sie aber auch zweimal berechnen: Prüfen Sie den Ausführungsplan mit EXPLAIN.
• Unbegrenzte Rekursion. Ein Fehler in der Abbruchbedingung oder ein Zyklus in den Daten (ein Mitarbeiter ist sein eigener Chef) führt zu endloser Rekursion. MySQL bricht standardmäßig nach 1000 Iterationen ab (cte_max_recursion_depth), PostgreSQL läuft bis an die Ressourcengrenzen — bei unzuverlässigen Daten lohnt sich eine Absicherung wie WHERE level < 100.

Wie weiter

• die Grundsyntax von WITH mit Übungen — in der Lektion Der WITH-Operator;
• wie die Unterabfragen funktionieren, mit denen wir CTEs verglichen haben — in der Lektion Verschachtelte SQL-Abfragen;
• CTEs an echten Aufgaben üben — im SQL-Trainer.