0Pricing
SQL Academy · Lección

Cómo funcionan las CTE recursivas

Caso base más paso recursivo

Cómo funcionan las CTE recursivas es una lección gratuita de SQL Academy en CoddyKit. Esta es la lección 1 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de SQL Academy, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de SQL Academy incluye 4 lecciones en total.

¿Qué es una CTE recursiva?

Una CTE recursiva es una expresión de tabla común que se referencia a sí misma. Permite escribir consultas que repiten un paso hasta que se cumple una condición, de forma similar a un bucle, pero expresada como SQL puro.

Las CTE recursivas se definen con la palabra clave WITH RECURSIVE y son ideales para recorrer datos jerárquicos o con estructura de grafo, como organigramas, árboles de carpetas y estructuras de listas de materiales.

La estructura de dos partes

Toda CTE recursiva tiene exactamente dos partes separadas por UNION ALL:

1. Caso base: un SELECT no recursivo que devuelve las filas iniciales.

2. Paso recursivo: un SELECT que vuelve a unir la CTE consigo misma y produce el siguiente nivel de filas.

El motor sigue ejecutando el paso recursivo y acumulando resultados hasta que no se producen nuevas filas.

WITH RECURSIVE cte_name AS (
  -- Base case
  SELECT ...
  UNION ALL
  -- Recursive step (references cte_name)
  SELECT ... FROM source JOIN cte_name ON ...
)
SELECT * FROM cte_name;

Contar del 1 al 5

La CTE recursiva más sencilla cuenta números. El caso base establece el valor 1. El paso recursivo suma 1 en cada iteración. La cláusula WHERE dentro del paso recursivo actúa como condición de terminación; sin ella, la consulta se ejecutaría para siempre.

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

Ejecución paso a paso

Así procesa el motor la CTE del contador en cada iteración:

Iteración 0 (caso base): devuelve {1}.

Iteración 1: aplica el paso recursivo a {1} y devuelve {2}.

Iteración 2: aplica el paso recursivo a {2} y devuelve {3}.

Iteraciones 3 y 4: devuelve {4} y después {5}.

Iteración 5: WHERE n < 5 es falso para n=5, por lo que devuelve cero filas. La consulta termina.

Todas las filas acumuladas —1, 2, 3, 4 y 5— forman el resultado final.

Configuración de una tabla jerárquica

Las CTE recursivas resultan especialmente útiles con tablas que se referencian a sí mismas. Creemos una tabla employees en la que cada empleado tenga un manager_id opcional que apunte de nuevo a la misma tabla.

CREATE TABLE employees (
  id       INTEGER PRIMARY KEY,
  name     VARCHAR(50),
  manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
  (1, 'Alice',   NULL),
  (2, 'Bob',     1),
  (3, 'Carol',   1),
  (4, 'Dave',    2),
  (5, 'Eve',     2),
  (6, 'Frank',   3);

Recorrido de la jerarquía

Ahora podemos recorrer toda la cadena de subordinación empezando por la directora ejecutiva (Alice, id=1). El caso base selecciona a Alice; el paso recursivo busca todos los empleados cuyo manager_id coincida con un id que ya esté en la CTE.

El resultado incluye a todos los empleados accesibles desde Alice, sin importar la profundidad del árbol.

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT depth, name FROM org_tree ORDER BY depth, name;

Seguimiento de la ruta

Una mejora habitual consiste en crear una cadena de ruta que muestre toda la cadena desde la raíz hasta cada nodo. Concatenamos los nombres separados por ' -> ' a medida que profundizamos en la recursión.

Esto facilita mostrar una navegación con estilo de ruta de navegación o depurar jerarquías profundas.

WITH RECURSIVE org_tree AS (
  SELECT id, name, name AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, ot.path || ' -> ' || e.name
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, path FROM org_tree ORDER BY path;

Limitación de la profundidad de recursión

Los datos profundos o circulares pueden hacer que una CTE recursiva se ejecute durante mucho tiempo. Dos prácticas seguras:

1. Realice un seguimiento de la profundidad y añada una cláusula WHERE: WHERE depth < 10 garantiza que nunca se superen los 10 niveles.

2. Use una columna de detección de ciclos: algunas bases de datos (PostgreSQL 14+) ofrecen la sintaxis CYCLE para detectar automáticamente las visitas repetidas a nodos.

WITH RECURSIVE org_tree AS (
  SELECT id, name, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
  WHERE ot.depth < 10
)
SELECT depth, name FROM org_tree;

UNION frente a UNION ALL en CTE recursivas

El paso recursivo casi siempre usa UNION ALL, no UNION. Estas son las razones:

UNION elimina los duplicados después de cada iteración comparando todo el conjunto de resultados. Esto es extremadamente costoso y puede cambiar la semántica en grafos en los que se llega legítimamente al mismo nodo por varias rutas.

UNION ALL conserva todas las filas sin eliminar duplicados, lo que resulta más rápido y correcto al recorrer árboles. Use UNION únicamente cuando necesite eliminar duplicados por un motivo concreto y comprenda el coste de rendimiento.

Generación de una serie de fechas

Las CTE recursivas también resultan prácticas para generar secuencias de fechas. Este ejemplo produce cada día de una semana determinada, un patrón que se usa a menudo para crear informes de calendario o rellenar huecos en datos de series temporales.

WITH RECURSIVE date_series AS (
  SELECT DATE '2024-01-01' AS day
  UNION ALL
  SELECT day + INTERVAL '1 day'
  FROM date_series
  WHERE day < DATE '2024-01-07'
)
SELECT day FROM date_series;

Búsqueda de todos los subordinados de un gerente

Puede iniciar el caso base con cualquier nodo específico, no solo con la raíz. Aquí partimos de Bob (id=2) y encontramos a todas las personas que dependen de él directa o indirectamente.

Este patrón resulta útil para comprobar permisos, realizar agregaciones de subárboles o limitar los paneles a un único departamento.

WITH RECURSIVE subordinates AS (
  SELECT id, name
  FROM employees
  WHERE id = 2
  UNION ALL
  SELECT e.id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT name FROM subordinates;

Comprobación rápida

Compruebe su comprensión del funcionamiento de las CTE recursivas.

Resumen de la lección

En esta lección ha aprendido cómo funcionan las CTE recursivas:

Estructura: toda CTE recursiva tiene un caso base (las filas iniciales) unido a un paso recursivo (un SELECT autorreferenciado) mediante UNION ALL.

Terminación: el motor repite el paso recursivo y acumula los resultados hasta que el paso devuelve cero filas.

Usos habituales: recorrer organigramas y árboles de carpetas, generar secuencias de números o fechas, calcular rutas y encontrar todos los nodos de un subárbol.

Consejos de seguridad: incluya siempre una condición de terminación (un límite de profundidad o un control de ciclos) y prefiera UNION ALL a UNION por motivos de rendimiento.

Preguntas frecuentes

¿La lección «Cómo funcionan las CTE recursivas» es gratis?

Sí — el texto completo de «Cómo funcionan las CTE recursivas» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de SQL Academy, actualiza a CoddyKit PRO. El curso de SQL Academy incluye 4 lecciones en total.

¿Qué aprenderé en «Cómo funcionan las CTE recursivas»?

Caso base más paso recursivo Practicas SQL Academy con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.

¿Necesito experiencia previa para empezar SQL Academy?

No se requiere experiencia previa. SQL Academy en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 1 de 4.

¿Cuánto tiempo toma la lección «Cómo funcionan las CTE recursivas»?

La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.

¿Puedo escribir y ejecutar código en esta lección de SQL Academy?

Sí. Cada lección de SQL Academy incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.

Todas las lecciones de este curso

  1. Cómo funcionan las CTE recursivas
  2. Recorrido de un árbol de categorías
  3. Generación de series y secuencias
  4. Cómo evitar bucles infinitos
← Volver a SQL Academy