WITH RECURSIVE — Genera secuencias y recorre jerarquías con consultas recursivas

Datos usados en este artículo — la tabla employee

WITH RECURSIVE es una manera de escribir una CTE que se llama a sí misma para producir filas una a una.

Este movimiento de realimentar el resultado anterior como la siguiente entrada y repetir se llama recursión.

El material es la tabla employee (30 filas; la columna manager_id apunta al emp_id del manager, una estructura autorreferencial).

Antes de empezar los ejercicios, mira las definiciones de columnas y los datos de ejemplo de la tabla employee, y también el SELECT mínimo que usaremos como anchor de una CTE recursiva.

① Ejecuta SELECT 1; y confirma que devuelve solo el valor 1 en una única fila (este es el SELECT más simple que usaremos como anchor de una CTE recursiva).

② Ejecuta PRAGMA table_info(employee); para revisar las definiciones de columnas.

③ Ejecuta SELECT * FROM employee LIMIT 5; para previsualizar las primeras 5 filas. Fíjate también en cómo aparece NULL en la columna manager_id (empleados que no tienen manager).

Las 3 partes que componen una consulta recursiva — anchor / término recursivo / UNION ALL

Usando el código de abajo, repasaremos las 3 partes — ① el anchor (SELECT 1) / ② el término recursivo (SELECT n + 1 FROM seq WHERE n < 5) / ③ `UNION ALL` — una a una.

-- Código de referencia para esta sección. Cubriremos las 3 partes ① / ② / ③ en orden
WITH RECURSIVE seq(n) AS (
  SELECT 1                            -- ① anchor
  UNION ALL                           -- ③ UNION ALL (el pegamento entre anchor y término recursivo)
  SELECT n + 1 FROM seq WHERE n < 5   -- ② término recursivo
)
SELECT n FROM seq;

-- Resultado (5 filas): n=1, 2, 3, 4, 5

① Anchor — la parte SELECT 1

El anchor es el SELECT que produce la primera fila.

SELECT 1 en el código de referencia es el anchor — es solo un SELECT ordinario, escrito una vez, que no se referencia a sí mismo.

El valor (1) devuelto aquí se convierte en el primer contenido de seq(n), que luego se entrega al término recursivo como el valor inicial de n.

② Término recursivo — la parte SELECT n + 1 FROM seq WHERE n < 5

-- Código de referencia (repetido). Esta sección explica la línea ② del término recursivo
WITH RECURSIVE seq(n) AS (
  SELECT 1                            -- ① anchor
  UNION ALL                           -- ③ UNION ALL (el pegamento)
  SELECT n + 1 FROM seq WHERE n < 5   -- ★ ② término recursivo ← lo que estamos explicando ★
)
SELECT n FROM seq;

En WITH RECURSIVE seq(n), la parte seq(n) es la declaración del nombre de la CTE (seq) y del nombre de columna (n). El contenido empieza como el `1` del anchor, y a partir de ahí contiene el resultado del término recursivo.

SELECT n + 1 significa «el valor anterior + 1» cada vez.

El n inicial es 1, así que el siguiente n + 1 es 2, luego 3, y así sucesivamente — esto continúa hasta que WHERE deja de devolver filas.

③ UNION ALL — la parte `UNION ALL` del código

-- Código de referencia (repetido). Esta sección explica la línea ③ UNION ALL
WITH RECURSIVE seq(n) AS (
  SELECT 1                            -- anchor
  UNION ALL                           -- ★ lo que estamos explicando ★ parece una línea, pero…
  SELECT n + 1 FROM seq WHERE n < 5   -- término recursivo
)
SELECT n FROM seq;

-- …dentro del DBMS esto equivale a «apilar lo que produce el término recursivo, un UNION ALL por iteración»:

SELECT 1            -- anchor (se ejecuta solo una vez)
UNION ALL           -- ← 1.ª pila: apilando la salida de la pasada 1
SELECT 2
UNION ALL           -- ← 2.ª pila
SELECT 3
UNION ALL           -- ← 3.ª pila
SELECT 4
UNION ALL           -- ← 4.ª pila
SELECT 5;

-- El resultado es [1, 2, 3, 4, 5] de cualquier modo
-- En otras palabras, el único «UNION ALL» dentro de WITH RECURSIVE ≒ UNION ALL repetido por cada iteración

UNION ALL es un operador que apila verticalmente los resultados de dos o más SELECTs.

Usamos UNION ALL (que conserva todas las filas) en lugar de UNION (que elimina duplicados) porque queremos que cada fila producida durante la recursión llegue al resultado final.

Ejemplo 1 — Generar una secuencia (1 a 5)

Requisito: «Quiero generar una secuencia de 1 a 10 sobre la marcha como filas, sin una tabla de secuencias existente.» Incluso sin una tabla de secuencias maestra, puedes construirla con una CTE recursiva. (Si lo ejecutas correctamente, aparecerá la explicación.)

① Define una CTE para la secuencia con WITH RECURSIVE. El anchor debe ser un SELECT que devuelva el valor inicial 1.

② En el término recursivo, referencia la CTE misma y devuelve el valor anterior más 1. La condición de terminación debe ser «sigue mientras el valor actual sea menor que 10».

③ En la consulta principal, devuelve la secuencia generada en orden ascendente.

Ejemplo 2 — Proyección de crecimiento salarial (¿cuál es el salario dentro de N años con un 5% anual?)

El Ejemplo 1 era una secuencia numérica abstracta, pero aquí cubriremos una recursión práctica que parte de datos reales de la tabla `employee`.

Dado el salario actual de Sam (emp_id=19), proyectaremos cuánto sería dentro de 5 años si sube un 5% cada año.

Solo cambia el término recursivo a projected * 1.05 y obtienes una serie que crece de forma compuesta.

-- Proyección de crecimiento salarial: si el salario de Sam (emp_id=19) sube 5% al año, ¿cuánto es dentro de N años?
WITH RECURSIVE salary_growth(year, projected) AS (
  SELECT 0, salary                                     -- anchor: extrae el salario actual de employee
  FROM employee WHERE emp_id = 19
  UNION ALL
  SELECT year + 1, projected * 1.05                    -- término recursivo: subida del 5% por año
  FROM salary_growth
  WHERE year < 5
)
SELECT year, ROUND(projected) AS projected_salary
FROM salary_growth;

-- Resultado (6 filas): proyección de 5 años a partir del salario actual de Sam, 4100000
--  year | projected_salary
--  -----+-----------------
--    0  | 4100000   ← anchor (employee.salary tal cual)
--    1  | 4305000   ← término recursivo (4100000 × 1.05)
--    2  | 4520250   ← término recursivo (4305000 × 1.05)
--    3  | 4746263   ← término recursivo (4520250 × 1.05, ROUND aplicado)
--    4  | 4983576   ← término recursivo (4746262.5 × 1.05, ROUND aplicado)
--    5  | 5232754   ← término recursivo. Siguiente, year=5 hace WHERE year<5 falso → parar

Hay dos diferencias respecto al ejemplo de la secuencia.

① El anchor extrae un valor de la tabla employee (SELECT 0, salary FROM employee WHERE emp_id = 19) — puedes partir de datos reales.

② Es una CTE de 2 columnas (year y projected), y el término recursivo actualiza ambas columnas a la vez con year + 1 y projected * 1.05.

Una vez que puedes extraer un valor de una tabla real en el anchor, puedes escribir simulaciones que parten de ese valor (interés compuesto, proyecciones de población, cálculos de pasos hasta una meta, etc.).

Requisito: «Partiendo del salario actual de Bob (emp_id=2), quiero proyectar cuánto sería dentro de 5 años si sube un 10% al año.» (Si lo ejecutas correctamente, aparecerá la explicación.)

① Define el nombre de la CTE como growth y las columnas como year y projected en WITH RECURSIVE.

② El anchor debe extraer salary de employee para emp_id = 2, con year empezando en 0.

③ El término recursivo debe devolver year + 1 y projected * 1.10, con la condición de terminación year < 5.

④ En la consulta principal, devuelve year y ROUND(projected) AS projected_salary en orden ascendente.

Ejemplo 3 — Uso con JOIN (recorrer la cadena manager_id paso a paso)

A continuación cubriremos el otro uso canónico de las CTEs recursivas, recorrer una jerarquía autorreferencial.

employee.manager_id contiene «el emp_id de tu manager», así que siguiéndolo paso a paso puedes subir por el organigrama: empleado → manager → manager del manager, y así.

En el ejemplo de la secuencia usamos n+1 para producir el siguiente valor; aquí extraemos la siguiente fila (el manager) con un JOIN para avanzar la cadena.

-- Recorriendo la jerarquía: desde un empleado inicial (emp_id=19, Sam) subiendo por los managers
WITH RECURSIVE chain AS (
  SELECT emp_id, name, manager_id    -- ① anchor: la fila inicial
  FROM employee WHERE emp_id = 19
  UNION ALL                          -- ③ UNION ALL
  SELECT e.emp_id, e.name, e.manager_id
  FROM employee e
  JOIN chain c ON e.emp_id = c.manager_id    -- ② término recursivo: extrae la fila unida
)
SELECT emp_id, name, manager_id FROM chain;

-- Resultado (2 filas):
--  emp_id | name | manager_id
--  -------+------+-----------
--    19   | Sam  |    2        ← anchor (punto de partida)
--     2   | Bob  |  NULL       ← fila producida por el término recursivo; manager_id es NULL así que la siguiente pasada para

① El anchor (SELECT ... FROM employee WHERE emp_id = 19) extrae la fila inicial.

Aquí mete la fila de emp_id=19 (Sam) en chain como su primer contenido.

Observa que WITH RECURSIVE chain AS (...) no tiene una declaración de nombres de columna del tipo chain(...). Eso es porque el SELECT del anchor ya da a chain nombres de columna naturales (emp_id / name / manager_id) a partir de las columnas de employee. Cuando ya es legible, puedes omitirlo.

② El término recursivo (JOIN chain c ON e.emp_id = c.manager_id) hace join entre employee y el chain anterior y mete la fila unida en chain.

Una vez que el manager_id de la fila unida se vuelve NULL, la siguiente iteración no encuentra socio de join, el resultado es 0 filas y la recursión se detiene de forma natural.

(No hace falta una condición de terminación explícita como el WHERE n < 5 del ejemplo de la secuencia — el final de la jerarquía es la condición de terminación.)

Requisito: «Partiendo de emp_id = 19 (Sam), quiero recorrer la cadena manager_id hacia arriba — el manager de Sam, luego el manager de ese, y así — para listar la cadena de rangos.»

① Define una CTE para la jerarquía con WITH RECURSIVE. El anchor debe extraer una fila (emp_id, name, manager_id) de employee para emp_id = 19.

② En el término recursivo, haz join entre employee y la CTE por «employee.emp_id = el manager_id de la CTE» y extrae la fila del manager (emp_id, name, manager_id) paso a paso.

③ En la consulta principal, devuelve emp_id, name, manager_id.

La Práctica 3 recorría de abajo arriba a través de los managers.

Ahora iremos en la otra dirección: «Partiendo de emp_id = 1 (Alice), quiero recorrer la cadena manager_id bajando por los subordinados de Alice, luego sus subordinados, y así — para listar a toda la gente del equipo de Alice.»

① Define una CTE para los subordinados con WITH RECURSIVE. El anchor debe extraer una fila (emp_id, name, manager_id) de employee para emp_id = 1.

② En el término recursivo, haz join entre employee y la CTE por «employee.manager_id = el emp_id de la CTE» y extrae los subordinados de la fila actual (emp_id, name, manager_id) paso a paso.

③ En la consulta principal, devuelve emp_id, name, manager_id en orden ascendente de emp_id.

Ejemplo 4 — Recursión que produce varias filas a la vez (todo el árbol organizativo)

El Ejemplo 3 recorría una fila a la vez (Sam → Bob → parar).

Aquí cubriremos el caso en el que el anchor o el término recursivo producen varias filas a la vez.

El escenario: «Parte de los 5 executives (empleados sin manager) y extrae todo el árbol organizativo de 30 personas de una sola vez.» Verás la consulta recursiva recorriendo varias ramas en paralelo de forma en anchura.

-- Parte de todos los executives (empleados sin manager) y extrae recursivamente a todos en la organización
WITH RECURSIVE org_tree AS (
  -- ① anchor: 5 empleados con manager_id = NULL (devuelve varias filas a la vez)
  SELECT emp_id, name, manager_id
  FROM employee
  WHERE manager_id IS NULL
  UNION ALL
  -- ② término recursivo: extrae los subordinados directos de las filas anteriores (también varias filas)
  SELECT e.emp_id, e.name, e.manager_id
  FROM employee e
  JOIN org_tree t ON e.manager_id = t.emp_id
)
SELECT emp_id, name, manager_id FROM org_tree
ORDER BY emp_id;

-- Resultado (30 filas = toda la empresa):
--   Anchor (5 filas): emp_id=1 Alice, 2 Bob, 3 Carol, 9 Ivan, 28 Brian
--   Término recursivo pasada 1 (25 filas): los 9 subordinados de Alice + los 8 de Bob + los 8 de Carol
--   Término recursivo pasada 2 (0 filas): los subordinados no tienen subordinados propios → parar
--   Total: 5 + 25 = 30 filas

Hay dos diferencias respecto al Ejemplo 3.

① El anchor devuelve varias filas (5) a la vez — cada fila que cumple WHERE manager_id IS NULL se usa para inicializar la CTE.

② El término recursivo recorre varias ramas en paralelo — con 5 personas en org_tree, una pasada del término recursivo extrae los subordinados de cada persona al mismo tiempo dentro de la misma iteración (9 de Alice + 8 de Bob + 8 de Carol = 25 filas).

En otras palabras, las consultas recursivas pueden hacer más que «bajar una fila a la vez» — también pueden expandir varias ramas de forma paralela y en anchura.

Requisito: «Partiendo de Bob (emp_id=2) y Carol (emp_id=3) al mismo tiempo, quiero extraer sus subordinados en una sola consulta.» (Si lo ejecutas correctamente, aparecerá la explicación.)

① El nombre de la CTE debe ser team en WITH RECURSIVE; omite la declaración de nombres de columna (usa los nombres de columna naturales de employee).

② El anchor debe extraer dos filas de employee donde emp_id sea 2 o 3.

③ El término recursivo debe hacer join entre employee y la CTE por «employee.manager_id = el emp_id de la CTE» y extraer los subordinados.

④ En la consulta principal, devuelve emp_id, name, manager_id en orden ascendente de emp_id.

Consejos — ¿Por qué usar WITH RECURSIVE?

Tres situaciones donde WITH RECURSIVE brilla

Las consultas recursivas son para situaciones en las que el SQL plano o bien no puede hacerlo, o lo hace muy doloroso. Los principales casos de uso son tres:

① Recorrer una jerarquía — organigramas, carpetas, listas de materiales — estructuras autorreferenciales donde no sabes lo profundas que son. En lugar de apilar varios JOINs a mano, puedes escribirlo en una forma que para de forma natural al final de los datos.

② Generar muchas filas — secuencias, fechas consecutivas, calendarios — en lugar de escribir UNION ALL cien veces a mano, una sola línea con WHERE lo hace.

③ Mover un bucle del lado de la aplicación a SQL — escribir un bucle en la app para recorrer un ID padre choca con el problema N+1 de consultas, pero una consulta recursiva lo resuelve en una sola consulta.

Si un JOIN o GROUP BY plano sirven, no necesitas recursión. Pero cuando estás en una de las tres situaciones anteriores, es la primera herramienta a la que acudir.