CTE recursiva con WITH RECURSIVE

El modelo mental: raíz → siguiente nivel → repetir

Una CTE ordinaria nombra un resultado intermedio. Una CTE recursiva añade una relación consigo misma para avanzar por niveles. En un organigrama, por ejemplo, puedes empezar por la persona sin responsable, buscar después a quienes dependen de ella y repetir el proceso con cada nuevo nivel.

filas iniciales
      ↓
filas del siguiente nivel
      ↓
filas del siguiente nivel
      ↓
... hasta que no aparezcan filas nuevas

La palabra RECURSIVE hace visible esa intención. PostgreSQL y SQLite documentan este patrón dentro de la cláusula WITH.

Estructura de WITH RECURSIVE

WITH RECURSIVE nombre_cte AS (
  -- Caso inicial
  SELECT ...

  UNION ALL

  -- Paso recursivo
  SELECT ...
  FROM tabla
  JOIN nombre_cte ON ...
)
SELECT ...
FROM nombre_cte;

Lee las dos ramas por separado. La primera define desde dónde empieza el recorrido. La segunda expresa cómo encontrar filas relacionadas con las que ya están en la CTE. UNION ALL reúne ambos conjuntos sin eliminar duplicados automáticamente.

Ejemplo: recorrer el organigrama de empleados

La base educativa ya contiene empleados.responsable_id. Carmen no tiene responsable; Alex, Carlos y Gonzalo dependen de Carmen; y a partir de ellos aparecen niveles posteriores.

WITH RECURSIVE organigrama AS (
  SELECT id,
         nombre,
         responsable_id,
         0 AS nivel,
         nombre AS ruta
  FROM empleados
  WHERE responsable_id IS NULL

  UNION ALL

  SELECT e.id,
         e.nombre,
         e.responsable_id,
         o.nivel + 1,
         o.ruta || ' > ' || e.nombre
  FROM empleados AS e
  JOIN organigrama AS o
    ON e.responsable_id = o.id
)
SELECT nombre, nivel, ruta
FROM organigrama
ORDER BY ruta;

El caso inicial produce solo a Carmen. La rama recursiva usa cada fila obtenida para encontrar empleados cuyo responsable_id coincide con ese id. nivel + 1 registra la profundidad y ruta permite ver por qué camino se llegó a cada persona.

UNION ALL no significa “recursión infinita”

UNION ALL es habitual porque no obliga a deduplicar cada iteración. Pero la terminación no depende de cambiarlo mecánicamente por UNION: depende de que el paso recursivo tenga una relación que finalmente deje de producir filas y de que los datos no creen recorridos cíclicos no controlados.

En una jerarquía bien formada, una clave como responsable_id avanza hacia descendientes y llega a hojas que ya no tienen personas a su cargo. En grafos generales, la prevención de ciclos exige una estrategia explícita y puede variar según el motor y el problema.

Errores frecuentes

Práctica guiada

WITH RECURSIVE equipo AS (
  SELECT id, nombre, 0 AS nivel
  FROM empleados
  WHERE id = 4

  UNION ALL

  SELECT e.id,
         e.nombre,
         equipo.nivel + 1
  FROM empleados AS e
  JOIN equipo
    ON e.responsable_id = equipo.id
)
SELECT nombre, nivel
FROM equipo
ORDER BY nivel, nombre;

Compatibilidad y límites

PostgreSQL y SQLite documentan CTE recursivas mediante WITH RECURSIVE. Las reglas de búsqueda, detección de ciclos, límites de recursión y optimización no son idénticas entre motores, así que una consulta de producción debe comprobarse en el motor concreto.

El motor educativo integrado de AprenderSQL.es enseña y ejecuta CTE ordinarias, pero no simula CTE recursivas. Los resultados de esta página se comprobaron con SQLite real sobre los mismos datos educativos, no con el laboratorio del curso.

Conceptos relacionados

Fuentes técnicas consultadas

• PostgreSQL: WITH y consultas recursivas
• SQLite: Common Table Expressions recursivas