Uma CTE recursiva faz referência a si mesma, o que permite que uma única consulta percorra uma árvore de qualquer profundidade, como um organograma:
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;
A âncora é executada uma vez. Depois, a parte recursiva é executada várias vezes, cada vez sobre as linhas que a rodada anterior produziu, até que uma rodada não produza nada. Toda CTE recursiva precisa de algo que acabe por pará-la: aqui, as pessoas sem subordinados diretos. Quando você contar em ordem crescente, adicione um limite com WHERE; caso contrário, um erro nos dados (um ciclo) faz a consulta rodar para sempre.
O PostgreSQL, o MySQL 8, o SQLite, o Snowflake e o DuckDB escrevem WITH RECURSIVE; o SQL Server e o Oracle aceitam a mesma estrutura sem a palavra RECURSIVE.
Conte de 1 a 5: a âncora é 1, cada rodada soma 1 e o WHERE interrompe o processo.