SQL CTE Recursiva

Na aula CTE (WITH), cada CTE era um rascunho com nome que lia as tabelas do banco. Agora vem uma ideia nova: uma CTE que lê a si mesma. Ela começa com uma linha, usa essa linha para fazer a próxima, usa a próxima para fazer outra, e para quando uma regra mandar. Com isso o SQL faz coisas que um SELECT comum não faz: contar de 1 a 10 sem tabela nenhuma, montar um calendário com todos os dias do mês e descer pela hierarquia da Loja Aurora, da gerente Marta até o último funcionário.

Todos os exemplos rodam de verdade no seu navegador, no banco da Loja Aurora.

A ideia: a fila de dominós

Pense numa fila de dominós em pé. Para todos caírem, você precisa de três coisas:

Se faltar a beira da mesa, a fila nunca acaba. No SQL é igual: sem a condição de parada, a CTE gera linhas para sempre. Você vai ver isso acontecer (com segurança) em Quando dá errado.

1. Contar de 1 a 10

O primeiro exemplo não usa tabela nenhuma da loja. Ele cria os números:

WITH RECURSIVE conta(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM conta WHERE n < 10
)
SELECT n FROM conta;

WITH RECURSIVE

"Com, recursivamente". A palavra RECURSIVE avisa que a CTE vai ler a si mesma. (O SQLite aceita sem ela, mas escreva sempre: deixa claro para quem lê e é obrigatória em outros bancos.)

conta(n)

O nome da CTE e o nome da sua coluna, como na aula anterior. Aqui ele é quase obrigatório: SELECT 1 não tem nome de coluna, e é pela lista que a coluna se chama n.

SELECT 1

A âncora: o primeiro dominó. Roda uma vez só e cria a linha n = 1.

UNION ALL

"União de tudo". Junta as linhas da âncora com as que a parte recursiva for criando (aula UNION ALL).

SELECT n + 1 FROM conta

A parte recursiva: a regra do próximo dominó. Ela lê conta, a própria CTE, e para cada linha que chega cria uma nova, com o número seguinte.

WHERE n < 10

A condição de parada. Quando chega a linha n = 10, o WHERE é falso e nenhuma linha nova nasce. Sem linha nova, acabou.

SELECT n FROM conta;

O comando principal, igual ao de qualquer CTE: lê o resultado pronto.

Rodada por rodada

O SQLite guarda as linhas novas numa fila. Ele tira uma linha da fila, entrega para o resultado e roda a parte recursiva só com aquela linha. O que a parte recursiva devolver entra no fim da fila. Quando a fila esvazia, terminou:

Repare que a parte recursiva nunca vê a CTE inteira: em cada rodada, conta é só a linha da vez. É por isso que n + 1 funciona: n é o número daquela linha.

2. Mudando a regra

As três partes são as mesmas; mude o que cada uma faz e você tem outra sequência.

A soma de 1 a 100

O comando principal pode fazer qualquer coisa com o resultado da CTE, inclusive somar (aula SUM()):

WITH RECURSIVE conta(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM conta WHERE n < 100
)
SELECT SUM(n) AS soma FROM conta;

Dá 5050, a conta famosa que dizem que o matemático Gauss fez de cabeça quando criança.

A tabuada do 7

A CTE gera os números de 1 a 10; o comando principal calcula uma coluna a mais com eles:

WITH RECURSIVE conta(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM conta WHERE n < 10
)
SELECT n, n * 7 AS resultado FROM conta;

WITH RECURSIVE conta(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM conta WHERE n < 10
)
SELECT n, n * 7 AS resultado FROM conta;

WITH RECURSIVE conta(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM conta WHERE n < 10
)
SELECT n, n * 9 AS resultado FROM conta;

3. Um calendário de datas

Agora o "número seguinte" vira o "dia seguinte". A função date(dia, '+1 day') soma um dia a uma data (aula Funções de Data), e as datas da Loja Aurora estão no formato AAAA-MM-DD, que se compara direitinho com <:

WITH RECURSIVE dias(dia) AS (
  SELECT '2026-10-01'
  UNION ALL
  SELECT date(dia, '+1 day') FROM dias WHERE dia < '2026-10-07'
)
SELECT dia,
       CASE strftime('%w', dia)
         WHEN '0' THEN 'domingo'  WHEN '1' THEN 'segunda'
         WHEN '2' THEN 'terça'    WHEN '3' THEN 'quarta'
         WHEN '4' THEN 'quinta'   WHEN '5' THEN 'sexta'
         ELSE 'sábado'
       END AS dia_da_semana
FROM dias;

SELECT '2026-10-01'

A âncora: o primeiro dia, como texto no formato ISO.

date(dia, '+1 day')

A regra: o dia seguinte ao da linha da vez. A função cuida sozinha da virada do mês e dos anos bissextos.

WHERE dia < '2026-10-07'

A parada: quando a linha da vez é o dia 7, nenhum dia novo nasce.

strftime('%w', dia)

Devolve o dia da semana como texto, de '0' (domingo) a '6' (sábado). O CASE troca o número pelo nome (aula CASE).

Para que serve: os dias sem pedido

A Ana quer ver os pedidos de cada dia da primeira quinzena de outubro de 2026. Com um GROUP BY direto na tabela pedidos, só aparecem os dias que tiveram pedido:

SELECT data, COUNT(*) AS pedidos
FROM pedidos
WHERE data BETWEEN '2026-10-01' AND '2026-10-14'
GROUP BY data;

Três linhas. Os outros 11 dias sumiram, porque não existe linha nenhuma deles para agrupar. Para um relatório diário, isso engana: parece que a loja só abriu três dias. A solução é gerar o calendário completo e juntar os pedidos nele com LEFT JOIN (aula LEFT JOIN), que mantém os dias sem par:

WITH RECURSIVE dias(dia) AS (
  SELECT '2026-10-01'
  UNION ALL
  SELECT date(dia, '+1 day') FROM dias WHERE dia < '2026-10-14'
)
SELECT d.dia, COUNT(p.id) AS pedidos
FROM dias AS d
LEFT JOIN pedidos AS p ON p.data = d.dia
GROUP BY d.dia
ORDER BY d.dia;

FROM dias AS d

O calendário manda: todos os 14 dias entram no resultado.

LEFT JOIN pedidos AS p ON p.data = d.dia

Pendura em cada dia os pedidos daquela data. Num dia sem pedido, as colunas de p ficam NULL.

COUNT(p.id)

Conta só os p.id que não são NULL. Por isso os dias vazios dão 0. Com COUNT(*), eles dariam 1, porque a linha do dia existe (aula COUNT()).

4. A hierarquia: da gerente até o último funcionário

Na tabela funcionarios, a coluna chefe_id diz quem é o chefe de cada um. A Marta Lustosa, a gerente, tem chefe_id NULL: ninguém manda nela. Na aula Self Join você juntou a tabela com ela mesma para achar o chefe direto. Mas e o chefe do chefe? E a equipe inteira, em todos os níveis? Com um self-join, você precisaria de um JOIN a mais para cada nível, e teria de saber de antemão quantos níveis existem. A CTE recursiva desce sozinha, quantos níveis houver.

WITH RECURSIVE equipe(id, nome, cargo, nivel) AS (
  SELECT id, nome, cargo, 0
  FROM funcionarios
  WHERE chefe_id IS NULL
  UNION ALL
  SELECT f.id, f.nome, f.cargo, e.nivel + 1
  FROM funcionarios AS f
  JOIN equipe AS e ON f.chefe_id = e.id
)
SELECT id, nome, cargo, nivel
FROM equipe
ORDER BY nivel, nome;

WHERE chefe_id IS NULL

A âncora: quem não tem chefe. Na Aurora, só a Marta. Ela entra com nivel 0.

FROM funcionarios AS f JOIN equipe AS e

A parte recursiva: para a linha da vez (e, alguém que já está na equipe), procura em funcionarios quem tem essa pessoa como chefe.

ON f.chefe_id = e.id

A ligação: "o chefe de f é e". Da Marta (id 1) saem os ids 2, 3, 4 e 5; da Luana (id 5) saem 6, 7 e 8.

e.nivel + 1

Cada degrau para baixo soma 1 ao nível.

(sem WHERE de parada?)

Aqui a parada vem dos próprios dados: quando a linha da vez é alguém que não chefia ninguém (o Davi, por exemplo), o JOIN não acha nenhum par e nenhuma linha nova nasce. Quando todos os da fila são "folhas", acabou.

ORDER BY nivel, nome

A ordem em que a recursão entrega as linhas não é garantida. Para um resultado arrumado, ordene sempre no comando principal.

O organograma desenhado

Dá para ir guardando, em cada rodada, o caminho desde a Marta. Ordenar por ele deixa cada pessoa logo abaixo do seu chefe, e alguns espaços na frente do nome desenham o recuo:

WITH RECURSIVE equipe(id, nome, nivel, caminho) AS (
  SELECT id, nome, 0, nome
  FROM funcionarios
  WHERE chefe_id IS NULL
  UNION ALL
  SELECT f.id, f.nome, e.nivel + 1, e.caminho || ' > ' || f.nome
  FROM funcionarios AS f
  JOIN equipe AS e ON f.chefe_id = e.id
)
SELECT substr('            ', 1, nivel * 4) || nome AS organograma,
       caminho
FROM equipe
ORDER BY caminho;

nome (na âncora)

O caminho começa só com o nome da Marta.

e.caminho || ' > ' || f.nome

A cada degrau, o caminho do chefe ganha > e o nome da pessoa. O || junta textos (aula Funções de Texto).

substr(' ', 1, nivel * 4)

Pega 4 espaços por nível de um texto de 12 espaços: 0 espaços para a Marta, 4 para quem está no nível 1, 8 no nível 2.

ORDER BY caminho

Em ordem alfabética, "Marta Lustosa > Luana Castelo > Davi Nunes" vem logo depois de "Marta Lustosa > Luana Castelo": cada um embaixo do seu chefe.

Subindo: do funcionário até a gerente

A recursão também anda para cima. A âncora agora é o Davi Nunes, o estoquista, e a regra procura o chefe da linha da vez:

WITH RECURSIVE acima(id, nome, chefe_id, passos) AS (
  SELECT id, nome, chefe_id, 0
  FROM funcionarios
  WHERE nome = 'Davi Nunes'
  UNION ALL
  SELECT f.id, f.nome, f.chefe_id, a.passos + 1
  FROM funcionarios AS f
  JOIN acima AS a ON f.id = a.chefe_id
)
SELECT passos, nome FROM acima;

Compare a ligação com a de cima: lá era f.chefe_id = e.id ("quem tem a linha da vez como chefe"); aqui é f.id = a.chefe_id ("quem é o chefe da linha da vez"). Na Marta, chefe_id é NULL, o JOIN não acha ninguém, e a subida para no topo.

5. O cinto de segurança: LIMIT, e UNION × UNION ALL

LIMIT dentro da CTE

No SQLite, a parte recursiva aceita um LIMIT. Ele conta as linhas da CTE inteira e para tudo quando chega no número, mesmo sem WHERE:

WITH RECURSIVE conta(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM conta
  LIMIT 5
)
SELECT n FROM conta;

Um LIMIT no comando principal (SELECT n FROM conta LIMIT 5;) também funciona no SQLite: ele para de pedir linhas depois da quinta, e a recursão para junto. Mesmo assim, a forma mais clara e mais portável é a condição de parada no WHERE. Use o LIMIT como cinto de segurança, não como freio.

UNION ALL × UNION

Com UNION ALL, toda linha nova entra na fila. Com UNION, uma linha igual a outra que já entrou é descartada. Veja uma regra "boba", que sempre gera o número 1:

WITH RECURSIVE sempre_um(n) AS (
  SELECT 1
  UNION
  SELECT 1 FROM sempre_um
)
SELECT n FROM sempre_um;

Uma linha só: o segundo 1 era repetido, foi descartado, a fila esvaziou e acabou. Com UNION ALL no lugar, esse mesmo comando nunca terminaria. O UNION serve para dados que podem andar em círculo (A leva a B, B leva a A); a hierarquia da Aurora não tem círculo, então UNION ALL é o certo, e é mais rápido, porque o SQLite não precisa lembrar de todas as linhas para comparar.

6. Formas diferentes de fazer a mesma coisa

Para "os números de 1 a 5" existem outros caminhos:

a) Digitar os valores

SELECT column1 AS n
FROM (VALUES (1), (2), (3), (4), (5));

Funciona para poucos valores. Para os 31 dias de um mês, ou para 1000 números, ninguém quer digitar. column1 é o nome que o SQLite dá à primeira coluna de um VALUES.

b) A função generate_series (só no programa sqlite3)

O programa sqlite3 traz a função generate_series, que gera a sequência pronta:

$ sqlite3 :memory: "SELECT value FROM generate_series(1, 3);"
1
2
3

Ela é uma extensão, que vem embutida no programa sqlite3, mas não faz parte do SQLite em si. No editor do guia (e em muitos programas que usam o SQLite por dentro), ela não existe:

SELECT value FROM generate_series(1, 3);

c) A CTE recursiva

Funciona em qualquer SQLite e nos outros bancos grandes, para qualquer tamanho, e a regra pode ser qualquer conta: datas, hierarquias, caminhos.

Quando dá errado

WITH RECURSIVE conta(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM conta
)
SELECT n FROM conta;

WITH RECURSIVE conta(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM conta
)
SELECT n FROM conta;

WITH RECURSIVE conta(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM conta WHERE n < 10
)
SELECT n FROM conta;

WITH RECURSIVE conta(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM conta WHERE n <= 10
)
SELECT n FROM conta;

WITH RECURSIVE conta(n) AS (
  SELECT n + 1 FROM conta WHERE n < 10
)
SELECT n FROM conta;

WITH RECURSIVE conta(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1, 'mais um' FROM conta WHERE n < 10
)
SELECT n FROM conta;

WITH RECURSIVE conta(n) AS (
  SELECT 1
  UNION ALL
  SELECT MAX(n) + 1 FROM conta WHERE n < 10
)
SELECT n FROM conta;

WITH RECURSIVE conta(n) AS (
  SELECT 1
  UNION ALL
  SELECT a.n + 1 FROM conta AS a, conta AS b WHERE a.n < 10
)
SELECT n FROM conta;

WITH RECURSIVE conta(n) AS (
  SELECT 1 ORDER BY 1
  UNION ALL
  SELECT n + 1 FROM conta WHERE n < 10
)
SELECT n FROM conta;

WITH conta(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM conta WHERE n < 500
)
SELECT n FROM conta
OPTION (MAXRECURSION 1000);

Referência rápida

Exercícios

Missão final: a equipe da Luana

WITH RECURSIVE equipe(id, nome, cargo, nivel) AS (
  SELECT id, nome, cargo, 0
  FROM funcionarios
  WHERE chefe_id IS NULL
  UNION ALL
  SELECT f.id, f.nome, f.cargo, e.nivel + 1
  FROM funcionarios AS f
  JOIN equipe AS e ON f.chefe_id = e.id
)
SELECT id, nome, cargo, nivel
FROM equipe
ORDER BY nivel, nome;

WITH RECURSIVE equipe(id, nome, cargo, nivel) AS (
  SELECT id, nome, cargo, 0
  FROM funcionarios
  WHERE nome = 'Luana Castelo'
  UNION ALL
  SELECT f.id, f.nome, f.cargo, e.nivel + 1
  FROM funcionarios AS f
  JOIN equipe AS e ON f.chefe_id = e.id
)
SELECT id, nome, cargo, nivel
FROM equipe
ORDER BY nivel, nome;

Também vale WHERE id = 5 na âncora. A parte recursiva não mudou nada: a mesma regra desce a partir de quem estiver no ponto de partida.

Resumo

• Uma CTE recursiva (WITH RECURSIVE) tem três partes: a âncora (o primeiro dominó), a parte recursiva (a regra do próximo), ligadas por UNION ALL, e a condição de parada.
• O SQLite tira uma linha da fila por vez e roda a parte recursiva só com ela; quando a fila esvazia, acabou.
• Com ela você gera sequências (n + 1), calendários (date(dia, '+1 day')) e percorre hierarquias (JOIN pelo chefe_id), para baixo ou para cima.
• Um calendário gerado com LEFT JOIN mostra também os dias sem nenhum pedido.
• Sem parada, o laço não tem fim: o editor interrompe depois de ~4 s com interrupted; fora dele, não há trava. Use WHERE, e o LIMIT como cinto de segurança.
• Na parte recursiva: nada de agregação, a CTE citada uma vez só, e as mesmas colunas da âncora.
• Próxima aula: cálculos que olham para as linhas vizinhas sem juntá-las: Funções de Janela (OVER).