Para quem trabalha com dados a sintaxe das CTEs (Common Table Expression) é bem popular, porém uma funcionalidade, pouco explorada são as CTEs Recursivas. Elas fazem parte do ANSI SQL desde 1999, mas foram sendo adotadas de forma gradual pelos “dialetos” do sql com o passar dos anos.

Exemplos de possíveis aplicações dessa funcionalidade.

Tabelas onde hierarquias diferentes se misturam

Exemplo: Suponhamos que uma tabela possua um cadastro de funcionários até cargos de liderança. Nela temos o ID de cada um dos funcionários e o ID de seu gerente imediato.

Vamos listar todos os funcionários que respondem ao gerente Jack (100):

** Este é um exemplo bem simples, que poderia ser resolvido de outra maneira. Apenas para demonstração a sintaxe.

 1WITH RECURSIVE depto_100 (id, nome, cargo) AS (
 2        SELECT
 3            id,
 4            nome,
 5            cargo
 6        FROM 
 7            funcionarios
 8        WHERE id = 100
 9
10        UNION ALL
11        SELECT 
12            e.id,
13            e.nome,
14            e.cargo
15        FROM 
16            funcionarios ASe
17        INNER JOIN depto AS de 
18            ON e.id_mng = de.id
19    )
20SELECT * FROM depto_100

É criado um SELECT de início, onde será baseada a interação e a consulta após o UNION ALL fará referência a própria CTE.

Outra aplicação interessante seria a criação de uma tabela calendário. No exemplo abaix crio uma tabela com duas colunas: data_inicio (primeiro dia do mês) e data_fim (último dia do mês) de maneira recursiva:

 1WITH RECURSIVE calendario AS (
 2    -- Base
 3    SELECT DATE '2023-01-01' AS data_inicio,
 4           LAST_DAY(DATE '2023-01-01') AS data_fim
 5
 6    UNION ALL
 7
 8    -- Recursão: próximos meses
 9    SELECT DATEADD(MONTH, 1, data_inicio) AS data_inicio,
10           LAST_DAY(DATEADD(MONTH, 1, data_inicio)) AS data_fim
11    FROM calendario
12    WHERE data_inicio < DATE '2025-12-01'
13)
14
15SELECT * FROM calendario;

Na parte BASE a coluna data_inicio foi definida como 2023-01-01 e a data_fim como o último dia relacionado ao mesmo mês do primeiro dia, utilizando LAST_DAY(). Na parte RECURSÃO, o é feito um SELECT na própria CTE, sempre adicionando 1 mês em data_inicio e data_fim, recursivamente. O loop recursivo finaliza quando data_inicio atinge um valor maior que 2025-12-01

A tabela resultante será algo como: