CTE recursivo

Una CTE recursiva es una CTE que se referencia a sí misma. Esto permite resolver problemas que requieren iterar sobre datos jerárquicos o generar secuencias, algo imposible con SQL convencional. MySQL soporta CTEs recursivas desde la versión 8.0.

Los casos de uso más habituales son recorrer árboles (jerarquías de empleados, categorías padre-hijo, comentarios anidados) y generar series de datos (secuencias numéricas, rangos de fechas).

Sintaxis

WITH RECURSIVE nombre_cte AS (
    -- Caso base (miembro ancla)
    SELECT ...
 
    UNION ALL
 
    -- Caso recursivo (referencia a sí misma)
    SELECT ...
    FROM nombre_cte
    WHERE condicion_de_parada
)
SELECT * FROM nombre_cte;

Una CTE recursiva tiene dos partes unidas por UNION ALL. El caso base (o miembro ancla) es una consulta normal que define el punto de partida. El caso recursivo referencia a la propia CTE y define cómo se genera cada nuevo nivel de resultados. La recursión se detiene cuando el caso recursivo no produce nuevas filas.

La palabra RECURSIVE es obligatoria, aunque solo una de las CTEs sea recursiva.

Jerarquía de empleados

El ejemplo clásico: recorrer la estructura organizativa completa a partir del director general. Nuestra tabla empleados tiene un campo supervisor_id que apunta al jefe de cada empleado, creando un árbol jerárquico.

WITH RECURSIVE jerarquia AS (
    SELECT
        id,
        nombre,
        puesto,
        supervisor_id,
        0 AS nivel,
        CAST(nombre AS CHAR(500)) AS ruta
    FROM empleados
    WHERE supervisor_id IS NULL
 
    UNION ALL
 
    SELECT
        e.id,
        e.nombre,
        e.puesto,
        e.supervisor_id,
        j.nivel + 1,
        CONCAT(j.ruta, ' > ', e.nombre)
    FROM empleados e
    JOIN jerarquia j ON e.supervisor_id = j.id
)
SELECT
    CONCAT(REPEAT('  ', nivel), nombre) AS empleado,
    puesto,
    nivel
FROM jerarquia
ORDER BY ruta;

El caso base selecciona a Ricardo (el único sin supervisor). El caso recursivo busca empleados cuyo supervisor_id coincida con el id de alguien ya en la jerarquía, incrementando el nivel en 1. La columna ruta acumula los nombres separados por >, lo que permite ordenar alfabéticamente respetando la estructura del árbol.

La indentación con REPEAT(' ', nivel) produce una representación visual del organigrama: Ricardo dirige todo, Alberto y Sofía son directores de área, y debajo de cada uno están sus equipos.

Encontrar la cadena de mando

Puedes invertir la dirección: en lugar de bajar desde el director, subir desde un empleado hasta la cima. La cadena de supervisores de Raúl:

WITH RECURSIVE cadena AS (
    SELECT id, nombre, puesto, supervisor_id, 0 AS nivel
    FROM empleados
    WHERE nombre = 'Raúl'
 
    UNION ALL
 
    SELECT e.id, e.nombre, e.puesto, e.supervisor_id, c.nivel + 1
    FROM empleados e
    JOIN cadena c ON e.id = c.supervisor_id
)
SELECT nombre, puesto, nivel
FROM cadena;

Aquí el caso base es Raúl y el caso recursivo busca el supervisor de cada persona ya en la cadena. El resultado muestra que Raúl reporta a Natalia, que reporta a Sofía, que reporta a Ricardo. Tres niveles de distancia hasta el director general.

Jerarquía de categorías

La tabla categorias también tiene una estructura jerárquica con categoria_padre_id. Podemos recorrer el árbol completo:

WITH RECURSIVE arbol AS (
    SELECT
        id,
        nombre,
        categoria_padre_id,
        0 AS nivel,
        CAST(nombre AS CHAR(500)) AS ruta
    FROM categorias
    WHERE categoria_padre_id IS NULL
 
    UNION ALL
 
    SELECT
        c.id,
        c.nombre,
        c.categoria_padre_id,
        a.nivel + 1,
        CONCAT(a.ruta, ' > ', c.nombre)
    FROM categorias c
    JOIN arbol a ON c.categoria_padre_id = a.id
)
SELECT
    CONCAT(REPEAT('  ', nivel), nombre) AS categoria,
    nivel
FROM arbol
ORDER BY ruta;

Nuestra jerarquía solo tiene dos niveles (categorías principales y subcategorías), pero la misma consulta funcionaría con cualquier profundidad. Si mañana añadiéramos "Ultrabooks" como subcategoría de "Portátiles", la CTE recursiva la mostraría automáticamente en el nivel 2.

Generar una secuencia de números

Las CTEs recursivas no solo sirven para recorrer datos existentes. También pueden generar datos. Una secuencia del 1 al 10:

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

El caso base produce el 1. El caso recursivo suma 1 al número anterior mientras sea menor que 10. Cuando n llega a 10, la condición WHERE n < 10 deja de cumplirse y la recursión se detiene.

Esto parece trivial, pero es enormemente útil cuando necesitas generar rangos para rellenar huecos en informes.

Serie de fechas para informes

Un problema habitual en informes: si un mes no tiene datos, no aparece en el resultado del GROUP BY. Con una CTE recursiva puedes generar todas las fechas y después hacer un LEFT JOIN:

WITH RECURSIVE meses AS (
    SELECT DATE('2025-10-01') AS fecha
 
    UNION ALL
 
    SELECT fecha + INTERVAL 1 MONTH
    FROM meses
    WHERE fecha < '2026-01-01'
)
SELECT
    DATE_FORMAT(m.fecha, '%Y-%m') AS mes,
    COUNT(p.id) AS pedidos,
    COALESCE(ROUND(SUM(p.total), 2), 0) AS ingresos
FROM meses m
LEFT JOIN pedidos p
    ON DATE_FORMAT(p.fecha_pedido, '%Y-%m') = DATE_FORMAT(m.fecha, '%Y-%m')
GROUP BY m.fecha
ORDER BY m.fecha;

La CTE genera una fila por cada mes del rango. El LEFT JOIN conecta los pedidos con su mes correspondiente. Si algún mes no tuviera pedidos, aparecería con 0 gracias al COALESCE. Diciembre tiene más pedidos (11) pero noviembre generó más ingresos, probablemente por pedidos de mayor importe como el ASUS ROG Zephyrus de 1899.99 euros.

Jerarquía con datos agregados

Puedes combinar una CTE recursiva con CTEs no recursivas para enriquecer la jerarquía con datos calculados. Empleados con su nivel jerárquico y los ingresos que generaron:

WITH RECURSIVE jerarquia AS (
    SELECT id, nombre, puesto, supervisor_id, 0 AS nivel
    FROM empleados
    WHERE supervisor_id IS NULL
 
    UNION ALL
 
    SELECT e.id, e.nombre, e.puesto, e.supervisor_id, j.nivel + 1
    FROM empleados e
    JOIN jerarquia j ON e.supervisor_id = j.id
)
SELECT
    j.nombre,
    j.puesto,
    j.nivel,
    COALESCE(COUNT(p.id), 0) AS pedidos,
    COALESCE(ROUND(SUM(p.total), 2), 0) AS ingresos
FROM jerarquia j
LEFT JOIN pedidos p ON j.id = p.empleado_id
GROUP BY j.id, j.nombre, j.puesto, j.nivel
ORDER BY j.nivel, ingresos DESC;

La jerarquía muestra claramente que solo los empleados de nivel 2 del equipo de ventas (Natalia, Daniel y Patricia) gestionan pedidos directamente. Los directores y el personal de almacén no tienen pedidos asignados.

Límite de recursión

MySQL tiene un límite por defecto de 1000 iteraciones para las CTEs recursivas, controlado por la variable cte_max_recursion_depth. Si la recursión supera ese límite, MySQL devuelve un error.

Puedes ajustar el límite si necesitas más iteraciones:

SET SESSION cte_max_recursion_depth = 5000;

Este límite es una protección contra recursiones infinitas. Si tu CTE recursiva produce un error de profundidad, revisa primero que la condición de parada sea correcta antes de aumentar el límite.

Cuándo usar CTEs recursivas

Las CTEs recursivas son la herramienta adecuada para recorrer datos jerárquicos (empleados, categorías, comentarios anidados), generar series de datos (fechas, números) para rellenar huecos en informes, y calcular caminos o distancias en estructuras de grafo.

No las uses cuando un simple JOIN o GROUP BY resuelva el problema. La recursión añade complejidad y puede afectar al rendimiento con conjuntos de datos muy grandes.

Practica con CTE recursivo

Usa el editor para recorrer jerarquías con CTEs recursivos:

En el siguiente artículo exploraremos los operadores ANY y ALL, que permiten comparar un valor contra un conjunto de resultados de una subconsulta.