CTE-Grundlagen

Die Grundform

WITH paid_orders AS (
    SELECT * FROM orders WHERE status = 'paid'
)
SELECT customer_id, sum(total) AS revenue
FROM paid_orders
GROUP BY customer_id;

WITH paid_orders AS (...) definiert eine virtuelle Tabelle namens paid_orders für die Dauer der Query. Im äußeren SELECT wird sie wie eine ganz normale Tabelle benutzt.

Funktional identisch zu einer Subquery in FROM:

SELECT customer_id, sum(total) AS revenue
FROM (
    SELECT * FROM orders WHERE status = 'paid'
) paid_orders
GROUP BY customer_id;

Bei einem einzelnen Filter ist die Subquery kürzer. Sobald die Logik verschachtelter wird (oder mehrfach gebraucht wird), gewinnt die CTE deutlich an Lesbarkeit.

Mehrere CTEs in einer Query

Du kannst mehrere CTEs aneinanderreihen — jeder kann auf vorherige zugreifen:

WITH
paid_orders AS (
    SELECT * FROM orders WHERE status = 'paid'
),
customer_totals AS (
    SELECT
        customer_id,
        sum(total) AS revenue,
        count(*)   AS order_count
    FROM paid_orders
    GROUP BY customer_id
),
top_customers AS (
    SELECT *
    FROM customer_totals
    WHERE revenue > 100
)
SELECT c.name, t.revenue, t.order_count
FROM top_customers t
INNER JOIN customers c ON c.id = t.customer_id
ORDER BY t.revenue DESC;

Liest sich von oben nach unten wie ein Mini-Programm:

1. paid_orders — alle bezahlten Bestellungen
2. customer_totals — pro Kunde aggregiert
3. top_customers — nur die mit Umsatz > 100
4. Outer query — Join mit Kunden-Namen und Sortierung

Im Vergleich zur äquivalenten verschachtelten Subquery-Variante: deutlich klarer welcher Schritt was tut.

CTEs sind keine Variablen

Wichtige Eigenheit: jeder CTE-Verweis erzeugt konzeptionell dieselbe Auswertung. Du kannst dich aber nicht darauf verlassen, dass Postgres das Ergebnis zwischenspeichert (mehr im Artikel MATERIALIZED vs. NOT MATERIALIZED).

Was CTE nicht ist:

• Keine Variable — du kannst keinen Wert in einem CTE „berechnen und speichern".
• Keine echte temporäre Tabelle — keine Indexe, keine Wiederverwendbarkeit über Queries hinweg.
• Kein PL/pgSQL-Block — keine Schleifen, keine Variablen.

Dafür gibt's andere Werkzeuge: Variablen über LET (PG 17+ in Procedures), echte Temp-Tabellen (CREATE TEMPORARY TABLE), oder gespeicherte Funktionen.

Mehrfache Referenzierung

Ein CTE kann in derselben Query mehrmals verwendet werden:

WITH stats AS (
    SELECT
        avg(total) AS avg_order,
        max(total) AS max_order
    FROM orders WHERE status = 'paid'
)
SELECT
    o.id,
    o.total,
    CASE
        WHEN o.total > (SELECT avg_order FROM stats) THEN 'above_avg'
        ELSE 'at_or_below_avg'
    END AS bucket,
    (SELECT max_order FROM stats) AS overall_max
FROM orders o
WHERE o.status = 'paid'
ORDER BY o.total DESC;

stats wird zweimal referenziert (im CASE und in der overall_max-Spalte). Konzeptionell wird's einmal berechnet — Postgres entscheidet, ob's intern materialisiert wird.

CTE vs. Subquery — wann was?

In modernen Postgres-Versionen (12+) ist die Performance zwischen CTE und Subquery oft identisch — der Optimizer transformiert CTEs intern wie Subqueries (es sei denn, sie sind als MATERIALIZED markiert oder rekursiv).

Faustregel: bei 3 oder mehr Schritten lieber CTEs verwenden — die Lesbarkeit gewinnt.

Spalten-Aliasse

Du kannst die Spalten eines CTEs explizit benennen:

WITH revenue_by_country (country, total_revenue, customer_count) AS (
    SELECT
        c.country,
        sum(o.total),
        count(DISTINCT c.id)
    FROM customers c
    INNER JOIN orders o ON o.customer_id = c.id
    WHERE o.status = 'paid'
    GROUP BY c.country
)
SELECT * FROM revenue_by_country
ORDER BY total_revenue DESC;

Die Klammer hinter dem CTE-Namen definiert die Spalten-Namen für die Außenansicht. Selten gebraucht (man kann auch im SELECT direkt benennen), aber praktisch wenn die Spalten-Logik weit oben verständlich sein soll.

Praxis-Beispiele

Top-Kunden mit Anteil am Gesamtumsatz

WITH
revenue_per_customer AS (
    SELECT
        customer_id,
        sum(total) AS revenue
    FROM orders
    WHERE status = 'paid'
    GROUP BY customer_id
),
global_total AS (
    SELECT sum(revenue) AS total FROM revenue_per_customer
)
SELECT
    c.name,
    r.revenue,
    ROUND(100.0 * r.revenue / g.total, 2) AS pct_of_total
FROM revenue_per_customer r
CROSS JOIN global_total g
INNER JOIN customers c ON c.id = r.customer_id
ORDER BY r.revenue DESC
LIMIT 10;

Drei klare Schritte: pro Kunde aggregieren, globalen Total bilden, Anteile berechnen. Mit verschachtelten Subqueries würde das schnell unleserlich.

Bereinigung mit CTE

WITH cleaned_orders AS (
    SELECT
        id,
        customer_id,
        total,
        COALESCE(status, 'unknown')          AS status,
        date_trunc('day', created_at)::date  AS day
    FROM orders
    WHERE total > 0
      AND created_at IS NOT NULL
)
SELECT
    day,
    status,
    count(*)   AS order_count,
    sum(total) AS revenue
FROM cleaned_orders
GROUP BY day, status
ORDER BY day DESC, status;

Bereinigung als ersten Schritt isolieren — die folgenden Aggregate werden lesbarer.

Weiterführende Ressourcen

Externe Quellen

• WITH Queries (Common Table Expressions)
• Release Notes 12 – CTE Inlining

Verwandte Artikel

• MATERIALIZED vs. NOT MATERIALIZED
• Rekursive CTEs
• Data-Modifying-CTEs
• Subqueries in WHERE