CTEs con WITH — Subconsultas y CTEs

El problema: subconsultas anidadas e ilegibles

Las subconsultas resuelven mucho, pero cuando se anidan se vuelven difíciles de leer: hay que leer de dentro hacia afuera y la misma subconsulta puede repetirse.

CTE: una subconsulta con nombre

Una CTE (Common Table Expression) es una subconsulta a la que le pones un nombre con la cláusula WITH, antes del SELECT principal. Luego la usas como si fuera una tabla más:

WITH precios_categoria AS (
  SELECT categoria_id, AVG(precio) AS media
  FROM productos
  GROUP BY categoria_id
)
SELECT c.nombre, pc.media
FROM precios_categoria pc
JOIN categorias c ON c.id = pc.categoria_id
ORDER BY pc.media DESC;

Ventajas:

Legibilidad: la consulta se lee de arriba abajo, como pasos.
Reutilización: puedes referenciar la CTE varias veces en el mismo SELECT sin repetir su código.
Composición: puedes definir varias CTEs separadas por comas, y unas pueden apoyarse en las anteriores.

WITH
  caros AS (
    SELECT * FROM productos WHERE precio > 100
  ),
  con_stock AS (
    SELECT * FROM caros WHERE stock > 0   -- se apoya en la CTE anterior
  )
SELECT nombre, precio, stock FROM con_stock ORDER BY precio DESC;

CTEs recursivas (mención)

Con WITH RECURSIVE una CTE puede referenciarse a sí misma, lo que permite recorrer estructuras jerárquicas (un árbol de categorías padre/hijo, una lista de empleados y sus jefes) o generar series de valores. Es un tema avanzado: de momento basta con saber que existe y que es la herramienta para datos en forma de árbol o grafo, algo que las subconsultas normales no pueden expresar.

Regla práctica: si te encuentras copiando y pegando la misma subconsulta, o anidando tres niveles, conviértela en una CTE con WITH.

Ejemplos

WITH para dar nombre a un cálculo intermedio

WITH stats AS (
  SELECT AVG(precio) AS media FROM productos
)
SELECT nombre, precio
FROM productos, stats
WHERE precio > stats.media
ORDER BY precio DESC;

Varias CTEs encadenadas

WITH
  caros AS (SELECT * FROM productos WHERE precio > 100),
  con_stock AS (SELECT * FROM caros WHERE stock > 0)
SELECT nombre, precio FROM con_stock ORDER BY precio DESC;