Árboles y secuencias: WITH RECURSIVE

En resumen: WITH RECURSIVE es una consulta con un paso que lee su propio resultado, así que puede recorrer árboles como las categorías o generar una secuencia de fechas.

Problema

La ruta hasta la categoría

Encima de cada producto el sitio web muestra la ruta hasta él: «Alimentos > Lácteos». Las categorías son un árbol: cada una tiene un parent_id que apunta a la categoría de arriba. Con un self join del módulo sobre unir tablas se hace así:

SQLbase de datos: tienda

SELECT c.name, p.name AS parent
FROM categories c
LEFT JOIN categories p ON p.id = c.parent_id;

SELECT c.name, p.name AS parent FROM categories c LEFT JOIN categories p ON p.id = c.parent_id;

▶ EjecutarRestaurar la consultaBase nueva

Esto funciona porque el árbol tiene dos niveles. Si mañana aparece «Quesos» bajo «Lácteos», necesitas un join más, y para cuatro niveles otro más. No sabes de antemano cuántos niveles habrá.

La idea

Un paso que se lee a sí mismo

WITH RECURSIVE permite que un paso lea sus propias filas. Este es el ejemplo más pequeño, los números del 1 al 5:

SQLbase de datos: tienda

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

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

▶ EjecutarRestaurar la consultaBase nueva

El paso tiene dos partes, unidas por UNION ALL:

1. El inicio SELECT 1 da la primera fila.
2. La parte recursiva SELECT x + 1 FROM n WHERE x < 5 toma las filas de la ronda anterior y crea otras nuevas: de 1 hace 2, de 2 hace 3.

Esto se repite hasta que la parte recursiva no devuelve nada: con x = 5 la condición ya no es verdadera. Un paso así se llama CTE recursiva.

Sin WHERE x < 5 la consulta no pararía nunca. Toda parte recursiva necesita una condición que tarde o temprano la detenga.

La idea

El árbol de categorías

Ahora la ruta. El inicio son las categorías principales (sin padre). La parte recursiva encuentra los hijos de todo lo encontrado hasta ahora y añade su nombre a la ruta:

SQLbase de datos: tienda

WITH RECURSIVE tree(id, name, path, level) AS (
  SELECT id, name, name, 1
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, t.path || ' > ' || c.name, t.level + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT path, level FROM tree
ORDER BY path;

WITH RECURSIVE tree(id, name, path, level) AS ( SELECT id, name, name, 1 FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, t.path || ' > ' || c.name, t.level + 1 FROM categories c JOIN tree t ON c.parent_id = t.id ) SELECT path, level FROM tree ORDER BY path;

▶ EjecutarRestaurar la consultaBase nueva

En la primera ronda están «Alimentos» y «Hogar» (nivel 1). En la segunda están las seis subcategorías (nivel 2), cada una con la ruta de su padre, « > » y su propio nombre. En la tercera ronda ya no hay hijos y todo se detiene. Si el árbol se hace más profundo, la misma consulta llegará también a los niveles nuevos.

Pruébalo

Todo lo de «Alimentos»

Muestra el nombre y el precio de todos los productos de «Alimentos», junto con sus subcategorías. Obtén los números de categoría con un paso recursivo, no a mano. El campo empieza con el inicio del paso.

SQLbase de datos: tienda

SELECT id FROM categories WHERE name = 'Alimentos';

SELECT id FROM categories WHERE name = 'Alimentos';

Comprobar▶ Ejecutar

Míralo

Los días sin ventas

La gerencia quiere un gráfico de las ventas por día de octubre de 2026. El primer intento:

SQLbase de datos: tienda

SELECT o.ordered_at, ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.ordered_at BETWEEN '2026-10-01' AND '2026-10-31'
GROUP BY o.ordered_at;

SELECT o.ordered_at, ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.ordered_at BETWEEN '2026-10-01' AND '2026-10-31' GROUP BY o.ordered_at;

▶ EjecutarRestaurar la consultaBase nueva

Cinco filas. Octubre tiene 31 días, y el gráfico uniría el 3 de octubre directamente con el 5 y escondería que del 7 al 17 de octubre no se vendió nada. Un día sin pedidos no está en orders, así que GROUP BY no tiene de qué crear una fila.

La idea

Una secuencia de fechas

Un paso recursivo puede crear todos los días por sí solo. El inicio es el 1 de octubre, y cada ronda añade un día con date(day, '+1 day'):

SQLbase de datos: tienda

WITH RECURSIVE days(day) AS (
  SELECT '2026-10-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM days WHERE day < '2026-10-31'
)
SELECT day FROM days;

WITH RECURSIVE days(day) AS ( SELECT '2026-10-01' UNION ALL SELECT date(day, '+1 day') FROM days WHERE day < '2026-10-31' ) SELECT day FROM days;

▶ EjecutarRestaurar la consultaBase nueva

31 filas. Ahora las ventas se unen a los días con LEFT JOIN, y para los días sin pedidos COALESCE pone 0:

SQLbase de datos: tienda

WITH RECURSIVE days(day) AS (
  SELECT '2026-10-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM days WHERE day < '2026-10-31'
)
SELECT d.day, COUNT(DISTINCT o.id) AS orders,
  COALESCE(ROUND(SUM(oi.quantity * oi.unit_price), 2), 0) AS revenue
FROM days d
LEFT JOIN orders o ON o.ordered_at = d.day
LEFT JOIN order_items oi ON oi.order_id = o.id
GROUP BY d.day
ORDER BY d.day;

WITH RECURSIVE days(day) AS ( SELECT '2026-10-01' UNION ALL SELECT date(day, '+1 day') FROM days WHERE day < '2026-10-31' ) SELECT d.day, COUNT(DISTINCT o.id) AS orders, COALESCE(ROUND(SUM(oi.quantity * oi.unit_price), 2), 0) AS revenue FROM days d LEFT JOIN orders o ON o.ordered_at = d.day LEFT JOIN order_items oi ON oi.order_id = o.id GROUP BY d.day ORDER BY d.day;

▶ EjecutarRestaurar la consultaBase nueva

Están los 31 días, también los que tienen cero. El orden del LEFT JOIN importa: a la izquierda están los días, de los que quieres todos, a la derecha los pedidos, que pueden faltar.

Más precisamente, para curiosos

PostgreSQL tiene una función ya hecha para una secuencia: generate_series(DATE '2026-10-01', DATE '2026-10-31', INTERVAL '1 day'). En SQLite y en MySQL (desde la versión 8) una secuencia así se hace con WITH RECURSIVE, como aquí.

Ejercicio

Dos tareas

1. ¿En cuántos días de octubre de 2026 la tienda no tuvo ningún pedido?

Cuenta los días de octubre de 2026 sin un solo pedido, hasta el final del mes, incluidos los días posteriores al último pedido (columna quiet_days). El campo ya tiene la secuencia de días.

SQLbase de datos: tienda

WITH RECURSIVE days(day) AS (
  SELECT '2026-10-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM days WHERE day < '2026-10-31'
)
SELECT day FROM days;

WITH RECURSIVE days(day) AS ( SELECT '2026-10-01' UNION ALL SELECT date(day, '+1 day') FROM days WHERE day < '2026-10-31' ) SELECT day FROM days;

Comprobar▶ Ejecutar

2. El sitio web quiere la ruta completa de cada producto: «Alimentos > Lácteos > Queso blanco».

Muestra el nombre de cada producto que tiene categoría y su ruta completa (columna breadcrumb), desde la categoría principal hasta el nombre del producto. El campo ya tiene el árbol.

SQLbase de datos: tienda

WITH RECURSIVE tree(id, name, path, level) AS (
  SELECT id, name, name, 1
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, t.path || ' > ' || c.name, t.level + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT path, level FROM tree;

WITH RECURSIVE tree(id, name, path, level) AS ( SELECT id, name, name, 1 FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, t.path || ' > ' || c.name, t.level + 1 FROM categories c JOIN tree t ON c.parent_id = t.id ) SELECT path, level FROM tree;

Comprobar▶ Ejecutar

Test

Ponte a prueba

1. ¿Qué dos partes tiene un paso recursivo?

Un inicio, UNION ALL y una parte que lee el propio pasoSELECT y WHEREDos CTE con el mismo nombre

2. ¿Qué pasa si quitas WHERE x < 5 de la parte recursiva?

Devuelve de 1 a 5Nada: la base de datos se detiene solaLa consulta nunca termina

3. Un informe por días muestra solo 5 días de octubre. ¿Por qué?

Porque BETWEEN no incluye el finalGROUP BY crea filas solo para las fechas que existen en ordersPorque las fechas son texto

4. Para la ruta «Alimentos > Lácteos», ¿por qué el paso recursivo es mejor que un self join?

No necesita parent_idFunciona para un árbol de cualquier profundidad, no solo de dos nivelesEs más rápido

Comprobar

Empezar la lección de nuevo

Lecciones relacionadas

• Se basa en: LEFT JOIN: también las filas sin pareja · Muchas tablas, y una tabla unida consigo misma · Subconsultas · Un informe por pasos: WITH
• Continúa en: Bases de datos de grafos
• Ejercicios sobre el tema: Subconsultas y ventanas