Hoe werken CTE's en recursieve query's in PostgreSQL?

Question

Accepted Answer

Een **CTE** (Common Table Expression), gedefinieerd met **`WITH`**, is een benoemde tijdelijke resultaatset die complexe query's leesbaar maakt. PostgreSQL ondersteunt volledig CTE's, inclusief **recursieve** CTE's (`WITH RECURSIVE`) voor het doorkruisen van hiërarchische/boomgegevens.

## Basis CTE's — leesbare query-opbouw

```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
```

CTE's verdelen een complexe query in benoemde, leesbare stappen (gekoppeld, elk verwijzend naar vorige) — veel duidelijker dan geneste subquery's.

## Recursieve CTE's — hiërarchieën doorkruisen

```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
```

Een **recursieve CTE** heeft een basisgeval (de startende rijen) en een recursieve stap (`UNION ALL` die terugkoppelt naar de CTE zelf), itererend totdat geen nieuwe rijen meer — perfect voor **bomen en hiërarchieën** (organogrammen, categorieënbomen, stuklijsten, grafiekpaden).

## Een opmerking over CTE-materialisatie (Postgres-specifiek)

```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)
```

Vóór Postgres 12 waren CTE's altijd een "optimalisatiebarrière" (gerealiseerd); nu kunnen ze inline worden gebruikt voor betere prestaties — `MATERIALIZED`/`NOT MATERIALIZED` regelt dit.

## Waarom het belangrijk is

CTE's en recursieve query's zijn waardevolle PostgreSQL-functies voor **leesbaarheid en hiërarchische gegevens**, dus het begrijpen ervan is nuttig voor het schrijven van onderhoudbare complexe query's en het omgaan met boomgestructureerde gegevens.

Het dagelijks voordeel is **leesbaarheid**: complexe query's met meerdere subquery-niveaus worden moeilijk onderhoudbaar, terwijl CTE's je toestaan ze op te splitsen in benoemde, opeenvolgende logische stappen (van boven naar beneden lezen), wat de duidelijkheid drastisch verbetert — wat CTE's een belangrijk instrument maakt voor het schrijven van onderhoudbare SQL.

Het opvallende vermogen is **recursieve CTE's** (`WITH RECURSIVE`), die het mogelijk maken om **hiërarchische en boomgestructureerde gegevens** op te vragen (organogrammen, categorieënbomen, stuklijsten, gethreade reacties, grafiekdoorgang) — een klasse query's die anders zeer moeilijk zijn in SQL, wat recursieve CTE's de standaardoplossing maakt voor hiërarchische doorgang.

Het begrijpen van de recursieve CTE-structuur (basisgeval + recursieve stap die terugkoppelt naar zichzelf) is cruciale kennis voor deze veelvoorkomende behoefte.

Een nuttig Postgres-specifiek detail is het **CTE-materialisatiegedrag** (Postgres 12+ kan CTE's inline gebruiken voor betere prestaties, regelbaar met `MATERIALIZED`/`NOT MATERIALIZED`) — relevant voor optimalisatie.

Aangezien complexe query's veel voorkomen (en leesbaarheid is belangrijk voor onderhoud), en aangezien hiërarchische gegevens (die recursieve CTE's uniek goed afhandelen) vaak voorkomen (organisatiestructuren, geneste categorieën), is het begrijpen van CTE's en recursieve query's waardevolle, praktisch relevante PostgreSQL-kennis die de onderhoudbaarheid van query's verbetert en hiërarchisch opvragen ontsluit — een regelmatig relevant vermogen voor schone, geavanceerde SQL en een veelvoorkomend onderwerp, vooral de recursieve vorm voor de terugkerende behoefte om boomgestructureerde gegevens op te vragen.