CTEs recursivas em SQL: WITH RECURSIVE para árvores, grafos e séries numéricas

Uma CTE comum não passa de uma subconsulta com nome. Uma CTE recursiva é outra história: ela pode referenciar a si mesma e rodar em loop até os dados acabarem. É assim que o SQL percorre hierarquias — uma árvore de subordinados, uma cadeia de categorias, um grafo de amigos ou de dependências. Se você já escreveu código de aplicação que bate no banco em loop perguntando "e quem é o gerente desse gerente?", uma CTE recursiva reduz tudo isso a uma única consulta.

Vamos trabalhar a mecânica sobre um esquema employees(id, name, manager_id) mais orders, e terminar com a parte mais assustadora: os loops infinitos.

Como o WITH RECURSIVE funciona

Uma CTE recursiva sempre tem duas partes unidas por UNION ALL:

• A âncora — uma consulta de partida que roda exatamente uma vez. É a "raiz" da recursão.
• O passo recursivo — uma consulta que referencia o próprio nome da CTE. Ele se repete várias vezes, e a cada iteração enxerga as linhas adicionadas pela anterior.

WITH RECURSIVE chain AS (
  -- anchor: runs once
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL          -- top-level execs

  UNION ALL

  -- recursive step: references chain
  SELECT e.id, e.name, e.manager_id, c.depth + 1
  FROM employees e
  JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain ORDER BY depth, id;

O motor roda a âncora e guarda a saída dela em uma "tabela de trabalho". Depois roda o passo recursivo, alimentando-o com a tabela de trabalho atual sob o nome chain. As novas linhas viram a próxima tabela de trabalho, e o passo roda de novo. No momento em que uma iteração retorna zero linhas, a recursão para e tudo o que foi acumulado é entregue à consulta externa.

A palavra-chave RECURSIVE é escrita uma única vez, logo depois de WITH, mesmo que várias CTEs sejam recursivas. Sem ela, o Postgres não deixa uma CTE referenciar a si mesma.

Percorrendo um organograma (para baixo e para cima)

O caso mais comum é "mostre todo mundo abaixo deste gerente". A coluna depth te dá o nível de aninhamento, e um path construído ao longo do caminho te dá a cadeia completa até cada pessoa.

WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id,
         1 AS depth,
         name::text AS path
  FROM employees
  WHERE id = 1                       -- start from one boss

  UNION ALL

  SELECT e.id, e.name, e.manager_id,
         s.depth + 1,
         s.path || ' > ' || e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT id, name, depth, path
FROM subordinates
ORDER BY path;

Para percorrer para cima (de um funcionário até seus gerentes), basta inverter a condição do join: no passo recursivo, em vez de "cujo gerente é a linha atual", procure "quem é o gerente da linha atual".

WITH RECURSIVE managers AS (
  SELECT id, name, manager_id
  FROM employees WHERE id = 42

  UNION ALL

  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN managers m ON m.manager_id = e.id   -- climb to the boss
)
SELECT * FROM managers;

Categorias de produtos, comentários encadeados, uma lista de materiais — são todos o mesmo modelo de "uma linha aponta para o seu pai", e todos cedem a esse único template.

Séries numéricas e calendários

A recursão não precisa de uma tabela de origem. Você pode gerar uma sequência de números ou de datas na hora — útil quando você precisa "preencher" dias que não tiveram pedidos.

WITH RECURSIVE days AS (
  SELECT DATE '2026-01-01' AS d
  UNION ALL
  SELECT d + 1 FROM days
  WHERE d < DATE '2026-01-31'        -- the stop condition!
)
SELECT d.d, COUNT(o.id) AS orders
FROM days d
LEFT JOIN orders o ON o.created_at::date = d.d
GROUP BY d.d
ORDER BY d.d;

No PostgreSQL uma série costuma sair mais simples com generate_series('2026-01-01', '2026-01-31', INTERVAL '1 day') — mas a recursão é universal e funciona onde generate_series não existe (em motores mais simples, digamos). Este exemplo importa por outro motivo: a regra de parada é você quem inventa. Sem WHERE d < ... a consulta giraria para sempre.

Grafos e segurança contra ciclos

Uma árvore é segura: cada nó tem um único pai, então não há ciclos. Um grafo não é. Se um loop se infiltra em manager_id (A → B → A), ou você percorre um grafo de amizades/dependências, a recursão vai girar e devorar memória.

O Postgres 14+ traz proteção embutida — CYCLE:

WITH RECURSIVE reachable AS (
  SELECT from_id, to_id
  FROM edges WHERE from_id = 1

  UNION ALL

  SELECT e.from_id, e.to_id
  FROM edges e
  JOIN reachable r ON e.from_id = r.to_id
)
CYCLE to_id SET is_cycle USING path_arr
SELECT DISTINCT to_id FROM reachable WHERE NOT is_cycle;

CYCLE to_id diz ao Postgres para rastrear os valores de to_id já visitados: numa repetição ele marca a linha como is_cycle = true e para de descer. Em versões mais antigas você faz o mesmo truque na mão — carrega um array de nós visitados e filtra as repetições:

-- instead of CYCLE: a manual path array
SELECT e.from_id, e.to_id, r.path || e.to_id
FROM edges e
JOIN reachable r ON e.from_id = r.to_id
WHERE e.to_id <> ALL(r.path)        -- don't revisit a node

Uma armadilha clássica: UNION versus UNION ALL. O UNION remove linhas duplicadas e, com isso, corta algumas repetições por conta própria, mas não vai te salvar de ciclos cujo path/depth seja diferente, e ainda é mais lento. Conte com proteção explícita (CYCLE ou um array), não com o efeito colateral do UNION.

Salvaguardas práticas:

• Mantenha sempre uma condição de parada explícita no passo recursivo (WHERE depth < 100).
• Para grafos, use CYCLE ou um array de nós visitados.
• No MySQL 8 a sintaxe é a mesma (WITH RECURSIVE), mas a profundidade é limitada por cte_max_recursion_depth (1000 por padrão) — você toma um erro em vez de um travamento.
• No ClickHouse as CTEs recursivas chegaram tarde e são limitadas em alguns pontos; para hierarquias o pessoal costuma recorrer a motores e funções dedicadas como dictGetHierarchy. Confira a sua versão.

Uma CTE recursiva é um loop for escondido dentro do SQL declarativo. Domine a dupla âncora + UNION ALL, tenha a condição de parada sempre em mente, e percorrer árvores e grafos deixa de ser motivo para arrastar a lógica de volta para a sua aplicação.