CTEs recursivas para jerarquías y grafos

Sintaxis WITH RECURSIVE

Una CTE recursiva se compone de dos partes unidas por UNION ALL (o UNION si se quiere deduplicar). La primera es el ancla (anchor member): devuelve el conjunto inicial. La segunda es el paso recursivo (recursive member): referencia a la propia CTE y produce las siguientes filas. La recursión para cuando el paso deja de devolver filas.

WITH RECURSIVE nombre_cte AS (
    -- 1. Anchor: fila(s) inicial(es)
    SELECT ...
    FROM ...
    WHERE condicion_base

    UNION ALL

    -- 2. Paso recursivo: se ejecuta hasta que no produce filas
    SELECT ...
    FROM tabla t
    JOIN nombre_cte c ON c.id = t.padre_id
)
SELECT * FROM nombre_cte;

MySQL 8+ y PostgreSQL soportan WITH RECURSIVE. En SQL Server la palabra clave RECURSIVE se omite (lo detecta el motor), en Oracle se usa CONNECT BY o WITH RECURSIVE.

Jerarquía de empleados

El caso de uso clásico: una tabla empleados con id, nombre, jefe_id. Queremos obtener el árbol completo a partir de un jefe, incluyendo el nivel de profundidad de cada empleado.

CREATE TABLE empleados (
    id INT PRIMARY KEY,
    nombre VARCHAR(100),
    jefe_id INT REFERENCES empleados(id)
);

INSERT INTO empleados VALUES
    (1, 'Alicia (CEO)', NULL),
    (2, 'Bernardo', 1),
    (3, 'Carla', 1),
    (4, 'David', 2),
    (5, 'Elena', 2),
    (6, 'Fran', 3);

WITH RECURSIVE organigrama AS (
    -- Ancla: el CEO (sin jefe)
    SELECT id, nombre, jefe_id, 1 AS nivel, CAST(nombre AS VARCHAR(500)) AS ruta
    FROM empleados
    WHERE jefe_id IS NULL

    UNION ALL

    -- Paso: empleados cuyo jefe ya esta en la CTE
    SELECT e.id, e.nombre, e.jefe_id, o.nivel + 1,
           CAST(o.ruta || ' > ' || e.nombre AS VARCHAR(500))
    FROM empleados e
    JOIN organigrama o ON e.jefe_id = o.id
)
SELECT nivel, nombre, ruta
FROM organigrama
ORDER BY ruta;

Resultado (con nivel y ruta completa):

 nivel |    nombre    |              ruta
-------+--------------+--------------------------------
   1   | Alicia (CEO) | Alicia (CEO)
   2   | Bernardo     | Alicia (CEO) > Bernardo
   3   | David        | Alicia (CEO) > Bernardo > David
   3   | Elena        | Alicia (CEO) > Bernardo > Elena
   2   | Carla        | Alicia (CEO) > Carla
   3   | Fran         | Alicia (CEO) > Carla > Fran

La columna nivel se calcula acumulando en el paso recursivo. Hacer CAST de la columna ruta es necesario en PostgreSQL para que el tipo VARCHAR sea suficientemente grande en todas las iteraciones; si se omite, el motor puede asumir que la longitud máxima es la del ancla y truncar.

Recorrido de grafos y detección de ciclos

Cuando la estructura no es un árbol sino un grafo con ciclos (p. ej. una red social con amistades mutuas), UNION ALL provoca un bucle infinito. Hay dos formas de protegerse:

Opción 1: acumular el camino recorrido

WITH RECURSIVE caminos AS (
    SELECT origen, destino,
           ARRAY[origen, destino] AS camino,
           1 AS saltos
    FROM conexiones
    WHERE origen = 'Madrid'

    UNION ALL

    SELECT c.origen, x.destino,
           c.camino || x.destino,
           c.saltos + 1
    FROM caminos c
    JOIN conexiones x ON c.destino = x.origen
    WHERE NOT x.destino = ANY(c.camino)  -- evita ciclos
      AND c.saltos < 5                   -- limita profundidad
)
SELECT * FROM caminos WHERE destino = 'Roma';

El truco: el array camino guarda todos los nodos ya visitados, y la cláusula NOT destino = ANY(camino) impide repetirlos. Además se pone un límite de saltos como salvaguarda.

Opción 2: usar CYCLE (SQL estándar moderno)

PostgreSQL 14+ soporta la cláusula CYCLE del estándar SQL:2016 que automatiza la detección:

WITH RECURSIVE caminos AS (
    SELECT origen, destino FROM conexiones WHERE origen = 'Madrid'
    UNION ALL
    SELECT c.origen, x.destino
    FROM caminos c JOIN conexiones x ON c.destino = x.origen
)
CYCLE destino SET hay_ciclo USING ruta
SELECT * FROM caminos WHERE NOT hay_ciclo;

Generación de series

Otro uso frecuente: generar datos sintéticos. PostgreSQL tiene generate_series, pero MySQL no, y la CTE recursiva soluciona el problema:

-- Serie de números del 1 al 100
WITH RECURSIVE numeros(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM numeros WHERE n < 100
)
SELECT * FROM numeros;

-- Serie de fechas para un calendario
WITH RECURSIVE calendario(dia) AS (
    SELECT DATE '2026-01-01'
    UNION ALL
    SELECT dia + INTERVAL '1 day'
    FROM calendario
    WHERE dia < DATE '2026-12-31'
)
SELECT EXTRACT(ISODOW FROM dia) AS dia_semana, COUNT(*)
FROM calendario
GROUP BY 1
ORDER BY 1;

Caso real: lista de materiales (BOM)

En manufactura, un producto está compuesto por subproductos, que a su vez están compuestos por piezas básicas. Calcular la cantidad total de cada pieza básica para fabricar 10 unidades del producto final requiere recursión.

CREATE TABLE composicion (
    producto_id INT,
    componente_id INT,
    cantidad INT
);

WITH RECURSIVE bom AS (
    -- Ancla: el producto final con cantidad 10
    SELECT 1 AS producto_id, 1 AS componente_id, 10 AS cantidad_total
    UNION ALL
    SELECT b.producto_id, c.componente_id,
           b.cantidad_total * c.cantidad
    FROM bom b
    JOIN composicion c ON b.componente_id = c.producto_id
)
SELECT componente_id, SUM(cantidad_total) AS total
FROM bom
WHERE componente_id NOT IN (SELECT producto_id FROM composicion)  -- solo hojas
GROUP BY componente_id
ORDER BY componente_id;

Este patrón evita un procedimiento almacenado y expresa la lógica jerárquica en una sola consulta declarativa. Es legible, mantenible y aprovechable por cualquier ORM.

Consideraciones de rendimiento

Las CTEs recursivas pueden ser lentas si el árbol es profundo o ancho. Recomendaciones:

• Limitar la profundidad con un contador (WHERE nivel < 10). Evita recursiones sin fin ante datos corruptos.
• Indexar la columna de enlace (jefe_id, padre_id). Cada iteración hace un JOIN a la CTE, y sin índice el coste es cuadrático.
• Evitar procesar el árbol completo si solo necesitas una rama: empezar el ancla con WHERE id = ? para la raíz concreta.
• En PostgreSQL usar la cláusula MATERIALIZED o NOT MATERIALIZED (12+) para forzar o desaconsejar la materialización explícita de la CTE según el patrón de acceso.