Como funcionam as CTEs recursivas
Caso base mais etapa recursiva.
Como funcionam as CTEs recursivas é uma aula grátis de SQL Academy no CoddyKit. Esta é a aula 1 de 4. Você pode ler a aula completa abaixo gratuitamente — depois pratica ao vivo no navegador com um editor de código integrado e um tutor de IA 24/7. Faz parte do caminho de aprendizado de SQL Academy, e seu progresso é sincronizado entre a web e o app CoddyKit. O curso de SQL Academy inclui 4 aulas no total.
O que é uma CTE recursiva?
Uma CTE recursiva é uma expressão de tabela comum que faz referência a si mesma. Ela permite escrever consultas que repetem uma etapa até que uma condição seja atendida — de modo semelhante a um laço, mas expresso como SQL puro.
As CTEs recursivas são definidas com a palavra-chave WITH RECURSIVE e são ideais para percorrer dados hierárquicos ou semelhantes a grafos, como organogramas, árvores de pastas e estruturas de listas de materiais.
Estrutura em duas partes
Toda CTE recursiva tem exatamente duas partes, separadas por UNION ALL:
1. Caso base — um SELECT não recursivo que retorna as linhas iniciais.
2. Etapa recursiva — um SELECT que une a CTE novamente a si mesma, produzindo o nível seguinte de linhas.
O mecanismo continua executando a etapa recursiva e acumulando resultados até que ela produza zero novas linhas.
WITH RECURSIVE cte_name AS (
-- Base case
SELECT ...
UNION ALL
-- Recursive step (references cte_name)
SELECT ... FROM source JOIN cte_name ON ...
)
SELECT * FROM cte_name;Contando de 1 a 5
A CTE recursiva mais simples conta números. O caso base inicializa o valor 1. A etapa recursiva adiciona 1 a cada iteração. A cláusula WHERE dentro da etapa recursiva atua como a condição de término — sem ela, a consulta seria executada para sempre.
WITH RECURSIVE counter(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;Execução passo a passo
Veja como o mecanismo processa a CTE contadora, iteração por iteração:
Iteração 0 (caso base): retorna {1}.
Iteração 1: aplica a etapa recursiva a {1} e retorna {2}.
Iteração 2: aplica a etapa recursiva a {2} e retorna {3}.
Iterações 3 e 4: retorna {4} e depois {5}.
Iteração 5: WHERE n < 5 é falso para n=5, portanto nenhuma linha é retornada. A consulta termina.
Todas as linhas acumuladas — 1, 2, 3, 4, 5 — são o resultado final.
Configurando uma tabela hierárquica
As CTEs recursivas são especialmente úteis em tabelas autorreferentes. Vamos criar uma tabela employees em que cada funcionário tem um manager_id opcional que aponta de volta para a mesma tabela.
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name VARCHAR(50),
manager_id INTEGER REFERENCES employees(id)
);
INSERT INTO employees VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Carol', 1),
(4, 'Dave', 2),
(5, 'Eve', 2),
(6, 'Frank', 3);Percorrendo a hierarquia
Agora podemos percorrer toda a cadeia de subordinação começando pelo CEO (Alice, id=1). O caso base seleciona Alice; a etapa recursiva encontra todos os funcionários cujo manager_id corresponde a um id já presente na CTE.
O resultado inclui todos os funcionários alcançáveis a partir de Alice, independentemente da profundidade da árvore.
WITH RECURSIVE org_tree AS (
SELECT id, name, manager_id, 0 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, ot.depth + 1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT depth, name FROM org_tree ORDER BY depth, name;Rastreando o caminho
Um aprimoramento comum é criar um texto do caminho que mostre toda a cadeia da raiz até cada nó. Concatenamos os nomes, separados por ' -> ', à medida que avançamos na recursão.
Isso facilita a exibição de uma navegação no estilo de trilhas ou a depuração de hierarquias profundas.
WITH RECURSIVE org_tree AS (
SELECT id, name, name AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, ot.path || ' -> ' || e.name
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, path FROM org_tree ORDER BY path;Limitando a profundidade da recursão
Dados com grande profundidade ou circulares podem fazer uma CTE recursiva ser executada por muito tempo. Duas práticas seguras:
1. Acompanhe a profundidade e adicione uma cláusula WHERE — WHERE depth < 10 garante que você nunca ultrapasse 10 níveis.
2. Use uma coluna de detecção de ciclos — alguns bancos de dados (PostgreSQL 14+) oferecem a sintaxe CYCLE para detectar automaticamente visitas repetidas aos nós.
WITH RECURSIVE org_tree AS (
SELECT id, name, 0 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, ot.depth + 1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
WHERE ot.depth < 10
)
SELECT depth, name FROM org_tree;UNION versus UNION ALL em CTEs recursivas
A etapa recursiva quase sempre usa UNION ALL, não UNION. Veja o motivo:
UNION elimina duplicatas das linhas após cada iteração, comparando todo o conjunto de resultados — isso é extremamente dispendioso e pode alterar a semântica de grafos nos quais o mesmo nó é legitimamente alcançado por vários caminhos.
UNION ALL mantém todas as linhas sem eliminar duplicatas, o que é mais rápido e correto para percorrer árvores. Use UNION somente quando tiver uma necessidade específica de eliminar duplicatas e compreender o custo de desempenho.
Gerando uma sequência de datas
As CTEs recursivas também são úteis para gerar sequências de datas. Este exemplo produz cada dia de uma determinada semana — um padrão frequentemente usado para criar relatórios de calendário ou preencher lacunas em dados de séries temporais.
WITH RECURSIVE date_series AS (
SELECT DATE '2024-01-01' AS day
UNION ALL
SELECT day + INTERVAL '1 day'
FROM date_series
WHERE day < DATE '2024-01-07'
)
SELECT day FROM date_series;Encontrando todos os subordinados de um gerente
Você pode inicializar o caso base com qualquer nó específico — não apenas com a raiz. Aqui começamos por Bob (id=2) e encontramos todas as pessoas que se reportam a ele direta ou indiretamente.
Esse padrão é útil para verificações de permissões, agregações de subárvores ou para restringir painéis a um único departamento.
WITH RECURSIVE subordinates AS (
SELECT id, name
FROM employees
WHERE id = 2
UNION ALL
SELECT e.id, e.name
FROM employees e
JOIN subordinates s ON e.manager_id = s.id
)
SELECT name FROM subordinates;Verificação rápida
Verifique sua compreensão sobre o funcionamento das CTEs recursivas.
Recapitulação da lição
Nesta lição, aprendeu como funcionam as CTEs recursivas:
Estrutura: toda CTE recursiva tem um caso base (linhas iniciais) unido a uma etapa recursiva (SELECT autorreferente) por meio de UNION ALL.
Término: o mecanismo repete a etapa recursiva e acumula resultados até que a etapa retorne zero linhas.
Usos comuns: percorrer organogramas e árvores de pastas, gerar sequências de números ou datas, calcular caminhos e encontrar todos os nós de uma subárvore.
Dicas de segurança: sempre inclua uma condição de término (limite de profundidade ou proteção contra ciclos) e prefira UNION ALL a UNION por questões de desempenho.
Perguntas Frequentes
A aula “Como funcionam as CTEs recursivas” é grátis?
Sim — o texto completo de “Como funcionam as CTEs recursivas” é grátis para ler aqui na web. Para praticá-la interativamente (um editor de código integrado e um tutor de IA 24/7) e desbloquear o restante do curso de SQL Academy, atualize para CoddyKit PRO. O curso de SQL Academy inclui 4 aulas no total.
O que vou aprender em “Como funcionam as CTEs recursivas”?
Caso base mais etapa recursiva. Você pratica SQL Academy com código prático que executa diretamente no navegador, e um tutor de IA 24/7 responde suas dúvidas enquanto trabalha na aula.
Preciso ter experiência prévia para começar SQL Academy?
Nenhuma experiência prévia é necessária. SQL Academy no CoddyKit é estruturado para alunos iniciantes até avançados, então você pode começar aqui ou desde o início e aprender no seu ritmo. Esta é a aula 1 de 4.
Quanto tempo leva a aula “Como funcionam as CTEs recursivas”?
A maioria das aulas CoddyKit leva cerca de 5–10 minutos. Cada uma é compacta e interativa, então você faz progresso constante e retoma exatamente de onde parou entre web e app.
Posso escrever e executar código nesta aula de SQL Academy?
Sim. Cada aula de SQL Academy inclui um editor de código integrado, então você escreve e executa código real direto no navegador e recebe feedback de IA instantaneamente — nenhuma configuração local necessária.
Todas as aulas deste curso
- Como funcionam as CTEs recursivas
- Percorrendo uma árvore de categorias
- Gerando séries e sequências
- Evitando laços infinitos