Qué es una CTE en SQL (WITH) y cuándo usarla para ordenar tus consultas

Tu consulta tiene una subconsulta dentro de otra subconsulta, y ya no recuerdas qué hacía cada paréntesis. Una CTE sirve para eso: darle nombre a cada paso y leer la consulta de arriba hacia abajo. Aquí la usas con una base de ejemplo del blog, y todo se ejecuta en la página.

¿Qué es una CTE?

Es un bloque con nombre que va antes de tu SELECT principal:

WITH nombre AS (
  SELECT ...
)
SELECT ...
FROM nombre;

No crea ninguna tabla en la base de datos: existe solo mientras corre esa consulta. Piensa en ella como una tabla temporal que tú nombras y que solo ve tu consulta.

¿Qué problema resuelven? El de las subconsultas anidadas

Queremos los clientes cuyo gasto total en pedidos completados supera el gasto promedio de los clientes. Con subconsultas, el cálculo del gasto por cliente se escribe dos veces:

Funciona, pero cuesta leerla y el mismo bloque aparece dos veces. Si cambias un filtro, tienes que acordarte de cambiarlo en los dos lugares.

La misma consulta con WITH

Con una CTE defines el gasto por cliente una sola vez y lo usas las veces que quieras:

Mismo resultado: Ana y Carla, con 400 cada una. Pero ahora la consulta se lee en dos pasos: primero calculamos gasto, después comparamos contra su promedio.

¿Contra qué promedio se compara?

Vale la pena mirar la CTE de cerca, porque el promedio tiene un detalle con NULL:

Hay 6 clientes con pedidos completados, pero solo 5 tienen un total. Franco tiene un pedido completado con total_amount en NULL, así que su suma es NULL. AVG ignora los NULL: el promedio es 241, calculado entre 5 clientes y no entre 6. Una CTE no cambia ese comportamiento, pero te deja ver el paso intermedio y detectarlo.

¿Puedo encadenar varias CTE?

Sí. Separas cada una con una coma, y cada una puede usar las anteriores. Es la forma de armar un análisis paso a paso. Aquí, primero nos quedamos con los pedidos completados que tienen monto, y después los resumimos por mes:

Cada CTE tiene un trabajo: completados limpia, por_mes resume, y el SELECT final presenta. Si algo sale raro, puedes ejecutar cada bloque por separado para ver dónde está el problema.

¿Una CTE hace la consulta más rápida?

No necesariamente. Una CTE es sobre todo una herramienta de legibilidad. Que sea más rápida o más lenta depende del motor de base de datos y de cómo la ejecuta. No uses una CTE esperando mejorar el rendimiento; úsala para que tu consulta se entienda y se pueda mantener.

CTE, subconsulta y vista: ¿en qué se diferencian?

¿Qué son las CTE recursivas?

Existe una variante con WITH RECURSIVE para recorrer jerarquías, como un organigrama o categorías dentro de categorías. Es un tema aparte y lo veremos en otra lección. Todo lo que viste aquí aplica a las CTE normales.

¿Qué te preguntan sobre esto en una entrevista de SQL?

Suelen pedirte reescribir una consulta con subconsultas usando WITH, o resolver un problema por pasos. Lo que evalúan es si puedes dividir un problema grande en partes: primero filtrar, luego agrupar, luego comparar. Si cada CTE tiene un nombre que dice lo que hace (completados, por_mes), además comunicas bien.

Inténtalo tú mismo

Con una CTE, escribe una consulta que muestre los productos cuyo ingreso total en pedidos completados supera el promedio de ingresos por producto. El ingreso de un producto es la suma de quantity * unit_price. Muestra las columnas name y total, de mayor a menor ingreso. La CTE ya viene armada: completa el SELECT final.