CTE Recursivas en SQL: recorrer jerarquías y estructuras complejas

Las CTE recursivas permiten recorrer estructuras jerárquicas: árboles, categorías, empleados, rutas, dependencias y más. Son una herramienta avanzada para navegar relaciones padre‑hijo.

1. ¿Qué es una CTE recursiva?

Una CTE recursiva se llama a sí misma para recorrer estructuras jerárquicas.

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

2. Sintaxis

WITH RECURSIVE nombre_cte AS (
    -- Caso base
    SELECT ...

    UNION ALL

    -- Caso recursivo
    SELECT ...
    FROM nombre_cte
)
SELECT * FROM nombre_cte;
    

3. Recorrer jerarquías

WITH RECURSIVE jerarquia AS (
    SELECT id, nombre, jefe_id, 1 AS nivel
    FROM empleados
    WHERE jefe_id IS NULL

    UNION ALL

    SELECT e.id, e.nombre, e.jefe_id, j.nivel + 1
    FROM empleados e
    JOIN jerarquia j ON e.jefe_id = j.id
)
SELECT * FROM jerarquia;
    

4. Calcular niveles de profundidad

WITH RECURSIVE categorias AS (
    SELECT id, nombre, padre_id, 1 AS nivel
    FROM categorias
    WHERE padre_id IS NULL

    UNION ALL

    SELECT c.id, c.nombre, c.padre_id, cat.nivel + 1
    FROM categorias c
    JOIN categorias cat ON c.padre_id = cat.id
)
SELECT * FROM categorias ORDER BY nivel;
    

5. Prevención de ciclos

WITH RECURSIVE rutas AS (
    SELECT id, destino, origen, ARRAY[id] AS visitados
    FROM conexiones

    UNION ALL

    SELECT c.id, c.destino, c.origen, r.visitados || c.id
    FROM conexiones c
    JOIN rutas r ON c.origen = r.destino
    WHERE NOT c.id = ANY(r.visitados)
)
SELECT * FROM rutas;
    

6. Ejemplos prácticos

-- Árbol de categorías
WITH RECURSIVE arbol AS (
    SELECT id, nombre, padre_id, 1 AS nivel
    FROM categorias
    WHERE padre_id IS NULL

    UNION ALL

    SELECT c.id, c.nombre, c.padre_id, a.nivel + 1
    FROM categorias c
    JOIN arbol a ON c.padre_id = a.id
)
SELECT * FROM arbol ORDER BY nivel;

-- Cadena de mando
WITH RECURSIVE mando AS (
    SELECT id, nombre, jefe_id
    FROM empleados
    WHERE id = 1

    UNION ALL

    SELECT e.id, e.nombre, e.jefe_id
    FROM empleados e
    JOIN mando m ON e.id = m.jefe_id
)
SELECT * FROM mando;
    

7. Ejercicios para practicar

WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 10
)
SELECT * FROM nums;
            

WITH RECURSIVE jerarquia AS (
    SELECT * FROM empleados WHERE jefe_id IS NULL
    UNION ALL
    SELECT e.* FROM empleados e
    JOIN jerarquia j ON e.jefe_id = j.id
)
SELECT * FROM jerarquia;
            

WITH RECURSIVE cat AS (
    SELECT id, nombre, padre_id, 1 AS nivel FROM categorias WHERE padre_id IS NULL
    UNION ALL
    SELECT c.id, c.nombre, c.padre_id, cat.nivel + 1
    FROM categorias c JOIN cat ON c.padre_id = cat.id
)
SELECT * FROM cat;
            

WITH RECURSIVE rutas AS (
    SELECT id, destino, origen, ARRAY[id] AS visitados FROM conexiones
    UNION ALL
    SELECT c.id, c.destino, c.origen, r.visitados || c.id
    FROM conexiones c JOIN rutas r ON c.origen = r.destino
    WHERE NOT c.id = ANY(r.visitados)
)
SELECT * FROM rutas;
            

-- Sirven para recorrer jerarquías y estructuras padre‑hijo.

8. Mini‑proyecto: Árbol completo de categorías

WITH RECURSIVE arbol AS (
    SELECT id, nombre, padre_id, 1 AS nivel
    FROM categorias
    WHERE padre_id IS NULL

    UNION ALL

    SELECT c.id, c.nombre, c.padre_id, a.nivel + 1
    FROM categorias c
    JOIN arbol a ON c.padre_id = a.id
)
SELECT * FROM arbol ORDER BY nivel;
    

9. Errores comunes

• No definir correctamente el caso base.
• Crear recursión infinita.
• No prevenir ciclos.
• Confundir CTE recursivas con CTE normales.

10. Preguntas frecuentes

¿Las CTE recursivas son rápidas?

Depende del tamaño de la jerarquía.

¿Todas las bases de datos las soportan?

Sí, todas las modernas.

Siguiente guía