CTE recursiva en SQL: consultar jerarquías

Lección 6.5 · Tiempo de lectura: ~9 min · Tareas de práctica ↓

Una CTE recursiva en SQL es una expresión de tabla común que hace referencia a sí misma, así que una sola consulta puede recorrer una jerarquía de cualquier profundidad: un organigrama, un árbol de categorías, un hilo de comentarios. En esta lección aprenderás la sintaxis WITH RECURSIVE, cómo trabajan juntos el miembro ancla y el miembro recursivo, cómo encontrar todos los subordinados o todos los jefes de un empleado, cómo construir una ruta por el árbol y cómo detener una recursión descontrolada.

En la lección anterior, una CTE era un paso con nombre de la consulta que se ejecutaba una vez. Los datos jerárquicos necesitan algo más. Si cada fila de empleado guarda el id de su jefe, un JOIN encuentra a los subordinados directos, un segundo JOIN el nivel siguiente, y así sucesivamente, pero no sabes de antemano cuántos niveles hay. Una CTE recursiva repite la unión por ti hasta que no queda nada por encontrar.

Los ejemplos usan la sintaxis de MySQL 8.0 y también funcionan en MariaDB 10.2+. Las bases de datos de ejemplo de SQLtest son de solo lectura, así que ejecuta los ejemplos en el playground en una base sin datos de ejemplo: cada uno crea su propia tabla.

Cómo funciona una CTE recursiva

Una CTE recursiva tiene dos partes unidas por UNION ALL:

WITH RECURSIVE cte_name AS (
    -- Miembro ancla: las filas iniciales
    SELECT ...
    FROM table_name
    WHERE start_condition

    UNION ALL

    -- Miembro recursivo: el nivel siguiente, encontrado a partir del anterior
    SELECT ...
    FROM table_name
    INNER JOIN cte_name ON join_condition
)
SELECT * FROM cte_name;

• WITH RECURSIVE le indica a la base de datos que la CTE hace referencia a sí misma.
• El miembro ancla se ejecuta una vez y devuelve las filas iniciales, por ejemplo el director general.
• El miembro recursivo hace referencia a la CTE por su nombre. En cada ejecución ve solo las filas producidas por la iteración anterior.
• La recursión se detiene cuando una iteración no devuelve filas. Las filas de todas las iteraciones forman el resultado.

El ejemplo más simple: una serie de números

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

Resultado: 5 filas con los números 1, 2, 3, 4, 5.

El ancla devuelve 1. La primera iteración lee 1 y devuelve 2, la siguiente devuelve 3, y así sucesivamente. Cuando la iteración anterior produjo 5, la condición n < 5 es falsa, el miembro recursivo no devuelve nada y la consulta termina. La condición WHERE es la condición de parada: sin ella, la recursión nunca terminaría.

Datos de ejemplo: un organigrama

Una jerarquía en una tabla suele guardarse como una lista de adyacencia: cada fila referencia a su padre en la misma tabla. Aquí manager_id apunta a otro empleado, y el director general no tiene jefe:

CREATE TABLE employee (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    manager_id INT,
    title VARCHAR(100)
);

INSERT INTO employee VALUES
    (1, 'Alice Johnson', NULL, 'CEO'),
    (2, 'Bob Smith', 1, 'VP of Engineering'),
    (3, 'Carol White', 1, 'VP of Sales'),
    (4, 'David Brown', 2, 'Engineering Manager'),
    (5, 'Eve Davis', 2, 'Engineering Manager'),
    (6, 'Frank Miller', 3, 'Sales Manager'),
    (7, 'Grace Wilson', 4, 'Senior Developer'),
    (8, 'Henry Moore', 4, 'Developer'),
    (9, 'Ivy Taylor', 5, 'Developer'),
    (10, 'Jack Anderson', 6, 'Sales Representative');

Encontrar todos los subordinados

¿Quién depende de Bob Smith, directa o indirectamente? El ancla selecciona a Bob; el miembro recursivo encuentra a los empleados cuyo jefe está en el nivel anterior:

WITH RECURSIVE subordinates AS (
    SELECT employee_id, employee_name, title, 0 AS level
    FROM employee
    WHERE employee_name = 'Bob Smith'

    UNION ALL

    SELECT e.employee_id, e.employee_name, e.title, s.level + 1
    FROM employee AS e
    INNER JOIN subordinates AS s ON e.manager_id = s.employee_id
)
SELECT employee_id, employee_name, title, level
FROM subordinates
ORDER BY level, employee_name;

Resultado: 6 filas: Bob Smith en el nivel 0, David Brown y Eve Davis en el nivel 1, Grace Wilson, Henry Moore e Ivy Taylor en el nivel 2.

La columna level cuenta las iteraciones: empieza en 0 en el ancla y crece en uno con cada paso hacia abajo. Para excluir al propio Bob, agrega WHERE level > 0 a la consulta final.

Construir el árbol completo con una ruta

Para mostrar todo el organigrama, empieza por el empleado sin jefe y reúne la ruta desde la cima hasta cada persona:

WITH RECURSIVE org_chart AS (
    SELECT
        employee_id,
        employee_name,
        title,
        0 AS level,
        CAST(employee_name AS CHAR(500)) AS path
    FROM employee
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.employee_id,
        e.employee_name,
        e.title,
        oc.level + 1,
        CONCAT(oc.path, ' > ', e.employee_name)
    FROM employee AS e
    INNER JOIN org_chart AS oc ON e.manager_id = oc.employee_id
)
SELECT
    CONCAT(REPEAT('- ', level), employee_name) AS hierarchy,
    title,
    path
FROM org_chart
ORDER BY path;

Resultado: los 10 empleados en el orden del árbol, cada uno con la cadena de jefes por encima.

Dos detalles hacen que funcione:

• Ordenar por la ruta coloca a cada empleado justo después de su jefe, así que el resultado se lee como un árbol.
• CAST(... AS CHAR(500)) en el ancla. Los tipos de las columnas de una CTE recursiva se toman del miembro ancla. Sin la conversión, path tendría el tipo de employee_name, VARCHAR(100), y en cuanto una ruta superara los 100 caracteres, MySQL se detendría con el error "Data too long for column 'path'". Haz suficientemente anchas, ya en el ancla, las columnas de texto que crecen en la recursión.

Subir por el árbol: todos los jefes de un empleado

La misma técnica funciona en la otra dirección. Para encontrar la cadena de jefes por encima de Ivy Taylor, empieza por ella y une cada fila con su padre: la condición de unión se invierte.

WITH RECURSIVE managers AS (
    SELECT employee_id, employee_name, manager_id, 0 AS steps_up
    FROM employee
    WHERE employee_name = 'Ivy Taylor'

    UNION ALL

    SELECT e.employee_id, e.employee_name, e.manager_id, m.steps_up + 1
    FROM employee AS e
    INNER JOIN managers AS m ON e.employee_id = m.manager_id
)
SELECT employee_name, steps_up
FROM managers
ORDER BY steps_up;

Resultado: 4 filas: Ivy Taylor (0), Eve Davis (1), Bob Smith (2), Alice Johnson (3).

La recursión se detiene en Alice Johnson: su manager_id es NULL, así que la unión no encuentra nada.

Detener una recursión descontrolada

Una CTE recursiva termina cuando una iteración no devuelve filas. Dos situaciones rompen esa regla: que falte la condición de parada, como en una serie de números sin WHERE, y los ciclos en los datos, por ejemplo cuando por error A es jefe de B y B es jefe de A. Entonces las mismas filas se encuentran una y otra vez.

Limitar la profundidad

Agrega una condición sobre el nivel en el miembro recursivo:

WITH RECURSIVE subordinates AS (
    SELECT employee_id, employee_name, 0 AS level
    FROM employee
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.employee_name, s.level + 1
    FROM employee AS e
    INNER JOIN subordinates AS s ON e.manager_id = s.employee_id
    WHERE s.level < 2
)
SELECT employee_name, level
FROM subordinates;

Resultado: 6 filas: el director general, dos vicepresidentes y tres gerentes; los niveles por debajo de 2 no se expanden.

MySQL también tiene un límite de seguridad: la variable cte_max_recursion_depth (1000 por defecto). Una consulta que necesita más iteraciones falla con "Recursive query aborted after 1001 iterations". Para una recursión realmente profunda, súbelo con SET SESSION cte_max_recursion_depth = 10000;. MariaDB se comporta distinto: al llegar a max_recursive_iterations se detiene sin error y devuelve las filas encontradas hasta ese momento, así que un resultado incompleto puede pasar inadvertido.

Saltar las filas ya visitadas

Un límite de profundidad detiene un ciclo, pero igual devuelve filas repetidas. Para impedir el ciclo en sí, guarda la lista de ids visitados y no entres en una fila que ya está en la ruta:

WITH RECURSIVE org_chart AS (
    SELECT employee_id, employee_name, CAST(employee_id AS CHAR(500)) AS visited
    FROM employee
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.employee_name, CONCAT(oc.visited, ',', e.employee_id)
    FROM employee AS e
    INNER JOIN org_chart AS oc ON e.manager_id = oc.employee_id
    WHERE FIND_IN_SET(e.employee_id, oc.visited) = 0
)
SELECT employee_name, visited
FROM org_chart;

Resultado: 10 filas; por ejemplo, Ivy Taylor tiene visited = 1,2,5,9.

FIND_IN_SET() devuelve la posición de un valor en una lista separada por comas, o 0 si no está. PostgreSQL 14+ tiene para esto una cláusula CYCLE integrada.

Reglas y diferencias entre bases de datos

• El miembro recursivo debe hacer referencia a la CTE exactamente una vez, en FROM o JOIN, no en una subconsulta.
• En MySQL, el miembro recursivo no puede usar funciones de agregación ni de ventana, GROUP BY, ORDER BY ni DISTINCT. Agrega en la consulta final.
• UNION ALL conserva todas las filas y es la opción habitual. UNION elimina filas duplicadas y, como efecto secundario, detiene un ciclo cuando las filas repetidas son idénticas.

Para un mejor rendimiento, indexa la columna usada en la unión recursiva, como manager_id.

Conclusiones clave:

• Una CTE recursiva hace referencia a sí misma y recorre una jerarquía de cualquier profundidad con WITH RECURSIVE.
• El miembro ancla devuelve las filas iniciales; el miembro recursivo encuentra el nivel siguiente a partir del anterior; la recursión se detiene cuando una iteración no devuelve filas.
• La unión e.manager_id = cte.employee_id baja por el árbol; e.employee_id = cte.manager_id sube.
• Un contador level y una columna path muestran la posición de cada fila; convierte en el ancla las columnas de texto que crecen.
• Protege las consultas con un límite de profundidad o una lista de ids visitados; MySQL se detiene después de cte_max_recursion_depth iteraciones.

Preguntas frecuentes

¿Qué es una CTE recursiva en SQL?

Es una expresión de tabla común que hace referencia a sí misma. Consta de un miembro ancla que devuelve las filas iniciales y un miembro recursivo que se ejecuta repetidamente, cada vez sobre las filas de la iteración anterior, hasta que no aparecen filas nuevas. Se usa para jerarquías y grafos: organigramas, árboles de categorías, rutas.

¿Cómo detengo una recursión infinita en una CTE recursiva?

Agrega una condición de parada al miembro recursivo, como WHERE level < 10, y protégete de los ciclos en los datos llevando la cuenta de los ids visitados. MySQL además interrumpe una consulta después de cte_max_recursion_depth iteraciones (1000 por defecto) y SQL Server después de MAXRECURSION (100 por defecto); MariaDB se detiene en silencio después de max_recursive_iterations.

¿MySQL admite CTE recursivas?

Sí, desde MySQL 8.0 y MariaDB 10.2, con la sintaxis WITH RECURSIVE. Las versiones antiguas de MySQL no tienen CTE; allí las jerarquías se consultaban con procedimientos almacenados o con un número fijo de self-joins.

¿Por qué mi CTE recursiva falla con "Data too long for column"?

Las columnas de una CTE recursiva toman sus tipos del miembro ancla. Si el miembro recursivo construye una cadena más larga, como una ruta, ya no cabe. Convierte la columna en el ancla a un tipo más ancho, por ejemplo CAST(name AS CHAR(500)).