CTEs Recursivas para Consultas de Datos Jerárquicos

Visión General

Una Common Table Expression (CTE) recursiva permite que una query se referencie a sí misma, así podés recorrer datos jerárquicos almacenados en una sola tabla. Tiene dos partes: un anchor member que selecciona las filas iniciales, y un recursive member que joinea esas filas de vuelta a la tabla fuente. Esa combinación sirve para org charts, árboles de categorías, file systems, comentarios anidados y cualquier relación parent-child ligada con una self-referencing foreign key. ¿Datasets grandes? Revisá los patrones de batch processing para operaciones masivas eficientes. Los ejemplos acá funcionan en PostgreSQL 16+, MySQL 8.0+, SQLite 3.8.4+, SQL Server 2019+, Oracle 21c+, Snowflake, BigQuery y DuckDB 0.10+ — la sintaxis es similar entre engines, con diferencias menores notadas en la sección Variantes.

Cuándo Usarlo

Usá una CTE recursiva cuando necesitás recorrer una jerarquía desde un punto de partida conocido. Los casos comunes incluyen encontrar todos los reports directos e indirectos de un manager, listar todas las subcategorías bajo una categoría padre, recorrer un directory tree, fetchear un comentario y todas sus respuestas, explotar un bill of materials en componentes, o seguir dependencias transitivas.

Cuándo NO Usarlo

Evitá la CTE recursiva para queries planas que no recorran una jerarquía; una CTE regular o un subquery es más simple. Tampoco es la herramienta correcta para jerarquías muy profundas (algunas bases de datos alcanzan límites de recursión), graph traversal con ciclos (las CTEs recursivas no manejan ciclos de forma nativa) o problemas de shortest path (para eso usá una graph database o un algoritmo especializado).

Solución

Estructura básica de CTE recursiva

WITH RECURSIVE hierarchy AS (
    -- Anchor member: punto de partida
    SELECT
        id,
        parent_id,
        name,
        1 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive member: join de vuelta a la CTE
    SELECT
        c.id,
        c.parent_id,
        c.name,
        h.depth + 1 AS depth
    FROM categories c
    INNER JOIN hierarchy h ON c.parent_id = h.id
)
SELECT * FROM hierarchy ORDER BY depth, name;

Org chart: todos los reports de un manager específico

WITH RECURSIVE reports AS (
    -- Anchor: reports directos del manager 5
    SELECT
        employee_id,
        manager_id,
        employee_name,
        1 AS depth,
        CAST(manager_id AS VARCHAR(1000)) AS path
    FROM employees
    WHERE manager_id = 5

    UNION ALL

    -- Recursive: reports de reports
    SELECT
        e.employee_id,
        e.manager_id,
        e.employee_name,
        r.depth + 1,
        r.path || ' -> ' || CAST(e.manager_id AS VARCHAR)
    FROM employees e
    INNER JOIN reports r ON e.manager_id = r.employee_id
)
SELECT
    employee_id,
    employee_name,
    depth,
    path
FROM reports
ORDER BY depth, employee_name;

Árbol de categorías con path completo

WITH RECURSIVE category_tree AS (
    SELECT
        id,
        parent_id,
        name,
        CAST(name AS VARCHAR(1000)) AS full_path,
        1 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT
        c.id,
        c.parent_id,
        c.name,
        ct.full_path || ' / ' || c.name,
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT
    id,
    name,
    full_path,
    depth
FROM category_tree
ORDER BY full_path;

Encontrar todos los ancestors (traversal bottom-up)

WITH RECURSIVE ancestors AS (
    -- Anchor: nodo inicial
    SELECT
        id,
        parent_id,
        name,
        1 AS depth
    FROM categories
    WHERE id = 42  -- Empezar desde un nodo específico

    UNION ALL

    -- Recursive: subir al parent
    SELECT
        c.id,
        c.parent_id,
        c.name,
        a.depth + 1
    FROM categories c
    INNER JOIN ancestors a ON c.id = a.parent_id
)
SELECT * FROM ancestors ORDER BY depth DESC;

Agregar a través de la jerarquía

Si necesitás agregar datos a través de una jerarquía — por ejemplo, contar productos por categoría incluyendo subcategorías — podés pairar la CTE recursiva con un pipeline ETL con pandas para procesamiento downstream, o hacerlo todo en SQL:

WITH RECURSIVE category_tree AS (
    SELECT id, parent_id, name, 1 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.parent_id, c.name, ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT
    ct.id,
    ct.name,
    ct.depth,
    COUNT(p.id) AS product_count,
    COALESCE(SUM(p.price), 0) AS total_value
FROM category_tree ct
LEFT JOIN products p ON p.category_id = ct.id
GROUP BY ct.id, ct.name, ct.depth
ORDER BY ct.depth, ct.name;

Roll-up: sumar valores de hijos a todos los ancestors

WITH RECURSIVE descendants AS (
    SELECT id, parent_id, name, amount, 1 AS depth
    FROM nodes
    WHERE id = 1  -- Root node

    UNION ALL

    SELECT
        n.id,
        n.parent_id,
        n.name,
        n.amount,
        d.depth + 1
    FROM nodes n
    INNER JOIN descendants d ON n.parent_id = d.id
),
rollup AS (
    SELECT
        d.id,
        d.name,
        SUM(child.amount) AS total_descendant_amount
    FROM descendants d
    INNER JOIN descendants child
        ON child.id = d.id OR child.depth > d.depth
    -- Este approach es simplificado; un rollup más preciso
    -- requiere construir el path y checkear containment
    GROUP BY d.id, d.name
)
SELECT * FROM rollup ORDER BY total_descendant_amount DESC;

Detección de ciclos

WITH RECURSIVE traversal AS (
    SELECT
        id,
        parent_id,
        CAST(id AS VARCHAR(1000)) AS path,
        1 AS depth,
        false AS has_cycle
    FROM nodes
    WHERE id = 1

    UNION ALL

    SELECT
        n.id,
        n.parent_id,
        t.path || ' -> ' || CAST(n.id AS VARCHAR),
        t.depth + 1,
        POSITION(CAST(n.id AS VARCHAR) IN t.path) > 0 AS has_cycle
    FROM nodes n
    INNER JOIN traversal t ON n.parent_id = t.id
    WHERE t.has_cycle = false
    AND t.depth < 100  -- Safety limit
)
SELECT * FROM traversal WHERE has_cycle = true;

Limitar profundidad de recursión

WITH RECURSIVE limited_tree AS (
    SELECT id, parent_id, name, 1 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.parent_id, c.name, lt.depth + 1
    FROM categories c
    INNER JOIN limited_tree lt ON c.parent_id = lt.id
    WHERE lt.depth < 5  -- Solo 5 niveles de profundidad
)
SELECT * FROM limited_tree ORDER BY depth, name;

Explosión de bill of materials

WITH RECURSIVE bom AS (
    -- Anchor: assembly top-level
    SELECT
        component_id,
        assembly_id,
        quantity,
        1 AS level,
        CAST(component_id AS VARCHAR(1000)) AS component_path
    FROM bill_of_materials
    WHERE assembly_id = 'PRODUCT-001'

    UNION ALL

    -- Recursive: componentes de componentes
    SELECT
        b.component_id,
        b.assembly_id,
        b.quantity * bom.quantity AS total_quantity,
        bom.level + 1,
        bom.component_path || ' -> ' || CAST(b.component_id AS VARCHAR)
    FROM bill_of_materials b
    INNER JOIN bom ON b.assembly_id = bom.component_id
)
SELECT
    component_id,
    level,
    total_quantity,
    component_path
FROM bom
ORDER BY level, component_id;

Explicación

Acá te explico qué pasa bajo el capó cuando corrés una CTE recursiva. El engine de la base de datos evalúa el anchor member primero — tu punto de partida, las filas root donde parent_id IS NULL. Lo que sea que el anchor retorne se convierte en el working set contra el que joinea el recursive member. Después el engine corre el recursive member, que joinea el working set de vuelta a la tabla fuente. Las filas nuevas que produce ese join se agregan al working set, y el proceso se repite hasta que el recursive member no produce filas nuevas. Esa es la condición de terminación: cuando el join no retorna nada nuevo, la recursión para.

El UNION ALL combina todos los resultados intermedios en el output final. UNION (sin ALL) deduplicaría en cada paso, lo cual agrega overhead que casi nunca necesitás en un tree traversal — cada fila es un path único de root a nodo.

La mayoría de los engines defaultan a depth-first traversal. Cada rama se sigue hasta su hoja antes de que el engine vuelva atrás y pase al siguiente sibling. Si querés output breadth-first (todos los nodos a depth 1, después todos a depth 2, etc.), agregá ORDER BY depth a la query final. El orden de traversal y el orden de output son cosas separadas — el engine construye el result set completo antes de aplicar tu ORDER BY.

El rendimiento depende de tres cosas: el tamaño de la jerarquía, si parent_id está indexado, y qué tan profunda es la recursión. Sin índice en parent_id, cada paso recursivo hace un full table scan. En una tabla de 10.000 filas con 5 niveles de profundidad, son 5 full scans en lugar de 5 index lookups. Vi CTEs recursivas pasar de 30 segundos a menos de 100ms solo agregando CREATE INDEX idx_parent ON categories(parent_id).

Distintos engines tienen distintos límites de recursión. SQL Server defaulta a 100 niveles (override con OPTION (MAXRECURSION N)). PostgreSQL no tiene hard limit pero va a chocar con constraints de memoria en árboles muy profundos. MySQL 8.0+ defaulta a 1000 iteraciones. Snowflake y BigQuery manejan recursión profunda bien pero cobran por data escaneada, así que una CTE recursiva sobre una tabla grande puede salir cara rápido.

Un gotcha que pisé más de una vez: datos cíclicos. Si tus relaciones parent-child contienen un ciclo (A → B → C → A), la CTE recursiva va a loopear para siempre — o hasta que choque con el límite de recursión y tire error. Por eso el pattern de detección de ciclos de arriba importa. Siempre agregá un depth limit como safety net, incluso si pensás que tus datos son acíclicos. Migraciones de datos, imports mal hechos y ediciones manuales pueden introducir ciclos en lo que debería ser un árbol limpio.

Variantes

PostgreSQL: usar ARRAY para path

WITH RECURSIVE category_tree AS (
    SELECT
        id,
        parent_id,
        name,
        ARRAY[id] AS path,
        1 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT
        c.id,
        c.parent_id,
        c.name,
        ct.path || c.id,
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
    WHERE c.id != ALL(ct.path)  -- Prevención de ciclos
)
SELECT id, name, path, depth FROM category_tree ORDER BY path;

MySQL 8.0+: sintaxis de CTE recursiva

WITH RECURSIVE org_tree AS (
    SELECT employee_id, manager_id, employee_name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.manager_id, e.employee_name, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT * FROM org_tree WHERE level <= 3 ORDER BY level;

SQL Server: sin keyword RECURSIVE

WITH org_tree AS (
    SELECT employee_id, manager_id, employee_name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.manager_id, e.employee_name, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT * FROM org_tree OPTION (MAXRECURSION 100);

Snowflake: usar CONNECT BY (alternativa)

SELECT
    employee_id,
    manager_id,
    employee_name,
    LEVEL AS depth,
    SYS_CONNECT_BY_PATH(employee_name, ' -> ') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY employee_name;

Buenas Prácticas

• Siempre incluí una columna depth o level. Vas a agradecerlo cuando debuggeás — y te da una forma barata de limitar la recursión.
• Agregá un safety limit como WHERE depth < N para prevenir recursión infinita en datos cíclicos.
• Usá UNION ALL en lugar de UNION; UNION deduplica, lo cual agrega overhead que usualmente no necesitás.
• Construí una columna path para debugging, así podés ver la ruta exacta de recorrido.
• Testeá primero con datasets pequeños, porque las CTEs recursivas pueden ser lentas en tablas grandes. Para agregaciones sobre jerarquías grandes, considerá Spark groupBy aggregation.
• Indexá parent_id e id; el join recursivo golpea esas columnas repetidamente.
• En SQL Server, podés subir el límite default de 100 con OPTION (MAXRECURSION N).

Para una guía más profunda, consultá Transform Data in the Warehouse with dbt.

Errores Comunes

• Olvidar el anchor member. Sin un punto de partida la CTE no retorna nada; el anchor tiene que seleccionar filas que no dependan de la CTE.
• Usar UNION en lugar de UNION ALL. UNION deduplica resultados y agrega overhead, así que usá UNION ALL a menos que realmente necesites deduplicación.
• Saltearse la detección de ciclos. Los datos cíclicos causan recursión infinita, así que agregá una columna path y verificá repeticiones, o poné un límite de profundidad.
• No indexar parent_id. El join recursivo hace JOIN c ON c.parent_id = h.id; sin índice en parent_id, cada nivel se convierte en un full table scan.
• Esperar orden breadth-first. Las CTEs recursivas retornan depth-first por default. Usá ORDER BY depth si querés output breadth-first.

Resumen

• Una CTE recursiva tiene dos partes: un anchor member (caso base) y un recursive member (joinea de vuelta a la CTE). El engine corre el anchor, después loopea el recursive member hasta que no produce filas nuevas.
• Usá UNION ALL, no UNION — la deduplicación agrega overhead que no necesitás en tree traversal.
• Siempre agregá un depth limit (WHERE depth < N) como safety net contra datos cíclicos, incluso si pensás que tu árbol está limpio.
• Indexá parent_id — sin índice, cada paso recursivo hace un full table scan. Vi 30s → 100ms solo agregando un índice.
• La mayoría de los engines defaultan a depth-first. Agregá ORDER BY depth si querés breadth-first.
• Soportado en PostgreSQL 16+, MySQL 8.0+, SQLite 3.8.4+, SQL Server 2019+, Oracle 21c+, Snowflake, BigQuery y DuckDB 0.10+. La sintaxis es bastante similar; algunos engines requieren el keyword RECURSIVE, otros no.

Código complementario: El repo companion de sql-cte-recursive-hierarchy tiene scripts SQL ejecutables con datos de prueba para cada ejemplo de arriba, más una guía de setup multi-database.

See Also

• PostgreSQL 16 Documentation — WITH Queries (Common Table Expressions)
• MySQL 8.0 Reference Manual — Recursive Common Table Expressions
• SQLite Documentation — Recursive CTEs
• Microsoft Learn — Recursive CTEs in SQL Server
• Snowflake Documentation — Hierarchical Queries