CTE: WITH para escribir en pasos

Intermedio

Una CTE (Common Table Expression, expresión de tabla común) es una consulta con nombre que se define con WITH antes de la consulta principal. Permite escribir el análisis en pasos que se leen de arriba abajo, y cada paso puede usar los anteriores.

Sintaxis

WITH paso_1 AS (
  SELECT ...
),
paso_2 AS (
  SELECT ... FROM paso_1 ...
)
SELECT ...
FROM paso_2;

Ejemplo

WITH pedidos_de_lima AS (
  SELECT o.customer_id
  FROM orders AS o
  INNER JOIN restaurants AS r ON r.id = o.restaurant_id
  WHERE r.city_id = 7
    AND o.status = 'delivered'
    AND o.placed_at >= '2025-07-01'
    AND o.placed_at < '2025-09-01'
),
frecuencia AS (
  SELECT customer_id, count(*) AS pedidos
  FROM pedidos_de_lima
  GROUP BY customer_id
)
SELECT
  CASE WHEN pedidos >= 4 THEN '4 o más' ELSE pedidos::text END AS pedidos_en_el_periodo,
  count(*) AS clientes
FROM frecuencia
GROUP BY pedidos_en_el_periodo
ORDER BY pedidos_en_el_periodo;

Resultado

4 filas · Pídelo

4 filas · Pídelo
pedidos_en_el_periodo	clientes
1	49
2	13
3	10
4 o más	24

Cómo leerlo

Es la frecuencia de compra en Lima en julio y agosto de 2025: 49 clientes pidieron una sola vez y 24 pidieron cuatro veces o más. Cada CTE tiene un nombre que explica qué contiene (pedidos_de_lima, frecuencia), así que se puede revisar un paso por separado cambiando solo el SELECT final.

Error común

Encadenar tres o cuatro subconsultas en el FROM, una dentro de otra. El resultado es el mismo, pero hay que leerlo de adentro hacia afuera y no se puede probar un paso sin copiarlo aparte. Si una lógica se repite, o la consulta tiene más de un paso, una CTE suele ser más fácil de revisar.

En otros motores: PostgreSQL, MySQL, BigQuery, SQL Server, Oracle

PostgreSQLMySQLBigQuery

Una CTE recursiva se referencia a sí misma para recorrer jerarquías de cualquier profundidad. Estos motores exigen la palabra WITH RECURSIVE. MySQL tiene CTE desde la versión 8.0.

WITH RECURSIVE arbol AS (
  SELECT id, name, parent_id, 1 AS nivel
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, a.nivel + 1
  FROM categories AS c
  INNER JOIN arbol AS a ON c.parent_id = a.id
)
SELECT * FROM arbol;

SQL ServerOracle
La CTE recursiva se escribe con WITH a secas, sin la palabra RECURSIVE. Oracle además exige la lista de columnas después del nombre: WITH arbol (id, name, parent_id, nivel) AS (...).
PostgreSQL
Desde la versión 12, una CTE usada una sola vez se integra en la consulta principal y el motor la optimiza como una subconsulta. WITH x AS MATERIALIZED (...) fuerza a calcularla una sola vez y guardar su resultado.

Estos fragmentos no se ejecutan en el curso: se muestran como referencia de cada motor.

WITH pedidos_de_lima AS ( SELECT o.customer_id FROM orders AS o INNER JOIN restaurants AS r ON r.id = o.restaurant_id WHERE r.city_id = 7 AND o.status = 'delivered' AND o.placed_at >= '2025-07-01' AND o.placed_at < '2025-09-01' ), frecuencia AS ( SELECT customer_id, count(*) AS pedidos FROM pedidos_de_lima GROUP BY customer_id ) SELECT CASE WHEN pedidos >= 4 THEN '4 o más' ELSE pedidos::text END AS pedidos_en_el_periodo, count(*) AS clientes FROM frecuencia GROUP BY pedidos_en_el_periodo ORDER BY pedidos_en_el_periodo;

pedidos_en_el_periodo

clientes

4 o más

WITH RECURSIVE arbol AS ( SELECT id, name, parent_id, 1 AS nivel FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, c.parent_id, a.nivel + 1 FROM categories AS c INNER JOIN arbol AS a ON c.parent_id = a.id ) SELECT * FROM arbol;