Aprende SQL · lección gratuita

Lección 28 · CTEs recursivos

Resumen

Un CTE recursivo es una CTE que se referencia a sí misma, permitiendo recorrer estructuras jerárquicas (organigramas, árboles, grafos) o generar secuencias sin saber de antemano su profundidad. Se declara con WITH RECURSIVE y combina dos partes unidas por UNION ALL: el ancla (caso base, filas iniciales) y el paso recursivo (filas nuevas calculadas a partir de las ya producidas), que se repite hasta que no genera más filas.

• WITH RECURSIVE: palabra clave obligatoria en SQLite para habilitar la autorreferencia.
• Ancla (caso base): el primer SELECT, sin referencia a la CTE; produce las filas de arranque (p. ej. el CEO con WHERE jefe_id = 0).
• UNION ALL: une el ancla con el paso recursivo; se usa UNION ALL (no UNION) para no perder filas y por rendimiento.
• Paso recursivo: el segundo SELECT hace JOIN con la propia CTE para descender (o subir) un nivel cada iteración.
• Condición de parada: la recursión termina cuando el paso recursivo no devuelve filas nuevas; un WHERE en el paso (p. ej. WHERE n < 100) evita bucles infinitos en series.
• Columna de nivel: se suele arrastrar nivel (0 en el ancla, o.nivel + 1 en el paso) para conocer la profundidad.

Sintaxis / Conceptos

La forma general es:

WITH RECURSIVE cte AS (
  SELECT ...            -- ANCLA: filas iniciales
  UNION ALL
  SELECT ...            -- PASO: usa cte en su FROM/JOIN
  FROM tabla JOIN cte ON ...
)
SELECT ... FROM cte;

SQLite ejecuta el ancla una vez, guarda esas filas en una cola, y repite el paso recursivo tomando como entrada las filas producidas en la iteración anterior, añadiendo las nuevas, hasta que una iteración no produce ninguna fila. En descenso (CEO → subordinados) el JOIN es empleados e JOIN cte o ON e.jefe_id = o.id. En ascenso (empleado → sus jefes) se invierte: empleados e JOIN cte c ON e.id = c.jefe_id.

Errores comunes: usar UNION en vez de UNION ALL sin necesidad (más lento); olvidar RECURSIVE; o escribir una serie sin WHERE de parada, lo que en SQLite se corta por seguridad pero produce datos inesperados. El ancla y el paso deben tener el mismo número y tipo de columnas.

SELECT cargo FROM empleados WHERE departamento_id = 2 UNION SELECT cargo FROM empleados WHERE departamento_id = 4 ORDER BY cargo

Ejemplos

-- ESTRELLA: organigrama completo desde el CEO (jefe_id = 0 es el ancla)
WITH RECURSIVE org AS (
  SELECT id, nombre, jefe_id, 0 AS nivel
  FROM empleados
  WHERE jefe_id = 0                    -- ancla: la CEO Elena Ríos
  UNION ALL
  SELECT e.id, e.nombre, e.jefe_id, o.nivel + 1
  FROM empleados e
  JOIN org o ON e.jefe_id = o.id       -- paso: baja un nivel
)
SELECT nivel, nombre
FROM org
ORDER BY nivel, nombre;

-- Cadena de mando HACIA ARRIBA: de un junior hasta la CEO
WITH RECURSIVE cadena AS (
  SELECT id, nombre, cargo, jefe_id
  FROM empleados
  WHERE id = 15                        -- ancla: Martina Soto (junior)
  UNION ALL
  SELECT e.id, e.nombre, e.cargo, e.jefe_id
  FROM empleados e
  JOIN cadena c ON e.id = c.jefe_id    -- paso: sube al jefe
)
SELECT nombre, cargo FROM cadena;

-- Generar una serie de números (sin tabla): 1..10
WITH RECURSIVE serie(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM serie WHERE n < 10  -- condición de parada
)
SELECT n FROM serie;

-- Contar TODOS los subordinados (directos e indirectos) de un director
WITH RECURSIVE sub AS (
  SELECT id FROM empleados WHERE id = 3   -- ancla: Lucía Mendoza
  UNION ALL
  SELECT e.id FROM empleados e JOIN sub s ON e.jefe_id = s.id
)
SELECT COUNT(*) - 1 AS subordinados FROM sub;

💡 Siempre arrastra una columna nivel o una condición WHERE n < límite: te da control de la profundidad y te protege de recursiones que no terminan. Recuerda: UNION ALL, no UNION, salvo que necesites deduplicar a propósito.

Cheatsheet

Cláusula / Función	Qué hace
WITH RECURSIVE c AS (...)	Habilita la CTE autorreferente
Ancla + UNION ALL + paso	Estructura obligatoria de la recursión
WHERE jefe_id = 0	Ancla del organigrama (la CEO no tiene jefe real)
JOIN cte ON e.jefe_id = o.id	Desciende un nivel (jefe → subordinados)
JOIN cte ON e.id = c.jefe_id	Asciende un nivel (empleado → jefes)
WHERE n < N	Condición de parada en series numéricas

---

← CTEs encadenadosOperaciones de conjuntos: UNION, INTERSECT, EXCEPT →

Ver todas las lecciones de Aprende SQL →