Common Table Expression (CTE)

Wat is een Common Table Expression?

Een Common Table Expression, meestal CTE genoemd, is een tijdelijke query met een naam binnen één SQL-statement. Je schrijft ze bovenaan met WITH en gebruikt de naam daarna alsof het een tabel is.

Een CTE bestaat alleen tijdens die ene query. Ze maakt je SQL leesbaarder, omdat je een lange bewerking kan opdelen in stappen met duidelijke namen.

Denk aan een werkblad naast je berekening. Je bewaart geen nieuwe tabel in de databank, maar je geeft een tussenstap wel een naam zodat de rest van de query begrijpelijk blijft.

Hoe ziet een CTE eruit in SQL?

Je begint met WITH, geeft de tussenstap een naam, en gebruikt die naam in de hoofdquery.

WITH customer_revenue AS (
  SELECT
    customer_id,
    SUM(amount) AS total_revenue
  FROM orders
  GROUP BY customer_id
)
SELECT
  customer_id,
  total_revenue
FROM customer_revenue
WHERE total_revenue > 10000;

De CTE customer_revenue berekent eerst omzet per klant. De hoofdquery filtert daarna op klanten boven 10.000 euro omzet.

Wanneer gebruik je een CTE?

• Om complexe query's leesbaar te maken. Elke stap krijgt een naam die uitlegt wat er gebeurt.

• Om een tussenresultaat opnieuw te gebruiken. Binnen dezelfde query kan je naar dezelfde CTE verwijzen.

• Om window functions te filteren. Bereken eerst ROW_NUMBER() in een CTE en filter daarna op rij 1.

• Om transformatielogica te documenteren. In dbt-achtige modellen lees je CTE's vaak als de stappen van een datapijplijn.

CTE versus subquery en view

Een subquery staat midden in een query. Dat is kort handig, maar bij meerdere lagen wordt het snel moeilijk leesbaar. Een CTE zet dezelfde logica bovenaan met een naam.

Een view bewaart de query als object in de databank. Een CTE bewaart niets. Ze bestaat alleen voor het statement dat je op dat moment uitvoert. Gebruik dus een view wanneer meerdere mensen dezelfde definitie nodig hebben, en een CTE wanneer je één query helder wil houden.

Waar moet je op letten bij het gebruik van CTE's

• Leesbaarheid is het doel. Tien CTE's met vage namen helpen niet. Kies namen die de stap uitleggen.

• Performance verschilt per database. Sommige systemen optimaliseren CTE's alsof het inline subqueries zijn. Andere behandelen ze meer als aparte tussenstappen.

• Een CTE is geen permanente opslag. Wil je het resultaat hergebruiken buiten deze query, maak dan een view, materialized view of tabel.

FAQ over CTE's

1. Maakt een CTE mijn query sneller?
Niet automatisch. Ze maakt je query vooral leesbaarder. De optimizer beslist wat er technisch mee gebeurt.

2. Kan een CTE zichzelf aanroepen?
Ja, sommige databases ondersteunen recursieve CTE's. Die gebruik je bijvoorbeeld voor hiërarchische data zoals organisatiestructuren.

3. Is een CTE hetzelfde als een tijdelijke tabel?
Nee. Een tijdelijke tabel wordt als object aangemaakt voor een sessie of transactie. Een CTE leeft alleen binnen één statement.