El desconocido de SQL: usa WITH (CTEs) y dejar de usar consultas anidadas

Hoy os voy a contar algo que desde que lo he descubierto... quién me lo hubiera contado hace años!! Porque esto en la facultad no se enseña! jaja

Empezamos por el comienzo con un ejemplo claro. Esta query en SQL es un query típica del día a día

SELECT * FROM pedidos 
WHERE cliente_id IN (
     SELECT cliente_id
     FROM clientes
     WHERE pais = 'ES'
     AND fecha_alta < '2023-01-01'
);

Funcionar... funciona si!. Pero en cuanto la lógica crece (dos o tres niveles de sub-consultas, cálculos intermedios, etc...) la query se vuelve imposible de leer, de depurar y de mantener.  Pues esto se puede solucionar usando CTEs (Common Table Expression) mediante el comando sql WITH

CTEs y WITH

Un CTE es básicamente una tabla temporal con nombre que vive solo durante la ejecución de tu query. Se declara al principio con "WITH nombre As (...)" y luego la usas como si fuera una tabla más en tu query.

Una de las razones que no se esté usando mucho es por el soporte!!:

• MySQL soporta WITH desde la versión 8.0 (2018)

• MariaDB soporta WITH desde la versión 10.2

Esto hace que tanto en tutoriales como en cursos no se enseñe esto porque es algo "novedad" y se sigue enseñando las queries anidadas (que están bien eh!!)

¿Hay realmente diferencia entre WITH y queries anidadas?

Yo diría que hay bastante sip!!! Te dejo por aquí algunos motivos que he visto:

1. Cálculos previos con nombre propio. Puedes resolver una agregación o un filtro complejo una sola vez, ponerle un nombre legible, y reutilizarlo cuantas veces quieras en la misma query
2. Legibilidad de arriba a abajo. La query es mucho más legible. Se acerca casi a un algoritmo: "primero calculo esto, luego esto otro, y al final combino los resultados". Nada de sub-consultas escondidas dentro del los WHERE que hay que leer de dentro hacia fuera.
3. Múltiples CTEs encadenados. Puedes declarar varios WITH separados por comas, y que cada uno use al anterior. Esto es imposible de replicar limpiamente con subconsultas anidadas sin duplicar código.

Ejemplo concreto: de subconsulta anidada a CTE

Supongamos una tabla pedidos (con cliente_id, total, fecha) y queremos sacar los clientes cuyo gasto total en los últimos 12 meses supera la media de gasto de todos los clientes activos.
Con sub-consultas anidadas (el enfoque clásico):

SELECT c.id, c.nombre, gasto.total_gastado
FROM clientes c
JOIN (
   SELECT cliente_id, SUM(total) AS total_gastado
   FROM pedidos
   WHERE fecha >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
   GROUP BY cliente_id
) gasto ON gasto.cliente_id = c.id
WHERE gasto.total_gastado > (
   SELECT AVG(total_gastado)
   FROM (
       SELECT cliente_id, SUM(total) AS total_gastado
       FROM pedidos
       WHERE fecha >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
       GROUP BY cliente_id
   ) sub
);

Un problema que ya veo aquí es: la misma sub-consulta de agregación se repite dos veces. Si mañana cambias la ventana de tiempo de 12 a 6 meses, tienes que recordar cambiarla en los dos sitios.

Con WITH (CTEs):

WITH gasto_por_cliente AS (
   SELECT cliente_id, SUM(total) AS total_gastado
   FROM pedidos
   WHERE fecha >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
   GROUP BY cliente_id
),
media_gasto AS (
   SELECT AVG(total_gastado) AS media
   FROM gasto_por_cliente
)
SELECT c.id, c.nombre, g.total_gastado
FROM clientes c
JOIN gasto_por_cliente g ON g.cliente_id = c.id
JOIN media_gasto m
WHERE g.total_gastado > m.media;

El cálculo se hace una sola vez, tiene nombre (gasto_por_cliente, media_gasto), y el query final es muy sencilla de entender. Cambiar la ventana temporal ahora es tocar una sola línea, no como en el caso de queries anidadas.

Y hasta aquí por hoy! Espero que os guste y que empecéis a usar cosillas nuevas!!