Expresiones de tabla común: WITH — Aprende SQL

Aprende SQL · lección gratuita

Lección 26 · Expresiones de tabla común: WITH

Resumen

Una expresión de tabla común (CTE, Common Table Expression) es un conjunto de resultados temporal y con nombre, definido con la cláusula WITH justo antes de la consulta principal. Sirve para dar nombre a una subconsulta y reutilizarla, dejando el SQL plano y legible en lugar de anidar subconsultas dentro del FROM. Existe solo durante la ejecución de la sentencia: no crea ninguna tabla en disco.

WITH nombre AS (SELECT ...): declara la CTE; tras el paréntesis viene la consulta principal que la usa como si fuera una tabla.
Legibilidad: convierte una subconsulta anidada en un bloque con nombre que lees de arriba hacia abajo, no de adentro hacia afuera.
Reutilización: puedes referenciar la misma CTE varias veces en la consulta principal (p. ej. en un SELF JOIN o una subconsulta correlacionada) sin repetir su SELECT.
Alias de columnas opcional: WITH t(a, b) AS (...) renombra las columnas resultantes de la CTE.
Ámbito: la CTE solo es visible para la sentencia que la sigue; no persiste entre consultas.

Sintaxis / Conceptos

La estructura es WITH cte AS ( consulta ) consulta_principal. La consulta entre paréntesis se evalúa primero (conceptualmente) y su resultado queda disponible con el nombre de la CTE en el FROM/JOIN de la consulta principal.

Una CTE es equivalente a una subconsulta en el FROM, pero con dos ventajas: (1) tiene nombre, así que el lector entiende la intención; (2) se puede referenciar más de una vez. En la práctica el optimizador suele tratarlas igual que a una subconsulta, por lo que el cambio es sobre todo de claridad, no de rendimiento.

Errores comunes: olvidar que tras el ) de la CTE va directamente la consulta principal (sin una coma extra ni un SELECT suelto); o intentar usar la CTE en otra sentencia distinta (no existe fuera de su sentencia).

WITH ventas AS (SELECT empleado_id, SUM(cantidad * precio_unitario) AS total FROM detalle_pedidos d JOIN pedidos p ON d.pedido_id = p.id GROUP BY empleado_id) SELECT e.nombre, ROUND(v.total, 0) AS revenue FROM ventas v JOIN empleados e ON v.empleado_id = e.id ORDER BY revenue DESC LIMIT 5

Ejemplos

-- CTE básica: revenue por pedido, luego mostramos los mayores
WITH ingresos AS (
  SELECT pedido_id,
         SUM(cantidad * precio_unitario * (1 - descuento)) AS revenue
  FROM detalle_pedidos
  GROUP BY pedido_id
)
SELECT pedido_id, ROUND(revenue, 2) AS revenue
FROM ingresos
ORDER BY revenue DESC
LIMIT 5;

-- Filtrar usando un agregado calculado en la CTE (salario medio de la empresa)
WITH media AS (
  SELECT AVG(salario) AS sal_medio FROM empleados
)
SELECT e.nombre, e.salario
FROM empleados e, media m
WHERE e.salario > m.sal_medio
ORDER BY e.salario DESC;

-- Referenciar la misma CTE dos veces (deptos sobre el promedio global)
WITH por_depto AS (
  SELECT departamento_id, AVG(salario) AS prom FROM empleados GROUP BY departamento_id
)
SELECT d.departamento_id, ROUND(d.prom, 0) AS prom_depto
FROM por_depto d
WHERE d.prom > (SELECT AVG(prom) FROM por_depto)
ORDER BY prom_depto DESC;

💡 Una CTE no acelera por sí sola: su valor es la legibilidad y la reutilización. Si te encuentras anidando subconsultas a tres niveles, casi siempre conviene reescribirlas como CTEs encadenadas (siguiente lección).

Cheatsheet

Cláusula / Función	Qué hace
`WITH t AS (SELECT ...) SELECT ... FROM t`	Define una CTE y la usa en la consulta principal
`WITH t(a,b) AS (...)`	Define la CTE renombrando sus columnas
CTE vs subconsulta	Misma semántica; la CTE tiene nombre y es reutilizable
Ámbito de la CTE	Solo la sentencia que sigue al `WITH`; no persiste

---

← Tablas derivadas (subconsultas en FROM)CTEs encadenados →

Ver todas las lecciones de Aprende SQL →