Expresiones de tabla comunes: CTEs

Una CTE (Common Table Expression) es una consulta temporal con nombre que puedes definir antes de tu consulta principal usando la palabra clave WITH. Piensa en ella como una variable que almacena un resultado intermedio para usarlo después.

Diagrama: “Sin CTE (difícil de leer)” vs “Con CTE (claro y reutilizable)”: bloque CTE WITH conteo AS (...) + flecha → SELECT * FROM conteo WHERE total > 1;.

Sintaxis básica

WITH nombre_cte AS (
  SELECT ...
)
SELECT * FROM nombre_cte;

La CTE se define entre WITH y el SELECT principal. El nombre que le des funciona como si fuera una tabla temporal que solo existe durante esa consulta.

¿Por qué usar CTEs?

Compara estas dos consultas que buscan desarrolladores con más de un juego:

Sin CTE (subconsulta anidada):

SELECT developer, total
FROM (
  SELECT developer, COUNT(*) AS total
  FROM videogames
  GROUP BY developer
)
WHERE total > 1;

Con CTE (mucho más legible):

WITH conteo AS (
  SELECT developer, COUNT(*) AS total
  FROM videogames
  GROUP BY developer
)
SELECT developer, total
FROM conteo
WHERE total > 1;

El resultado es idéntico, pero la versión con CTE es más fácil de leer, depurar y mantener. Le das un nombre descriptivo al paso intermedio y la consulta principal queda limpia.

Múltiples CTEs

Puedes definir varias CTEs separadas por comas:

WITH
juegos_largos AS (
  SELECT title, developer, playtime_hours
  FROM videogames
  WHERE playtime_hours > 50
),
conteo_devs AS (
  SELECT developer, COUNT(*) AS total
  FROM videogames
  GROUP BY developer
)
SELECT j.title, j.playtime_hours, c.total
FROM juegos_largos j
JOIN conteo_devs c ON j.developer = c.developer;

Cada CTE puede referenciar a las CTEs definidas antes de ella, lo que permite construir consultas complejas paso a paso.

CTEs vs subconsultas

Característica	CTE (WITH)	Subconsulta
Legibilidad	Muy alta, con nombre descriptivo	Puede ser difícil de seguir si se anida
Reutilización	Se puede usar varias veces en la misma consulta	Hay que repetir la subconsulta
Recursividad	Soporta WITH RECURSIVE	No
Rendimiento	Similar en la mayoría de casos	Similar en la mayoría de casos

Tip: Usa CTEs cuando la subconsulta sea compleja o cuando necesites reutilizar el mismo resultado intermedio en varias partes de la consulta.

Nuestra tabla

Nombre de la Tabla: videogames

id	title	developer	year	playtime_hours
1	The Legend of Zelda	Nintendo	1986	20
2	Super Mario Bros.	Nintendo	1985	10
3	Final Fantasy VII	Square Enix	1997	40
4	The Witcher 3	CD Projekt Red	2015	100
5	Minecraft	Mojang Studios	2011	9999
6	Grand Theft Auto V	Rockstar Games	2013	80
7	Dark Souls	FromSoftware	2011	60
8	Portal 2	Valve	2011	8
9	Red Dead Redemption 2	Rockstar Games	2018	70
10	The Last of Us	Naughty Dog	2013	15
11	Halo: Combat Evolved	Bungie	2001	10
12	God of War	Santa Monica Studio	2018	25