Una CTE recursiva se referencia a sí misma, lo que permite recorrer con una sola consulta un árbol de cualquier profundidad, como un organigrama:
WITH RECURSIVE team AS (
SELECT employee_id, first_name, 1 AS level
FROM employees WHERE employee_id = 2 -- start: the anchor
UNION ALL
SELECT e.employee_id, e.first_name, t.level + 1
FROM employees AS e
JOIN team AS t ON e.manager_id = t.employee_id -- step: one level down
)
SELECT * FROM team;
El caso base se ejecuta una sola vez. Después, la parte recursiva se ejecuta una y otra vez, cada vez sobre las filas que produjo la iteración anterior, hasta que una iteración no produce nada. Toda CTE recursiva necesita algo que acabe deteniéndola: aquí, las personas sin subordinados. Cuando cuentes en sentido ascendente, añade un límite con WHERE, o un error en los datos (un ciclo) hará que se ejecute indefinidamente.
PostgreSQL, MySQL 8, SQLite, Snowflake y DuckDB usan WITH RECURSIVE; SQL Server y Oracle aceptan la misma estructura sin la palabra RECURSIVE.
Cuenta del 1 al 5: el caso base es 1, cada iteración suma 1 y WHERE detiene el proceso.