Recorriendo una jerarquía

Parte de la sección Más allá de lo básico del Journey de SQL de Coddy — lección 21 de 27.

La verdadera razón por la que existen las CTE recursivas: recorrer una relación padre/hijo almacenada en una sola tabla. Supongamos que employees tiene una columna manager_id que apunta a la misma tabla:

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id
    FROM employees WHERE id = 7              -- ancla: el empleado inicial
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain c ON e.id = c.manager_id      -- recursivo: saltar al gerente
)
SELECT * FROM chain

Cada paso une el CTE de nuevo con employees, subiendo un nivel en el árbol. La recursión se detiene de forma natural cuando una fila no tiene gerente (JOIN no coincide con nada).

Desafío

Intermedio

Tablas y columnas disponibles:
categories: id, name, parent_id

Cada categoría tiene un parent_id opcional que apunta a otra categoría. Devuelve todas las categorías que sean descendientes de la categoría id = 1 (sus hijos, nietos, …) pero no la categoría 1 en sí misma. Devuelve id y name, ordenados por id.

Hoja de referencia

Utiliza CTEs recursivas para recorrer relaciones padre/hijo almacenadas en una sola tabla (por ejemplo, una columna manager_id o parent_id que apunta a la misma tabla):

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id
    FROM employees WHERE id = 7          -- ancla: fila 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 al padre
)
SELECT * FROM chain

La recursión se detiene de forma natural cuando el JOIN no encuentra filas coincidentes (por ejemplo, una fila sin gerente).

Pruébalo tú mismo

WITH RECURSIVE descendants AS (
    -- ancla: hijos directos del id 1
    -- recursivo: sus hijos, etc.
)
SELECT id, name FROM descendants ORDER BY id