Una CTE (Common Table Expression, expresión de tabla común) es una consulta a la que le das un nombre al principio de la sentencia, con WITH, para usarla después como si fuera una tabla. Sirve para lo mismo que una subconsulta, pero se lee de arriba abajo, en el orden en que piensas el problema.

Sintaxis
WITH nombreCte AS (
    SELECT ...
)
SELECT ...
FROM nombreCte;

La CTE solo existe mientras dura la sentencia: no se guarda en la base de datos, a diferencia de una vista.

Primera CTE

Queremos los clientes que han gastado más que la media de gasto por cliente. Sin CTE habría que anidar una subconsulta dentro de otra. Con CTE, primero calculamos el gasto de cada cliente y después lo usamos dos veces:

PostgreSQL en tu navegador · Ctrl+Enter para ejecutar · los cambios se deshacen al terminar
nombre apellidos gasto
Luis Sanchez García 4999,00
Ana Fernandez Montero 4304,25
María Lopez ruiz 3199,78

El gasto medio de los cinco clientes con pedidos es de 3.170,50 €, y solo tres lo superan. Prueba a ejecutar únicamente el SELECT de dentro de la CTE para ver la tabla intermedia.

Varias CTE en la misma consulta

Se pueden definir varias separándolas con comas, y cada una puede usar las anteriores. Es la forma más clara de construir una consulta compleja por pasos:

PostgreSQL en tu navegador · Ctrl+Enter para ejecutar · los cambios se deshacen al terminar
nombre pedidos pedidoMasCaro
Luis 2 2999,50
María 2 2199,80

Fíjate en que en el WHERE final sí podemos usar el alias pedidos: dentro de la CTE resumen es ya una columna con nombre.

CTE frente a subconsulta, vista y tabla temporal

Dónde vive Se puede reutilizar Cuándo usarla
Subconsulta Dentro de la consulta No Cálculos cortos que se usan una vez
CTE En la sentencia Varias veces dentro de la misma sentencia Consultas largas que se entienden mejor por pasos
Vista En la base de datos En cualquier consulta Lógica que usan varias consultas o usuarios
Tabla temporal En tempdb hasta que se borra En toda la sesión Resultados intermedios grandes que se consultan muchas veces

CTE recursivas

Una CTE puede referirse a sí misma. Es la forma estándar de recorrer jerarquías, como un organigrama o un árbol de categorías. Tiene dos partes unidas por UNION ALL: el ancla, que da el punto de partida, y la parte recursiva, que se repite hasta que no devuelve filas.

El ejemplo más sencillo genera los números del 1 al 5:

WITH numeros AS (
    SELECT 1 AS n           -- ancla
    UNION ALL
    SELECT n + 1            -- parte recursiva
    FROM numeros
    WHERE n < 5
)
SELECT n FROM numeros;
n
1
2
3
4
5
En SQL Server se escribe WITH a secas. PostgreSQL, MySQL y SQLite exigen WITH RECURSIVE para las CTE recursivas, por eso este ejemplo no es ejecutable en el editor de la página. SQL Server corta la recursión a los 100 niveles para evitar bucles infinitos; se puede cambiar con OPTION (MAXRECURSION n).

Errores frecuentes

  • Olvidar el punto y coma anterior. En SQL Server, si la sentencia previa no termina en ;, el WITH puede confundirse con otra cláusula. Por eso es habitual ver ;WITH.
  • Usar la CTE en una segunda sentencia. Solo existe para la sentencia que va justo detrás.
  • Ordenar dentro de la CTE. Un ORDER BY dentro de la CTE no garantiza el orden del resultado final; ponlo en el SELECT de fuera.

Practica

  1. Modifica la primera CTE para devolver los clientes que gastan menos que la media.
  2. Añade a la segunda consulta una columna con el gasto total de cada cliente.
  3. Escribe una CTE con los pedidos de septiembre de 2022 y úsala para contar cuántos hay por descripción de producto.