WITH RECURSIVE en SQL: organigramas y jerarquías

Un organigrama, un árbol de categorías o los comentarios con respuestas se guardan igual: cada fila apunta a su madre (jefe_id). Para recorrer todos los niveles sin saber cuántos hay, SQL tiene las consultas recursivas, WITH RECURSIVE.

El organigrama completo

Una CTE recursiva tiene dos partes unidas por UNION ALL: el ancla, que da las primeras filas (quien no tiene jefe), y el paso recursivo, que se une con lo que ya hay en el resultado para sacar el nivel siguiente. Se repite hasta que una vuelta no añade nada:

WITH RECURSIVE organigrama AS (
  -- Punto de partida: quien no tiene jefe
  SELECT id, nombre, puesto, jefe_id, 0 AS nivel, nombre AS ruta
  FROM empleados
  WHERE jefe_id IS NULL
  UNION ALL
  -- Paso recursivo: los empleados cuyo jefe ya está en el resultado
  SELECT e.id, e.nombre, e.puesto, e.jefe_id, o.nivel + 1, o.ruta || ' > ' || e.nombre
  FROM empleados e
  JOIN organigrama o ON e.jefe_id = o.id
)
SELECT nivel, nombre, puesto, ruta
FROM organigrama
ORDER BY ruta;

Ábrelo en el editor y cámbialo →

resultado · SQLitenivel | nombre        | puesto          | ruta
------+---------------+-----------------+------------------------------------------
    0 | Elena Vargas  | Directora       | Elena Vargas
    1 | Marta Ruiz    | Jefa de almacén | Elena Vargas > Marta Ruiz
    2 | Nadia Fuentes | Mozo de almacén | Elena Vargas > Marta Ruiz > Nadia Fuentes
    2 | Óscar Lara    | Mozo de almacén | Elena Vargas > Marta Ruiz > Óscar Lara
    1 | Raúl Peña     | Jefe de ventas  | Elena Vargas > Raúl Peña
    2 | Carla Medina  | Vendedora       | Elena Vargas > Raúl Peña > Carla Medina
    2 | Hugo Salas    | Vendedor        | Elena Vargas > Raúl Peña > Hugo Salas
    2 | Iván Soto     | Vendedor        | Elena Vargas > Raúl Peña > Iván Soto

El ancla da la directora; cada vuelta añade a quienes dependen de alguien que ya está en el resultado. Esquema: decodigo.com.

Ordenar por la ruta deja a cada persona bajo su jefe, como en un índice.

La misma consulta en PostgreSQL y MySQL

En PostgreSQL falla tal cual:

resultado · PostgreSQL, sin CASTERROR:  recursive query "organigrama" column 6 has type character varying(60) in non-recursive term but type character varying overall
LINE 3:   SELECT id, nombre, puesto, jefe_id, 0 AS nivel, nombre AS ...
                                                          ^
HINT:  Cast the output of the non-recursive term to the correct type.

La columna ruta nace como varchar(60) en el ancla y crece en el paso recursivo. Basta con convertirla a TEXT en el ancla: CAST(nombre AS TEXT) AS ruta; con eso, PostgreSQL devuelve las mismas ocho filas.

En MySQL hay dos problemas. || no concatena textos (es un «o» lógico), y la columna toma el tamaño del ancla, así que una ruta de más de 60 caracteres da error. Aquí, añadiendo el puesto a cada nombre:

-- En MySQL, || no concatena: se usa CONCAT. Y la columna ruta toma el tipo de la primera parte,
-- nombre VARCHAR(60): en cuanto una ruta pasa de 60 caracteres, error.
WITH RECURSIVE organigrama AS (
  SELECT id, nombre, jefe_id, 0 AS nivel, nombre AS ruta
  FROM empleados
  WHERE jefe_id IS NULL
  UNION ALL
  SELECT e.id, e.nombre, e.jefe_id, o.nivel + 1, CONCAT(o.ruta, ' > ', e.nombre, ' (', e.puesto, ')')
  FROM empleados e
  JOIN organigrama o ON e.jefe_id = o.id
)
SELECT nivel, ruta FROM organigrama ORDER BY ruta;

-- Con CAST, la columna nace con sitio de sobra
WITH RECURSIVE organigrama AS (
  SELECT id, nombre, jefe_id, 0 AS nivel, CAST(nombre AS CHAR(500)) AS ruta
  FROM empleados
  WHERE jefe_id IS NULL
  UNION ALL
  SELECT e.id, e.nombre, e.jefe_id, o.nivel + 1, CONCAT(o.ruta, ' > ', e.nombre, ' (', e.puesto, ')')
  FROM empleados e
  JOIN organigrama o ON e.jefe_id = o.id
)
SELECT nivel, ruta FROM organigrama ORDER BY ruta;

-- Y || entre textos, en MySQL, es un O lógico
SELECT 'Elena' || ' > ' || 'Raúl' AS concatenado;

resultado · MySQLERROR 1406 (22001) at line 3: Data too long for column 'ruta' at row 1
+-------+---------------------------------------------------------------------------------+
| nivel | ruta                                                                            |
+-------+---------------------------------------------------------------------------------+
|     0 | Elena Vargas                                                                    |
|     1 | Elena Vargas > Marta Ruiz (Jefa de almacén)                                     |
|     2 | Elena Vargas > Marta Ruiz (Jefa de almacén) > Nadia Fuentes (Mozo de almacén)   |
|     2 | Elena Vargas > Marta Ruiz (Jefa de almacén) > Óscar Lara (Mozo de almacén)      |
|     1 | Elena Vargas > Raúl Peña (Jefe de ventas)                                       |
|     2 | Elena Vargas > Raúl Peña (Jefe de ventas) > Carla Medina (Vendedora)            |
|     2 | Elena Vargas > Raúl Peña (Jefe de ventas) > Hugo Salas (Vendedor)               |
|     2 | Elena Vargas > Raúl Peña (Jefe de ventas) > Iván Soto (Vendedor)                |
+-------+---------------------------------------------------------------------------------+
+-------------+
| concatenado |
+-------------+
|           0 |
+-------------+

Todo el equipo de un jefe

Cambiando el ancla por «los que dependen de Raúl», se obtiene su equipo completo, a cualquier profundidad. Igual en los tres motores:

-- Todo el equipo que depende de Raúl Peña (id 2), directa o indirectamente
WITH RECURSIVE equipo AS (
  SELECT id, nombre, puesto FROM empleados WHERE jefe_id = 2
  UNION ALL
  SELECT e.id, e.nombre, e.puesto
  FROM empleados e
  JOIN equipo q ON e.jefe_id = q.id
)
SELECT id, nombre, puesto FROM equipo ORDER BY id;

Ábrelo en el editor y cámbialo →

resultado · SQLiteid | nombre       | puesto
---+--------------+----------
 4 | Iván Soto    | Vendedor
 5 | Carla Medina | Vendedora
 6 | Hugo Salas   | Vendedor

Hacia arriba: la cadena de mando

Para subir, se invierte la unión: en cada vuelta se busca al jefe de la última persona encontrada:

-- Hacia arriba: la cadena de jefes de Óscar Lara (id 8)
WITH RECURSIVE jefes AS (
  SELECT id, nombre, jefe_id, 0 AS saltos FROM empleados WHERE id = 8
  UNION ALL
  SELECT e.id, e.nombre, e.jefe_id, j.saltos + 1
  FROM empleados e
  JOIN jefes j ON e.id = j.jefe_id
)
SELECT saltos, nombre FROM jefes ORDER BY saltos;

Ábrelo en el editor y cámbialo →

resultado · SQLitesaltos | nombre
-------+-------------
     0 | Óscar Lara
     1 | Marta Ruiz
     2 | Elena Vargas

El coste de cada rama

Una pregunta típica: cuánto suman los salarios de cada jefe y todo lo que cuelga de él. La CTE genera una pareja (jefe, empleado) por cada persona que está por debajo, empezando por cada uno consigo mismo:

-- Coste en salarios de cada rama: cada jefe con todos los que cuelgan de él (incluido él)
WITH RECURSIVE debajo AS (
  SELECT id AS jefe, id AS empleado FROM empleados
  UNION ALL
  SELECT d.jefe, e.id
  FROM empleados e
  JOIN debajo d ON e.jefe_id = d.empleado
)
SELECT j.nombre AS jefe, COUNT(*) AS personas, SUM(e.salario) AS salarios
FROM debajo d
JOIN empleados j ON j.id = d.jefe
JOIN empleados e ON e.id = d.empleado
GROUP BY j.id, j.nombre
HAVING COUNT(*) > 1
ORDER BY salarios DESC;

Ábrelo en el editor y cámbialo →

resultado · SQLitejefe         | personas | salarios
-------------+----------+---------
Elena Vargas |        8 |    25200
Raúl Peña    |        4 |    11350
Marta Ruiz   |        3 |     7650

Cuando los datos tienen un ciclo

Si por un error alguien acaba siendo jefe de su propio jefe, la recursión no termina nunca. La protección más sencilla, en cualquier motor, es un límite de profundidad:

-- Un error en los datos crea un ciclo: la directora pasa a depender de un vendedor
UPDATE empleados SET jefe_id = 4 WHERE id = 1;

-- Con un límite de profundidad, la consulta termina igual
WITH RECURSIVE jefes AS (
  SELECT id, nombre, jefe_id, 0 AS saltos FROM empleados WHERE id = 4
  UNION ALL
  SELECT e.id, e.nombre, e.jefe_id, j.saltos + 1
  FROM empleados e
  JOIN jefes j ON e.id = j.jefe_id
  WHERE j.saltos < 6
)
SELECT saltos, nombre FROM jefes;

Ábrelo en el editor y cámbialo →

resultado · SQLitesaltos | nombre
-------+-------------
     0 | Iván Soto
     1 | Raúl Peña
     2 | Elena Vargas
     3 | Iván Soto
     4 | Raúl Peña
     5 | Elena Vargas
     6 | Iván Soto

MySQL corta solo a las 1000 vueltas, con un error:

resultado · MySQLERROR 3636 (HY000) at line 4: Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

Y PostgreSQL (desde la 14) detecta el ciclo con la cláusula CYCLE, que marca la fila que lo cierra y deja de seguir:

UPDATE empleados SET jefe_id = 4 WHERE id = 1;

-- PostgreSQL 14+ detecta el ciclo por sí mismo con CYCLE
WITH RECURSIVE jefes AS (
  SELECT id, nombre, jefe_id FROM empleados WHERE id = 4
  UNION ALL
  SELECT e.id, e.nombre, e.jefe_id
  FROM empleados e
  JOIN jefes j ON e.id = j.jefe_id
) CYCLE id SET hay_ciclo USING camino
SELECT nombre, hay_ciclo FROM jefes;

resultado · PostgreSQL    nombre    | hay_ciclo 
--------------+-----------
 Iván Soto    | f
 Raúl Peña    | f
 Elena Vargas | f
 Iván Soto    | t

Todas las consultas de esta página se ejecutaron antes de publicarla sobre la base de ejemplo «tienda» del curso, en contenedores limpios: SQLite 3.46.1 (el de Python 3.13), PostgreSQL 17 y MySQL 8.4. Los resultados y los mensajes de error están copiados de esa ejecución. Documentación oficial: consultas recursivas en SQLite.