Percorrendo uma Hierarquia

Parte da seção Além do Básico do Journey de SQL da Coddy — lição 21 de 27.

A verdadeira razão pela qual as CTEs recursivas existem: percorrer um relacionamento pai/filho armazenado em uma única tabela. Suponha que employees tenha uma coluna manager_id que aponta de volta para a mesma tabela:

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id
    FROM employees WHERE id = 7              -- anchor: the starting employee
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain c ON e.id = c.manager_id      -- recursive: jump to the manager
)
SELECT * FROM chain

Cada etapa junta o CTE de volta a employees, subindo um nível na árvore. A recursão para naturalmente quando uma linha não tem gerente (JOIN não corresponde a nada).

Desafio

Médio

Tabelas e colunas disponíveis:
categories: id, name, parent_id

Cada categoria tem um parent_id opcional apontando para outra categoria. Retorne todas as categorias que são descendentes da categoria id = 1 (seus filhos, netos, …), mas não a própria categoria 1. Retorne id e name, ordenados por id.

Folha de consulta

Use CTEs recursivas para percorrer relacionamentos pai/filho armazenados em uma única tabela (ex: uma coluna manager_id ou parent_id apontando de volta para a mesma tabela):

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id
    FROM employees WHERE id = 7          -- âncora: linha inicial
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain c ON e.id = c.manager_id  -- recursivo: salto para o pai
)
SELECT * FROM chain

A recursão para naturalmente quando o JOIN não encontra linhas correspondentes (ex: uma linha sem gerente).

Experimente você mesmo

WITH RECURSIVE descendants AS (
    -- âncora: filhos diretos do id 1
    -- recursivo: seus filhos, etc.
)
SELECT id, name FROM descendants ORDER BY id