Nivel 4 · Avanzado · SQL · avanzado

[SQL 13] CTE y consultas recursivas

WITH para escribir consultas por pasos; WITH RECURSIVE para jerarquías y series.

0 / 6 retos resueltos 📘 SQL COMANDOS · CTE (Common Table Expression)

Al terminar esta práctica podrás…

Dividir una consulta compleja en pasos con nombre usando WITH.
Encadenar varias CTE y reutilizarlas en la consulta final.
Recorrer jerarquías (organigrama jefe → empleado) con WITH RECURSIVE.
Generar series (números, meses) para completar informes con ceros.

Una CTE (Common Table Expression) es un resultado temporal con nombre que existe solo durante la consulta. Se declara con WITH nombre AS (SELECT …) y luego se usa como una tabla. Hace lo mismo que una tabla derivada, pero se lee de arriba hacia abajo, paso a paso, y se puede reutilizar.

Una CTE recursiva se llama a sí misma. Tiene dos partes unidas por UNION ALL:

el caso base (punto de partida, por ejemplo el presidente), y
el paso recursivo, que se une con la propia CTE para avanzar un nivel (sus subordinados).

El motor repite el paso recursivo hasta que no aparecen filas nuevas. Es la forma estándar de recorrer jerarquías, árboles y grafos (en Oracle también existe CONNECT BY).

Sintaxis

WITH paso1 AS (SELECT …),
     paso2 AS (SELECT … FROM paso1 …)
SELECT … FROM paso2;

WITH RECURSIVE arbol(id, nivel) AS (
  SELECT id, 1 FROM t WHERE padre IS NULL      -- caso base
  UNION ALL
  SELECT t.id, a.nivel + 1                      -- paso recursivo
  FROM t JOIN arbol a ON t.padre = a.id
)
SELECT * FROM arbol;

WITH

Define resultados intermedios con nombre.

Varias CTE

Se separan con comas; una puede usar a las anteriores.

WITH RECURSIVE

Caso base UNION ALL paso recursivo.

Condición de término

La recursión para cuando el paso no produce filas nuevas (o con un WHERE n < 12).

Jerarquías

Organigramas, categorías, rutas: e.manager_id = arbol.id.

Series

Generar 1..12 para incluir meses sin datos con un LEFT JOIN.

SQLite / PostgreSQL / MySQL 8

WITH RECURSIVE …

Oracle

WITH … (sin RECURSIVE) · o CONNECT BY PRIOR employee_id = manager_id

1 · Ejemplos guiados · ejecuta y modifica cada bloque

01CTE simple

Primero calculamos el promedio por departamento; luego lo unimos con los empleados para ver quién lo supera.

sql-cte.sql