Leg uit wat recursieve Common Table Expressions (CTE) zijn en hoe ze worden gebruikt in SQL-query's

Recursieve Common Table Expressions (CTE) zijn een speciaal soort tijdelijke resultaatsets in SQL die naar zichzelf kunnen verwijzen om recursieve queries uit te voeren. Ze zijn handig voor het werken met hiërarchische of grafische gegevensstructuren, bijvoorbeeld om door een categoriebomen te navigeren, werknemers op ondergeschiktheid te organiseren, enz.

Een recursieve CTE bestaat uit twee delen:

1. Basisquery — de initiële set rijen.
2. Recursieve query — gebruikt de resultaten van de vorige iteratie om het volgende dataniveau te verkrijgen.

Voorbeeld: het verkrijgen van alle ondergeschikte werknemers met id = 1 in een werknemershiërarchie.

WITH RECURSIVE Subordinates AS (
    SELECT id, manager_id, name
    FROM employees
    WHERE id = 1  -- basisgeval
  
    UNION ALL
  
    SELECT e.id, e.manager_id, e.name
    FROM employees e
    INNER JOIN Subordinates s ON e.manager_id = s.id  -- recursief geval
)
SELECT * FROM Subordinates;

Deze query selecteert eerst de werknemer met id=1, voegt vervolgens recursief alle werknemers toe waarvan de manager_id overeenkomt met de id van de al gevonden werknemers, totdat de hele hiërarchie is doorlopen.