Common Table Expressions: CTE-Grundlagen

1. Was eine Common Table Expression wirklich ist

Eine Common Table Expression, kurz CTE, ist eine benannte, temporaere Ergebnismenge, die innerhalb einer einzelnen SQL-Anweisung mit der WITH-Klausel definiert wird. Anders als eine View existiert eine CTE nur fuer die Dauer der Abfrage, in der sie deklariert ist, und wird danach verworfen. Sie ist kein Objekt im Datenbankschema, sondern eine strukturelle Hilfe, die es erlaubt, eine komplexe Abfrage in benannte, logisch abgegrenzte Schritte zu zerlegen.

Der Kerngedanke hinter einer CTE ist Lesbarkeit durch Benennung. Statt eine Teilabfrage anonym in Klammern zu verschachteln, gibt man ihr mit WITH aktive_kunden AS (...) einen sprechenden Namen, der im Hauptquery wie eine ganz normale Tabelle in FROM oder JOIN verwendet werden kann. Diese Benennung ist der entscheidende Unterschied zu einer Subquery: Der Leser muss nicht erst den Inhalt der Klammern parsen, um zu verstehen, wofuer die Zwischenergebnismenge steht, der Name sagt es bereits.

Alle gaengigen relationalen Datenbanken unterstuetzen die CTE-Syntax nach dem SQL-99-Standard: PostgreSQL seit Version 8.4, MySQL seit Version 8.0, SQL Server seit 2005 und Oracle seit 9i. Die Grundsyntax ist dabei nahezu identisch, was eine CTE zu einer der portabelsten fortgeschrittenen SQL-Techniken macht, die zwischen Datenbanksystemen wechselt, ohne dass die Kernlogik angepasst werden muss.

2. Die WITH-Klausel im Detail

Die Syntax einer CTE beginnt mit dem Schluesselwort WITH, gefolgt vom Namen der Expression, optional einer Liste von Spaltennamen in Klammern, dem Schluesselwort AS und der eigentlichen Abfrage in runden Klammern. Nach der schliessenden Klammer folgt die Hauptabfrage, die die CTE per Namen referenziert, so als waere sie eine Tabelle. Wichtig ist die Reihenfolge: Die WITH-Klausel steht immer ganz am Anfang der Anweisung, vor SELECT, INSERT, UPDATE oder DELETE.

Die optionale Spaltenliste nach dem CTE-Namen ist nuetzlich, wenn die Ausdruecke in der inneren Abfrage keine eindeutigen Namen tragen, etwa bei berechneten Werten wie Aggregationen ohne Alias. Ohne explizite Spaltenliste uebernimmt die CTE die Spaltennamen aus der inneren SELECT-Liste, genau wie bei einer normalen Subquery. Ein Typfehler oder eine mehrdeutige Spalte wird bereits beim Parsen der CTE-Definition erkannt, nicht erst beim Ausfuehren der Hauptabfrage.

-- Basic CTE: named result set used like a table
WITH active_customers AS (
    SELECT customer_id, first_name, last_name, country
    FROM customers
    WHERE status = 'active'
      AND last_order_date >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT country, COUNT(*) AS customer_count
FROM active_customers
GROUP BY country
ORDER BY customer_count DESC;

-- CTE with explicit column list for clarity
WITH monthly_totals (order_month, total_revenue) AS (
    SELECT DATE_TRUNC('month', order_date), SUM(amount)
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT order_month, total_revenue
FROM monthly_totals
WHERE total_revenue > 10000;

3. CTEs gegen verschachtelte Subqueries

Der praktische Vorteil einer CTE zeigt sich am deutlichsten im Vergleich mit tief verschachtelten Subqueries. Eine Abfrage mit drei oder vier ineinander geschachtelten SELECT-Bloecken zwingt den Leser, von innen nach aussen zu denken, waehrend die Ausfuehrungsreihenfolge tatsaechlich von innen nach aussen laeuft, aber der Lesefluss im Code von aussen nach innen geht. Eine CTE kehrt das um: Jeder Schritt wird von oben nach unten deklariert, in genau der Reihenfolge, in der man ueber das Problem nachdenkt, erst die Rohdaten filtern, dann aggregieren, dann verknuepfen.

Ein weiterer Lesbarkeitsgewinn entsteht durch Wiederverwendung innerhalb derselben Abfrage. Eine Subquery, die zweimal benoetigt wird, muss entweder zweimal ausgeschrieben oder in eine View ausgelagert werden. Eine CTE wird einmal definiert und kann im Hauptquery beliebig oft referenziert werden, ohne dass der Code dupliziert wird. Das reduziert nicht nur die Zeilenzahl, sondern auch das Risiko, dass zwei Kopien derselben Logik bei einer spaeteren Aenderung auseinanderlaufen.

Bei der Fehlersuche zahlt sich die Struktur einer CTE zusaetzlich aus: Ein einzelner CTE-Block laesst sich isoliert ausfuehren, indem man ihn kurzzeitig zu einer eigenstaendigen Abfrage macht. Bei verschachtelten Subqueries muss man dagegen die Klammerstruktur manuell aufloesen, um einen inneren Schritt separat zu testen, was bei mehreren Verschachtelungsebenen fehleranfaellig ist.

-- Nested subqueries: hard to read top-down, logic buried in parentheses
SELECT p.product_name, s.total_qty
FROM products p
JOIN (
    SELECT product_id, SUM(qty) AS total_qty
    FROM (
        SELECT oi.product_id, oi.qty
        FROM order_items oi
        JOIN orders o ON o.order_id = oi.order_id
        WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
    ) recent_items
    GROUP BY product_id
) s ON s.product_id = p.product_id
WHERE s.total_qty > 50;

-- Same logic as a CTE chain: each step reads top-down
WITH recent_items AS (
    SELECT oi.product_id, oi.qty
    FROM order_items oi
    JOIN orders o ON o.order_id = oi.order_id
    WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
),
sales_by_product AS (
    SELECT product_id, SUM(qty) AS total_qty
    FROM recent_items
    GROUP BY product_id
)
SELECT p.product_name, s.total_qty
FROM products p
JOIN sales_by_product s ON s.product_id = p.product_id
WHERE s.total_qty > 50;

4. Mehrere CTEs in einer Abfrage kombinieren

Eine einzelne WITH-Klausel kann mehrere CTEs definieren, durch Komma getrennt, wobei jede folgende CTE auf alle zuvor definierten CTEs zugreifen darf. Diese Kettenbildung ist das eigentliche Werkzeug fuer komplexe Auswertungen: Die erste CTE filtert Rohdaten, die zweite aggregiert die gefilterten Daten, die dritte kombiniert das Ergebnis mit einer weiteren Datenquelle, und die Hauptabfrage fasst am Ende alles zusammen. Jeder Schritt bleibt fuer sich genommen einfach und pruefbar.

Wichtig ist die Referenzrichtung: Eine CTE kann nur auf CTEs zugreifen, die vor ihr in der WITH-Klausel stehen, nicht auf spaeter folgende. Diese Einschraenkung erzwingt eine natuerliche Top-Down-Abhaengigkeitsordnung, die der Lesereihenfolge entspricht und zirkulaere Referenzen von vornherein ausschliesst, ausser bei einer explizit als RECURSIVE markierten CTE, die sich selbst referenzieren darf.

In der Praxis ersetzt eine Kette aus mehreren CTEs oft ein ganzes ETL-Skript, das sonst mehrere temporaere Tabellen anlegen wuerde. Der Vorteil gegenueber echten Zwischentabellen: Der Optimierer sieht die gesamte Logik in einer Anweisung und kann in vielen Faellen Filter frueher anwenden, als es bei manuell erzeugten Zwischentabellen moeglich waere.

-- Multiple CTEs chained together, each building on the previous
WITH filtered_orders AS (
    SELECT order_id, customer_id, order_date, amount
    FROM orders
    WHERE order_date >= '2026-01-01'
      AND status <> 'cancelled'
),
customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_spent, COUNT(*) AS order_count
    FROM filtered_orders
    GROUP BY customer_id
),
ranked_customers AS (
    SELECT customer_id, total_spent, order_count,
           RANK() OVER (ORDER BY total_spent DESC) AS spend_rank
    FROM customer_totals
)
SELECT c.first_name, c.last_name, r.total_spent, r.spend_rank
FROM ranked_customers r
JOIN customers c ON c.customer_id = r.customer_id
WHERE r.spend_rank <= 10;

5. Abgrenzung zur rekursiven CTE

Eine gewoehnliche CTE, wie in den bisherigen Beispielen, wird auch als nicht-rekursive CTE bezeichnet: Sie wird einmal ausgewertet und liefert eine feste Ergebnismenge. Davon zu unterscheiden ist die rekursive CTE, eingeleitet mit WITH RECURSIVE, die sich selbst innerhalb ihrer eigenen Definition referenziert und dadurch iterativ wachsende Ergebnismengen erzeugen kann, etwa fuer hierarchische Strukturen wie Kategoriebaeume oder Organigramme.

Fuer die Grundlagen in diesem Beitrag ist wichtig zu wissen, dass jede CTE, die keine Selbstreferenz enthaelt, nicht rekursiv ist, auch wenn das Schluesselwort RECURSIVE in manchen Datenbanken trotzdem vor die gesamte WITH-Klausel geschrieben werden muss, sobald mindestens eine rekursive CTE in derselben Anweisung vorkommt. PostgreSQL und SQL Server behandeln das RECURSIVE-Schluesselwort dabei leicht unterschiedlich, MySQL 8 folgt dem PostgreSQL-Verhalten. Die Details rekursiver CTEs und ihrer Terminierungsbedingungen sind ein eigenstaendiges, tiefergehendes Thema.

6. Materialisierung: wie die Datenbank eine CTE ausfuehrt

Ein haeufiges Missverstaendnis ist die Annahme, eine CTE werde immer als eigenstaendiger Zwischenschritt materialisiert, also physisch berechnet und zwischengespeichert, bevor die Hauptabfrage darauf zugreift. Historisch war das in PostgreSQL vor Version 12 tatsaechlich der Fall: Jede CTE bildete eine Optimierungsgrenze, die sogenannte "Optimization Fence", ueber die hinweg der Planer keine Filter oder Joins verschieben konnte. Seit PostgreSQL 12 wird eine CTE standardmaessig inlined wie eine Subquery, sofern sie nicht rekursiv ist, mehrfach referenziert wird oder Seiteneffekte hat.

PostgreSQL erlaubt seit Version 12 die explizite Steuerung mit MATERIALIZED und NOT MATERIALIZED direkt nach dem AS-Schluesselwort. MATERIALIZED erzwingt die alte Optimization-Fence-Semantik, was sinnvoll ist, wenn eine CTE mehrfach verwendet wird und ihre erneute Berechnung teuer waere. NOT MATERIALIZED erlaubt dem Optimierer, die Grenzen der CTE aufzuloesen und Praedikate von aussen hineinzuschieben, was bei einmaliger Verwendung meist schneller ist.

SQL Server und Oracle haben nie eine feste Optimization Fence fuer CTEs gehabt, sie werden dort typischerweise wie Sichten in den Ausfuehrungsplan integriert und je nach Kostenkalkulation inlined oder materialisiert. Der praktische Rat lautet: Bei Performance-Problemen mit einer CTE immer den tatsaechlichen Ausfuehrungsplan pruefen, statt von einem festen Materialisierungsverhalten auszugehen, da sich dieses zwischen Datenbanksystemen und sogar zwischen Versionen desselben Systems unterscheidet.

-- PostgreSQL 12+: explicit materialization control
WITH expensive_calc AS MATERIALIZED (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT * FROM expensive_calc WHERE total > 1000
UNION ALL
SELECT * FROM expensive_calc WHERE total < 100;

-- NOT MATERIALIZED lets the planner push predicates into the CTE
WITH filtered AS NOT MATERIALIZED (
    SELECT * FROM orders
)
SELECT * FROM filtered WHERE order_id = 42;

7. CTEs vor INSERT, UPDATE und DELETE

Die WITH-Klausel ist nicht auf SELECT-Anweisungen beschraenkt. In PostgreSQL, SQL Server und Oracle kann eine CTE auch vor INSERT, UPDATE oder DELETE stehen, um die Menge der zu bearbeitenden Zeilen zunaechst uebersichtlich zu berechnen, bevor die eigentliche Datenaenderung darauf zugreift. Das ist besonders bei komplexen Bedingungen nuetzlich, bei denen die Filterlogik selbst schon mehrere Joins oder Aggregationen erfordert.

MySQL 8 unterstuetzt CTEs vor UPDATE und DELETE ebenfalls, allerdings mit Einschraenkungen bei der direkten Modifikation derselben Tabelle, die in der CTE referenziert wird. Hier hilft oft ein Join zurueck auf die Zieltabelle ueber den Primaerschluessel. Der Vorteil bleibt in allen Systemen derselbe: Die Selektionslogik wird einmal lesbar formuliert und getrennt von der eigentlichen Aenderungsoperation gehalten, was Code-Reviews von datenveraendernden Anweisungen erheblich erleichtert.

-- CTE before UPDATE: readable selection logic, separate from the write
WITH stale_accounts AS (
    SELECT account_id
    FROM accounts
    WHERE last_login < CURRENT_DATE - INTERVAL '365 days'
      AND status = 'active'
)
UPDATE accounts
SET status = 'inactive'
WHERE account_id IN (SELECT account_id FROM stale_accounts);

-- CTE before DELETE with a join for row-limited deletion
WITH duplicate_rows AS (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
    FROM subscribers
)
DELETE FROM subscribers
WHERE id IN (SELECT id FROM duplicate_rows WHERE rn > 1);

8. Scope und Sichtbarkeit von CTEs

Eine CTE ist ausschliesslich innerhalb der Anweisung sichtbar, in der sie definiert wurde. Sobald die Anweisung beendet ist, existiert die CTE nicht mehr, es gibt keinen persistierten Zustand und keine Moeglichkeit, dieselbe CTE in einer nachfolgenden Anweisung erneut zu referenzieren, ohne sie erneut zu deklarieren. Das unterscheidet eine CTE fundamental von einer temporaeren Tabelle, die ueber mehrere Anweisungen innerhalb einer Session bestehen bleibt.

Innerhalb einer Kette mehrerer CTEs gilt striktes lexikalisches Scoping von oben nach unten: Eine spaetere CTE sieht alle vorherigen, aber keine CTE sieht sich selbst, ausser im rekursiven Fall, und keine CTE sieht eine, die erst spaeter definiert wird. Namenskollisionen mit existierenden Tabellen im Schema werden zugunsten der CTE aufgeloest: Wenn eine CTE denselben Namen wie eine echte Tabelle traegt, gewinnt innerhalb der Abfrage die CTE-Definition, was in der Praxis unbeabsichtigte Verwirrung stiften kann und deshalb vermieden werden sollte.

9. Typische Fehler und Anti-Patterns

Der haeufigste Fehler ist die Annahme, eine CTE sei automatisch performanter als eine Subquery, nur weil sie lesbarer ist. Lesbarkeit und Performance sind zwei unabhaengige Eigenschaften. Eine schlecht geschriebene CTE mit unnoetig frueher Materialisierung kann in aelteren PostgreSQL-Versionen sogar langsamer sein als die aequivalente Subquery, weil der Optimierer keine Praedikate mehr hineinschieben kann. Der zweite haeufige Fehler ist das Verketten von zu vielen CTEs ohne echten Mehrwert, wo jede einzelne CTE nur eine triviale Umbenennung einer Spalte vornimmt, was die Lesbarkeit eher verschlechtert als verbessert.

Ein dritter Fehler betrifft mehrfach referenzierte CTEs, die teure Aggregationen enthalten: Wird eine CTE ohne MATERIALIZED zweimal im Hauptquery verwendet, kann die Datenbank die zugrunde liegende Berechnung zweimal ausfuehren, statt das Ergebnis einmal zu berechnen und wiederzuverwenden. In diesem Fall lohnt sich der explizite Blick in den Ausfuehrungsplan mit EXPLAIN ANALYZE, um zu pruefen, ob eine echte temporaere Tabelle oder eine materialisierte CTE die bessere Wahl waere.

10. Zusammenfassung

Eine Common Table Expression loest ein einziges, aber weitreichendes Problem: Sie macht komplexe Abfragen von oben nach unten lesbar, statt sie in verschachtelten Klammern zu verstecken. Die WITH-Klausel benennt Teilergebnisse, mehrere CTEs lassen sich zu einer Kette verketten, und jede einzelne CTE laesst sich isoliert pruefen und testen. Das ist der zentrale Unterschied zur anonymen Subquery, die nur als Ganzes verstanden werden kann.

Wichtig bleibt, Lesbarkeit und Performance getrennt zu betrachten: Eine CTE ist nicht automatisch schneller, ihr Materialisierungsverhalten unterscheidet sich zwischen Datenbanksystemen und Versionen. Wer eine CTE mehrfach referenziert oder in Performance-kritischem Code einsetzt, sollte den tatsaechlichen Ausfuehrungsplan pruefen, statt sich auf Annahmen zu verlassen. Fuer hierarchische Daten mit Selbstreferenz ist die rekursive CTE mit WITH RECURSIVE der naechste logische Schritt.

11. FAQ: Common Table Expressions