CTE Recursiva em SQL: Consultar Hierarquias

Lição 6.5 · Tempo de leitura: ~9 min · Tarefas para praticar ↓

Uma CTE recursiva em SQL é uma expressão de tabela comum que faz referência a si mesma, por isso uma única consulta consegue percorrer uma hierarquia de qualquer profundidade: um organograma, uma árvore de categorias, uma conversa de comentários. Nesta lição, você vai aprender a sintaxe WITH RECURSIVE, como o membro âncora e o membro recursivo trabalham juntos, como encontrar todos os subordinados ou todos os gestores de um funcionário, como construir um caminho pela árvore e como parar uma recursão descontrolada.

Na lição anterior, uma CTE era um passo nomeado da consulta, executado uma vez. Os dados hierárquicos precisam de mais. Se cada linha de funcionário guarda o id do seu gestor, um JOIN encontra os subordinados diretos, um segundo JOIN o nível seguinte, e assim por diante, mas não se sabe de antemão quantos níveis existem. Uma CTE recursiva repete a junção por você até não haver mais nada a encontrar.

Os exemplos usam a sintaxe do MySQL 8.0 e também funcionam no MariaDB 10.2+. Os bancos de exemplo do SQLtest são somente leitura, por isso execute os exemplos no playground num banco sem dados de exemplo: cada um cria a sua própria tabela.

Como Funciona uma CTE Recursiva

Uma CTE recursiva tem duas partes unidas por UNION ALL:

WITH RECURSIVE cte_name AS (
    -- Membro âncora: as linhas iniciais
    SELECT ...
    FROM table_name
    WHERE start_condition

    UNION ALL

    -- Membro recursivo: o nível seguinte, encontrado a partir do anterior
    SELECT ...
    FROM table_name
    INNER JOIN cte_name ON join_condition
)
SELECT * FROM cte_name;

• WITH RECURSIVE diz ao banco de dados que a CTE faz referência a si mesma.
• O membro âncora é executado uma vez e devolve as linhas iniciais, como o diretor principal.
• O membro recursivo faz referência à CTE pelo nome. Em cada execução, vê apenas as linhas produzidas pela iteração anterior.
• A recursão para quando uma iteração não devolve linhas. As linhas de todas as iterações formam o resultado.

O Exemplo Mais Simples: uma Sequência de Números

WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM numbers
    WHERE n < 5
)
SELECT n FROM numbers;

Resultado: 5 linhas com os números 1, 2, 3, 4, 5.

A âncora devolve 1. A primeira iteração lê 1 e devolve 2, a seguinte devolve 3, e assim por diante. Quando a iteração anterior produziu 5, a condição n < 5 é falsa, o membro recursivo não devolve nada e a consulta termina. A condição WHERE é a condição de parada: sem ela, a recursão nunca terminaria.

Dados de Exemplo: um Organograma

Uma hierarquia numa tabela costuma ser guardada como uma lista de adjacência: cada linha referencia o seu pai na mesma tabela. Aqui manager_id aponta para outro funcionário, e o CEO não tem gestor:

CREATE TABLE employee (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    manager_id INT,
    title VARCHAR(100)
);

INSERT INTO employee VALUES
    (1, 'Alice Johnson', NULL, 'CEO'),
    (2, 'Bob Smith', 1, 'VP of Engineering'),
    (3, 'Carol White', 1, 'VP of Sales'),
    (4, 'David Brown', 2, 'Engineering Manager'),
    (5, 'Eve Davis', 2, 'Engineering Manager'),
    (6, 'Frank Miller', 3, 'Sales Manager'),
    (7, 'Grace Wilson', 4, 'Senior Developer'),
    (8, 'Henry Moore', 4, 'Developer'),
    (9, 'Ivy Taylor', 5, 'Developer'),
    (10, 'Jack Anderson', 6, 'Sales Representative');

Encontrar Todos os Subordinados

Quem responde a Bob Smith, direta ou indiretamente? A âncora seleciona Bob; o membro recursivo encontra os funcionários cujo gestor está no nível anterior:

WITH RECURSIVE subordinates AS (
    SELECT employee_id, employee_name, title, 0 AS level
    FROM employee
    WHERE employee_name = 'Bob Smith'

    UNION ALL

    SELECT e.employee_id, e.employee_name, e.title, s.level + 1
    FROM employee AS e
    INNER JOIN subordinates AS s ON e.manager_id = s.employee_id
)
SELECT employee_id, employee_name, title, level
FROM subordinates
ORDER BY level, employee_name;

Resultado: 6 linhas: Bob Smith no nível 0, David Brown e Eve Davis no nível 1, Grace Wilson, Henry Moore e Ivy Taylor no nível 2.

A coluna level conta as iterações: começa em 0 na âncora e cresce um a cada passo para baixo. Para excluir o próprio Bob, adicione WHERE level > 0 à consulta final.

Construir a Árvore Completa com um Caminho

Para mostrar todo o organograma, comece pelo funcionário sem gestor e reúna o caminho desde o topo até cada pessoa:

WITH RECURSIVE org_chart AS (
    SELECT
        employee_id,
        employee_name,
        title,
        0 AS level,
        CAST(employee_name AS CHAR(500)) AS path
    FROM employee
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.employee_id,
        e.employee_name,
        e.title,
        oc.level + 1,
        CONCAT(oc.path, ' > ', e.employee_name)
    FROM employee AS e
    INNER JOIN org_chart AS oc ON e.manager_id = oc.employee_id
)
SELECT
    CONCAT(REPEAT('- ', level), employee_name) AS hierarchy,
    title,
    path
FROM org_chart
ORDER BY path;

Resultado: os 10 funcionários na ordem da árvore, cada um com a cadeia de gestores acima dele.

Dois detalhes fazem isto funcionar:

• Ordenar pelo caminho coloca cada funcionário logo a seguir ao seu gestor, e o resultado lê-se como uma árvore.
• CAST(... AS CHAR(500)) na âncora. Os tipos das colunas de uma CTE recursiva vêm do membro âncora. Sem a conversão, path teria o tipo de employee_name, VARCHAR(100), e assim que um caminho passasse de 100 caracteres, o MySQL pararia com o erro "Data too long for column 'path'". Torne suficientemente largas, já na âncora, as colunas de texto que crescem na recursão.

Subir na Árvore: Todos os Gestores de um Funcionário

A mesma técnica funciona no sentido contrário. Para encontrar a cadeia de gestores acima de Ivy Taylor, comece por ela e junte cada linha ao seu pai: a condição de junção inverte-se.

WITH RECURSIVE managers AS (
    SELECT employee_id, employee_name, manager_id, 0 AS steps_up
    FROM employee
    WHERE employee_name = 'Ivy Taylor'

    UNION ALL

    SELECT e.employee_id, e.employee_name, e.manager_id, m.steps_up + 1
    FROM employee AS e
    INNER JOIN managers AS m ON e.employee_id = m.manager_id
)
SELECT employee_name, steps_up
FROM managers
ORDER BY steps_up;

Resultado: 4 linhas: Ivy Taylor (0), Eve Davis (1), Bob Smith (2), Alice Johnson (3).

A recursão para em Alice Johnson: o seu manager_id é NULL, por isso a junção não encontra nada.

Parar uma Recursão Descontrolada

Uma CTE recursiva termina quando uma iteração não devolve linhas. Duas situações quebram essa regra: a falta de uma condição de parada, como numa sequência de números sem WHERE, e os ciclos nos dados, por exemplo quando, por engano, A é gestor de B e B é gestor de A. Nesse caso, as mesmas linhas são encontradas repetidamente.

Limitar a Profundidade

Adicione uma condição sobre o nível ao membro recursivo:

WITH RECURSIVE subordinates AS (
    SELECT employee_id, employee_name, 0 AS level
    FROM employee
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.employee_name, s.level + 1
    FROM employee AS e
    INNER JOIN subordinates AS s ON e.manager_id = s.employee_id
    WHERE s.level < 2
)
SELECT employee_name, level
FROM subordinates;

Resultado: 6 linhas: o CEO, dois vice-presidentes e três gestores; os níveis abaixo de 2 não são expandidos.

O MySQL também tem um limite de segurança: a variável cte_max_recursion_depth (1000 por padrão). Uma consulta que precisa de mais iterações falha com "Recursive query aborted after 1001 iterations". Para uma recursão legitimamente profunda, aumente-o com SET SESSION cte_max_recursion_depth = 10000;. O MariaDB comporta-se de outra forma: ao atingir max_recursive_iterations, para sem erro e devolve as linhas encontradas até então, por isso um resultado incompleto pode passar despercebido.

Ignorar Linhas Já Visitadas

Um limite de profundidade para um ciclo, mas continua a devolver linhas repetidas. Para impedir o próprio ciclo, guarde a lista de ids visitados e não entre numa linha que já está no caminho:

WITH RECURSIVE org_chart AS (
    SELECT employee_id, employee_name, CAST(employee_id AS CHAR(500)) AS visited
    FROM employee
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.employee_name, CONCAT(oc.visited, ',', e.employee_id)
    FROM employee AS e
    INNER JOIN org_chart AS oc ON e.manager_id = oc.employee_id
    WHERE FIND_IN_SET(e.employee_id, oc.visited) = 0
)
SELECT employee_name, visited
FROM org_chart;

Resultado: 10 linhas; por exemplo, Ivy Taylor tem visited = 1,2,5,9.

FIND_IN_SET() devolve a posição de um valor numa lista separada por vírgulas, ou 0 se ele não estiver lá. O PostgreSQL 14+ tem para isto uma cláusula CYCLE integrada.

Regras e Diferenças entre Bancos de Dados

• O membro recursivo tem de fazer referência à CTE exatamente uma vez, no FROM ou no JOIN, e não numa subconsulta.
• No MySQL, o membro recursivo não pode usar funções de agregação nem de janela, GROUP BY, ORDER BY ou DISTINCT. Agregue na consulta final.
• UNION ALL mantém todas as linhas e é a escolha habitual. UNION remove linhas duplicadas e, como efeito colateral, para um ciclo quando as linhas repetidas são idênticas.

Para melhor desempenho, indexe a coluna usada na junção recursiva, como manager_id.

Principais conclusões:

• Uma CTE recursiva faz referência a si mesma e percorre uma hierarquia de qualquer profundidade com WITH RECURSIVE.
• O membro âncora devolve as linhas iniciais; o membro recursivo encontra o nível seguinte a partir do anterior; a recursão para quando uma iteração não devolve linhas.
• A junção e.manager_id = cte.employee_id desce na árvore; e.employee_id = cte.manager_id sobe.
• Um contador level e uma coluna path mostram a posição de cada linha; converta na âncora as colunas de texto que crescem.
• Proteja as consultas com um limite de profundidade ou uma lista de ids visitados; o MySQL para após cte_max_recursion_depth iterações.

Perguntas Frequentes

O que é uma CTE recursiva em SQL?

É uma expressão de tabela comum que faz referência a si mesma. É composta por um membro âncora, que devolve as linhas iniciais, e por um membro recursivo, executado repetidamente, sempre sobre as linhas da iteração anterior, até não surgirem linhas novas. É usada para hierarquias e grafos: organogramas, árvores de categorias, rotas.

Como parar uma recursão infinita numa CTE recursiva?

Adicione uma condição de parada ao membro recursivo, como WHERE level < 10, e proteja-se contra ciclos nos dados acompanhando os ids visitados. O MySQL também interrompe uma consulta após cte_max_recursion_depth iterações (1000 por padrão) e o SQL Server após MAXRECURSION (100 por padrão); o MariaDB para silenciosamente após max_recursive_iterations.

O MySQL suporta CTEs recursivas?

Sim, desde o MySQL 8.0 e o MariaDB 10.2, com a sintaxe WITH RECURSIVE. As versões antigas do MySQL não têm CTEs; aí as hierarquias eram consultadas com procedimentos armazenados ou um número fixo de self-joins.

Por que a minha CTE recursiva falha com "Data too long for column"?

As colunas de uma CTE recursiva recebem os tipos do membro âncora. Se o membro recursivo constrói uma string mais longa, como um caminho, ela deixa de caber. Converta a coluna na âncora para um tipo mais largo, por exemplo CAST(name AS CHAR(500)).