Docs executáveis de SQLite: CTEs Recursivas

Q: O que é uma CTE recursiva no SQLite?

É uma consulta com WITH RECURSIVE que monta o resultado se referenciando a si mesma. Ela tem duas partes ligadas por UNION ALL: a consulta âncora, que devolve as linhas iniciais, e a consulta recursiva, que gera novas linhas a partir do passo anterior. O SQLite repete a parte recursiva até ela não retornar mais nenhuma linha nova.

Q: Quando vale a pena usar WITH RECURSIVE no SQLite?

Use quando precisar percorrer uma árvore ou grafo (funcionários e gerentes, categorias e subcategorias, comentários encadeados) ou gerar uma sequência (todas as datas de um intervalo, números de 1 a 100). Um JOIN comum resolve um ou dois níveis; já a CTE recursiva lida com profundidade arbitrária, mesmo sem você saber de antemão até onde a hierarquia vai.

Q: Como evitar loops infinitos numa CTE recursiva no SQLite?

Garanta uma condição de parada na parte recursiva — um WHERE que cedo ou tarde retorne zero linhas, ou um contador com limite. Em grafos com ciclos, mantenha o caminho visitado em uma coluna e exclua linhas que já apareceram nele. Como rede de segurança, coloque um LIMIT na consulta externa para que uma recursão descontrolada não estoure a memória.

Recursão em SQL parece estranho — até você ver funcionando

A maioria das consultas devolve linhas a partir de dados que já existem. Uma CTE recursiva é diferente: ela vai construindo linhas alimentando a própria saída como entrada, um passo de cada vez, até não sobrar nada novo para incluir. É assim que você percorre uma árvore de profundidade desconhecida, ou gera os números de 1 a 100 sem precisar de uma tabela de números.

A estrutura é sempre a mesma:

WITH RECURSIVE name(columns) AS (
    -- âncora: as linhas iniciais
    SELECT ...
    UNION ALL
    -- recursivo: linhas derivadas do passo anterior
    SELECT ... FROM name WHERE ...
)
SELECT * FROM name;

Âncora em cima, UNION ALL, e a consulta recursiva embaixo. O SQLite roda a âncora uma única vez e, a partir daí, fica executando a parte recursiva — cada iteração usa as linhas geradas na rodada anterior — até não devolver mais nenhuma linha nova. Aí ele para.

Gerar série de números no SQLite: contando de 1 a 10

O exemplo mais simples de CTE recursiva é gerar uma sequência. Não precisa de tabela nenhuma:

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

Vamos acompanhar passo a passo:

A âncora gera uma linha: n = 1.
A etapa recursiva pega essa linha, calcula n + 1 = 2 e, como 2 < 10 é verdadeiro, mantém a linha.
Na iteração seguinte, parte de n = 2 e gera n = 3. E assim por diante.
Quando n chega a 10, 10 < 10 é falso, a etapa recursiva não devolve nenhuma linha e o SQLite encerra.

O WHERE n < 10 é a condição de parada. Sem ele, a consulta roda infinitamente.

Gerando uma série de datas no SQLite

Mesma ideia, mas extremamente útil em relatórios do dia a dia — preencher todos os dias de um intervalo, inclusive aqueles em que nada aconteceu:

WITH RECURSIVE days(d) AS (
    SELECT date('2024-01-01')
    UNION ALL
    SELECT date(d, '+1 day') FROM days WHERE d < '2024-01-07'
)
SELECT d FROM days;

Normalmente, você faria um LEFT JOIN dessa série com uma tabela de eventos para contar corretamente os dias sem nenhum evento. Um GROUP BY date puro simplesmente ignora os dias vazios; já a série de datas te garante uma linha para cada dia, tendo evento ou não.

Percorrendo uma árvore pai-filho no SQLite

O caso de uso clássico de CTE recursiva. Veja uma tabela de funcionários em que cada linha aponta para o seu gestor:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'Ada',    NULL),
    (2, 'Boris',  1),
    (3, 'Carla',  1),
    (4, 'Diego',  2),
    (5, 'Elena',  2),
    (6, 'Farouk', 3);

WITH RECURSIVE org(id, name, manager_id, depth) AS (
    SELECT id, name, manager_id, 0
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, org.depth + 1
    FROM employees e
    JOIN org ON e.manager_id = org.id
)
SELECT printf('%.*c%s', depth * 2, ' ', name) AS tree
FROM org;

A âncora pega a raiz (a pessoa que não tem gerente). Já o passo recursivo faz um join da tabela employees com a própria CTE, achando todo mundo cujo manager_id bate com algum id que já está na CTE. A cada iteração, descemos um nível na hierarquia. O depth é só um contador que a gente adiciona para indentar a saída.

Isso funciona para árvores de qualquer profundidade. Dois níveis, dez níveis — a consulta continua a mesma.

Buscando todos os ancestrais de uma linha específica

Agora inverta o sentido. Em vez de descer a partir da raiz, vamos subir a partir de um funcionário específico para descobrir toda a cadeia de gerentes acima dele:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'Ada',    NULL),
    (2, 'Boris',  1),
    (3, 'Carla',  1),
    (4, 'Diego',  2),
    (5, 'Elena',  2),
    (6, 'Farouk', 3);

WITH RECURSIVE chain(id, name, manager_id) AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE name = 'Diego'
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain ON chain.manager_id = e.id
)
SELECT name FROM chain;

A âncora é o funcionário de partida. A cada passo recursivo, subimos para o gestor (o "pai"). O SQLite encerra a recursão quando chega na raiz — ou seja, quando manager_id IS NULL e o JOIN não encontra mais nada.

Esse padrão cai bem em vários cenários do dia a dia: breadcrumbs, comentários aninhados, caminhos de categorias e qualquer situação em que você precisa "subir até o topo" numa estrutura hierárquica.

Condição de parada e loop infinito na recursão

O bug mais clássico em uma CTE recursiva no SQLite é esquecer a condição de parada — ou escrever uma que nunca é satisfeita. Compare:

-- Executa para sempre:
WITH RECURSIVE bad(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM bad
)
SELECT n FROM bad;

Não existe nenhum WHERE que faça a consulta retornar zero linhas em algum momento. Ou seja, o SQLite vai alegremente tentar contar até o infinito.

Dois hábitos defensivos para evitar esse loop infinito:

Sempre coloque uma cláusula WHERE na parte recursiva que limite o crescimento.
Adicione LIMIT no SELECT externo como rede de segurança enquanto você está desenvolvendo — se você errar a condição de parada, pelo menos a consulta termina.

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

O CTE em si não tem limite, mas o LIMIT 5 interrompe a query externa cedo. O SQLite é esperto o suficiente para não continuar recursando além do que o LIMIT precisa. Isso ajuda na hora de explorar os dados, mas não substitui uma condição de parada de verdade em código de produção.

Ciclos em grafos

Árvores não têm ciclos. Grafos genéricos podem ter — e uma CTE recursiva ingênua vai entrar em loop infinito se os dados tiverem um ciclo. A solução é guardar o caminho já percorrido e se recusar a revisitar nós:

CREATE TABLE edges (a INTEGER, b INTEGER);
INSERT INTO edges VALUES (1, 2), (2, 3), (3, 1), (3, 4);

WITH RECURSIVE walk(node, path) AS (
    SELECT 1, ',1,'
    UNION ALL
    SELECT e.b, path || e.b || ','
    FROM edges e
    JOIN walk ON e.a = walk.node
    WHERE instr(path, ',' || e.b || ',') = 0
)
SELECT node, path FROM walk;

path é uma string com os nós já visitados, separados por vírgula. Antes de incluir um novo nó, a cláusula WHERE verifica se ele ainda não está ali. Sem essa proteção, o ciclo 1 → 2 → 3 → 1 rodaria pra sempre.

O SQL não traz um "conjunto de visitados" pronto — você mesmo monta esse controle, geralmente como string ou fazendo join com a própria CTE acumulada até então.

CTE recursiva vs self join

Se você só precisa descer um ou dois níveis, um self join resolve de forma mais simples e rápida:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'Ada',    NULL),
    (2, 'Boris',  1),
    (3, 'Carla',  1),
    (4, 'Diego',  2),
    (5, 'Elena',  2),
    (6, 'Farouk', 3);

SELECT e.name AS funcionario, m.name AS gerente
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Isso resolve "quem é o gestor direto de cada pessoa". Mas se você precisa de "todo mundo que se reporta à Ada, em qualquer nível abaixo dela" — quando a profundidade é desconhecida —, só uma CTE recursiva resolve isso de forma elegante. Escolha a ferramenta de acordo com a profundidade que você precisa percorrer:

Profundidade fixa e pequena: self join, talvez dois ou três encadeados.
Profundidade desconhecida ou arbitrária: WITH RECURSIVE.

Modelo mental da CTE recursiva

Uma CTE recursiva no SQLite é um laço escrito de forma declarativa:

A âncora é o valor inicial do laço.
A consulta recursiva é o corpo do laço — ela produz o próximo lote de linhas a partir das atuais.
A condição de parada é o teste de saída do laço — quando ela retorna zero linhas, o laço encerra.
O UNION ALL acumula tudo no resultado final.

Quando esse mapeamento entra na sua cabeça, a sintaxe deixa de parecer estranha. Você está escrevendo um for em SQL, só que com outra cara.

Próximo passo: índices

CTEs recursivas percorrem muitas linhas, e o join dentro do passo recursivo é executado a cada iteração. Se a coluna do join não tiver índice, o desempenho despenca rápido. Índices são o assunto do próximo capítulo, e manager_id é exatamente o tipo de coluna que se beneficia de um.

Perguntas frequentes

O que é uma CTE recursiva no SQLite?

É uma consulta com WITH RECURSIVE que monta o resultado se referenciando a si mesma. Ela tem duas partes ligadas por UNION ALL: a consulta âncora, que devolve as linhas iniciais, e a consulta recursiva, que gera novas linhas a partir do passo anterior. O SQLite repete a parte recursiva até ela não retornar mais nenhuma linha nova.

Quando vale a pena usar WITH RECURSIVE no SQLite?

Use quando precisar percorrer uma árvore ou grafo (funcionários e gerentes, categorias e subcategorias, comentários encadeados) ou gerar uma sequência (todas as datas de um intervalo, números de 1 a 100). Um JOIN comum resolve um ou dois níveis; já a CTE recursiva lida com profundidade arbitrária, mesmo sem você saber de antemão até onde a hierarquia vai.

Como evitar loops infinitos numa CTE recursiva no SQLite?

Garanta uma condição de parada na parte recursiva — um WHERE que cedo ou tarde retorne zero linhas, ou um contador com limite. Em grafos com ciclos, mantenha o caminho visitado em uma coluna e exclua linhas que já apareceram nele. Como rede de segurança, coloque um LIMIT na consulta externa para que uma recursão descontrolada não estoure a memória.

Conceitos relacionados

CTEs Recursivas no SQLite: WITH RECURSIVE na prática