Hoe recursieve CTE's werken
Een basisgeval plus een recursieve stap.
Hoe recursieve CTE's werken is een gratis SQL Academy-les op CoddyKit. Dit is les 1 van 4. Je kunt de volledige les hieronder gratis lezen en daarna in de browser praktisch oefenen met een ingebouwde code-editor en een AI-begeleider die 24/7 beschikbaar is. Deze les maakt deel uit van het leertraject SQL Academy. Je voortgang wordt gesynchroniseerd op het web en in de CoddyKit-app. De cursus SQL Academy bevat in totaal 4 lessen.
Wat is een recursieve CTE?
Een recursieve CTE is een algemene tabelexpressie die naar zichzelf verwijst. Hiermee kun je queries schrijven die een stap herhalen totdat aan een voorwaarde is voldaan — vergelijkbaar met een lus, maar uitgedrukt in zuivere SQL.
Recursieve CTE's worden gedefinieerd met het trefwoord WITH RECURSIVE en zijn ideaal voor het doorlopen van hiërarchische of graafachtige gegevens, zoals organisatieoverzichten, mappenbomen en stuklijststructuren.
De tweedelige structuur
Elke recursieve CTE bestaat precies uit twee delen, gescheiden door UNION ALL:
1. Basisgeval — een niet-recursieve SELECT die de startrijen retourneert.
2. Recursieve stap — een SELECT die de CTE opnieuw aan zichzelf koppelt en het volgende rijniveau produceert.
De database-engine blijft de recursieve stap uitvoeren en resultaten verzamelen totdat er geen nieuwe rijen meer worden geproduceerd.
WITH RECURSIVE cte_name AS (
-- Base case
SELECT ...
UNION ALL
-- Recursive step (references cte_name)
SELECT ... FROM source JOIN cte_name ON ...
)
SELECT * FROM cte_name;Tellen van 1 tot 5
De eenvoudigste recursieve CTE telt getallen. Het basisgeval initialiseert de waarde 1. De recursieve stap telt bij elke iteratie 1 op. De WHERE-clausule in de recursieve stap fungeert als stopvoorwaarde — zonder deze clausule zou de query oneindig blijven uitvoeren.
WITH RECURSIVE counter(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;Stapsgewijze uitvoering
Zo verwerkt de database-engine de teller-CTE iteratie voor iteratie:
Iteratie 0 (basisgeval): retourneert {1}.
Iteratie 1: past de recursieve stap toe op {1} en retourneert {2}.
Iteratie 2: past de recursieve stap toe op {2} en retourneert {3}.
Iteratie 3, 4: retourneert eerst {4} en daarna {5}.
Iteratie 5: WHERE n < 5 is onwaar voor n=5, dus worden er nul rijen geretourneerd. De query eindigt.
Alle verzamelde rijen — 1, 2, 3, 4, 5 — vormen het eindresultaat.
Een hiërarchietabel instellen
Recursieve CTE's komen goed tot hun recht bij tabellen die naar zichzelf verwijzen. Laten we een employees-tabel maken waarin elke werknemer een optionele manager_id heeft die terugwijst naar dezelfde tabel.
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name VARCHAR(50),
manager_id INTEGER REFERENCES employees(id)
);
INSERT INTO employees VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Carol', 1),
(4, 'Dave', 2),
(5, 'Eve', 2),
(6, 'Frank', 3);De hiërarchie doorlopen
Nu kunnen we de volledige rapportagelijn doorlopen vanaf de directeur (Alice, id=1). Het basisgeval selecteert Alice; de recursieve stap zoekt alle werknemers waarvan manager_id overeenkomt met een id die al in de CTE staat.
Het resultaat bevat elke werknemer die vanaf Alice bereikbaar is, ongeacht hoe diep de boom gaat.
WITH RECURSIVE org_tree AS (
SELECT id, name, manager_id, 0 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, ot.depth + 1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT depth, name FROM org_tree ORDER BY depth, name;Het pad bijhouden
Een veelgebruikte uitbreiding is het opbouwen van een padtekst die de volledige keten van de wortel naar elk knooppunt toont. Terwijl we recursief dieper gaan, voegen we namen samen met ' -> ' ertussen.
Zo kun je eenvoudig broodkruimelnavigatie weergeven of diepe hiërarchieën foutzoeken.
WITH RECURSIVE org_tree AS (
SELECT id, name, name AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, ot.path || ' -> ' || e.name
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, path FROM org_tree ORDER BY path;De recursiediepte beperken
Diepe of circulaire gegevens kunnen ervoor zorgen dat een recursieve CTE heel lang blijft uitvoeren. Twee veilige werkwijzen:
1. Houd de diepte bij en voeg een WHERE-clausule toe — WHERE depth < 10 zorgt ervoor dat je nooit verder dan 10 niveaus gaat.
2. Gebruik een kolom voor cyclusdetectie — sommige databases (PostgreSQL 14+) bieden CYCLE-syntaxis om herhaalde bezoeken aan knooppunten automatisch te detecteren.
WITH RECURSIVE org_tree AS (
SELECT id, name, 0 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, ot.depth + 1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
WHERE ot.depth < 10
)
SELECT depth, name FROM org_tree;UNION versus UNION ALL in recursieve CTE's
De recursieve stap gebruikt bijna altijd UNION ALL en niet UNION. Dit is waarom:
UNION verwijdert dubbele rijen na elke iteratie door de volledige resultatenset te vergelijken — dit is extreem duur en kan de betekenis veranderen van grafen waarin hetzelfde knooppunt legitiem via meerdere paden wordt bereikt.
UNION ALL behoudt alle rijen zonder dubbele rijen te verwijderen. Dat is zowel sneller als correct voor het doorlopen van bomen. Gebruik UNION alleen wanneer je een specifieke reden hebt om dubbele rijen te verwijderen en de gevolgen voor de prestaties begrijpt.
Een datumreeks genereren
Recursieve CTE's zijn ook handig voor het genereren van reeksen met datums. Dit voorbeeld produceert elke dag van een bepaalde week — een patroon dat vaak wordt gebruikt om kalenderrapporten te maken of hiaten in tijdreeksgegevens op te vullen.
WITH RECURSIVE date_series AS (
SELECT DATE '2024-01-01' AS day
UNION ALL
SELECT day + INTERVAL '1 day'
FROM date_series
WHERE day < DATE '2024-01-07'
)
SELECT day FROM date_series;Alle ondergeschikten van één manager vinden
Je kunt het basisgeval met elk specifiek knooppunt starten — niet alleen met de wortel. Hier beginnen we bij Bob (id=2) en vinden we iedereen die rechtstreeks of indirect aan hem rapporteert.
Dit patroon is handig voor toestemmingscontroles, aggregaties van deelbomen of om dashboards te beperken tot één afdeling.
WITH RECURSIVE subordinates AS (
SELECT id, name
FROM employees
WHERE id = 2
UNION ALL
SELECT e.id, e.name
FROM employees e
JOIN subordinates s ON e.manager_id = s.id
)
SELECT name FROM subordinates;Korte toets
Toets je begrip van de werking van recursieve CTE's.
Samenvatting van de les
In deze les heb je geleerd hoe recursieve CTE's werken:
Structuur: elke recursieve CTE bevat een basisgeval (startrijen) dat met UNION ALL wordt gekoppeld aan een recursieve stap (een SELECT die naar zichzelf verwijst).
Beëindiging: de database-engine herhaalt de recursieve stap en verzamelt resultaten totdat de stap nul rijen retourneert.
Veelgebruikte toepassingen: organisatieoverzichten en mappenbomen doorlopen, reeksen met getallen of datums genereren, paden berekenen en alle knooppunten in een deelboom vinden.
Veiligheidstips: neem altijd een stopvoorwaarde op (een dieptelimiet of cycluscontrole) en gebruik voor betere prestaties bij voorkeur UNION ALL in plaats van UNION.
Leer SQL met een AI-tutor — gratis
Schrijf echte code en voer die uit in je browser, krijg direct hulp van een AI-tutor die 24/7 beschikbaar is en ga verder waar je gebleven bent op het web of in de app.
- Cursussen
- 46
- Lessen
- 183
Veelgestelde vragen
Is de les “Hoe recursieve CTE's werken” gratis?
Ja — de volledige tekst van “Hoe recursieve CTE's werken” kun je hier gratis op het web lezen. Als je interactief wilt oefenen met een ingebouwde code-editor en een AI-begeleider die 24/7 beschikbaar is, en de rest van de cursus SQL Academy wilt ontgrendelen, kun je upgraden naar CoddyKit PRO. De cursus SQL Academy bevat in totaal 4 lessen.
Wat leer ik in “Hoe recursieve CTE's werken”?
Een basisgeval plus een recursieve stap. Je oefent met SQL Academy door code rechtstreeks in de browser uit te voeren. Een AI-begeleider die 24/7 beschikbaar is beantwoordt je vragen terwijl je de les doorwerkt.
Heb ik ervaring nodig om met SQL Academy te beginnen?
Ervaring vooraf is niet nodig. SQL Academy op CoddyKit is opgebouwd voor beginners tot gevorderden, zodat je hier of bij het begin kunt starten en in je eigen tempo kunt leren. Dit is les 1 van 4.
Hoe lang duurt de les “Hoe recursieve CTE's werken”?
De meeste lessen van CoddyKit duren ongeveer 5–10 minuten. Elke les is kort en interactief, zodat je gestaag vooruitgaat en op het web en in de app precies verdergaat waar je was gebleven.
Kan ik code schrijven en uitvoeren in deze les over SQL Academy?
Ja. Elke les over SQL Academy bevat een ingebouwde code-editor, zodat je rechtstreeks in je browser echte code kunt schrijven en uitvoeren en direct feedback van AI krijgt — lokale installatie is niet nodig.
Alle lessen in deze cursus
- Hoe recursieve CTE's werken
- Een categoriestructuur doorlopen
- Reeksen genereren
- Oneindige lussen vermijden