Wie funktionieren CTEs und rekursive Abfragen in PostgreSQL?

Question

Accepted Answer

Eine **CTE** (Common Table Expression), definiert mit **`WITH`**, ist ein benanntes temporäres Ergebnis, das komplexe Abfragen lesbar macht. PostgreSQL unterstützt CTEs vollständig, einschließlich **rekursiver** CTEs (`WITH RECURSIVE`) zum Durchlaufen hierarchischer/baumartiger Daten.

## Basis-CTEs — lesbare Abfrageerstellung

```sql
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
order_counts AS (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT u.name, oc.cnt
FROM active_users u
JOIN order_counts oc ON u.id = oc.user_id;   -- compose named steps cleanly
```

CTEs zerlegen eine komplexe Abfrage in benannte, lesbare Schritte (verkettet, jeder verweist auf vorherige) — viel klarer als verschachtelte Subabfragen.

## Rekursive CTEs — Hierarchien durchlaufen

```sql
-- find all subordinates under employee 1 (an org-chart hierarchy)
WITH RECURSIVE subordinates AS (
  -- BASE CASE: the starting point
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  -- RECURSIVE STEP: find employees whose manager is in the result so far
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;          -- the entire tree under employee 1
```

Eine **rekursive CTE** hat einen Basisfall (die Startzeilen) und einen rekursiven Schritt (`UNION ALL`, das zurück zur CTE selbst führt), wiederholend bis keine neuen Zeilen — perfekt für **Bäume und Hierarchien** (Organigramme, Kategoriebäume, Stücklisten, Graphpfade).

## Ein Hinweis zur CTE-Materialisierung (PostgreSQL-spezifisch)

```sql
-- Postgres 12+ may INLINE CTEs (optimize them) by default; force behavior if needed:
WITH x AS MATERIALIZED (...)      -- force materialization (compute once, store)
WITH x AS NOT MATERIALIZED (...)  -- force inlining (optimize into the main query)
```

Vor Postgres 12 waren CTEs immer eine „Optimierungsbarriere