Organiza consultas por etapas con CTE

La idea clave

Una expresión de tabla común, o CTE por sus siglas en inglés, es un resultado con nombre que existe únicamente durante la sentencia que lo declara. Puedes imaginarla como una mesa de trabajo: primero preparas un conjunto pequeño y comprensible; después construyes la respuesta final a partir de él.

Para aprender, la ventaja principal es la claridad: en lugar de esconder un resultado dentro de muchos paréntesis, le das un nombre y lees la consulta por etapas: “primero prepara estas filas; después usa ese resultado”.

Rendimiento: usa una CTE para organizar el razonamiento, no como promesa de velocidad. Cada motor puede optimizarla de forma distinta; cuando el rendimiento importe, mídelo.

La sintaxis básica

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

1. WITH anuncia una o más CTE.
2. nombre_cte describe las filas producidas por la consulta interior.
3. AS (...) contiene el SELECT que prepara el resultado.
4. La consulta principal utiliza la CTE en FROM o JOIN igual que usaría una tabla.

Ejemplo paso a paso

Queremos encontrar clientes cuyo gasto válido —pedidos no cancelados— alcance al menos 200. La CTE resume primero el gasto por cliente; la consulta exterior añade el nombre y filtra el resumen.

WITH gasto_por_cliente AS (
  SELECT cliente_id,
         ROUND(SUM(total), 2) AS gasto_total
  FROM pedidos
  WHERE estado <> 'cancelado'
  GROUP BY cliente_id
)
SELECT c.nombre,
       g.gasto_total
FROM gasto_por_cliente AS g
JOIN clientes AS c ON c.id = g.cliente_id
WHERE g.gasto_total >= 200
ORDER BY g.gasto_total DESC, c.nombre;

Cómo se construye la respuesta

1. La CTE descarta los pedidos cancelados antes de sumar.
2. GROUP BY cliente_id produce una fila por cliente con su gasto válido.
3. La consulta principal enlaza ese resumen con clientes para mostrar nombres.
4. El WHERE exterior filtra una columna ya calculada por la CTE.
5. El ORDER BY exterior fija el orden observable del resultado final.

Un error habitual

WITH pedidos_recientes AS (
  SELECT id, fecha, total
  FROM pedidos
  ORDER BY fecha DESC
)
SELECT id, fecha, total
FROM pedidos_recientes;

Practica con datos

WITH pedidos_validos AS (
  SELECT cliente_id, total
  FROM pedidos
  WHERE estado <> 'cancelado'
)
SELECT cliente_id,
       ROUND(SUM(total), 2) AS total_valido
FROM pedidos_validos
GROUP BY cliente_id
HAVING SUM(total) >= 200
ORDER BY total_valido DESC, cliente_id;

Si todavía se mezcla con las subconsultas

Empieza por una pregunta simple: “¿necesito poner el resultado dentro de una expresión o quiero darle un nombre y seguir trabajando con sus filas?”. Una subconsulta escalar encaja en comparaciones como precio > (SELECT AVG(precio) ...). Una CTE encaja cuando el resultado intermedio tiene varias columnas, será unido con otra tabla o mejora la lectura por etapas. Repasa subconsultas y vuelve a transformar uno de sus ejemplos en una CTE.

Compatibilidad y límites del laboratorio

La forma ordinaria WITH nombre AS (SELECT ...) está documentada en PostgreSQL, MySQL 8.0 y SQLite. Los tres motores también ofrecen CTE recursivas, pero su sintaxis, límites y optimización requieren una lección aparte. El laboratorio de AprenderSQL.es ejecuta CTE ordinarias de lectura; no simula recursión ni operaciones de escritura dentro de WITH.

Fuentes técnicas consultadas

• PostgreSQL: consultas WITH
• MySQL 8.0: Common Table Expressions
• SQLite: cláusula WITH

Qué debes recordar

• Una CTE es un resultado con nombre y alcance de una sola sentencia.
• La consulta interior prepara filas; la consulta exterior construye la respuesta final.
• El nombre debe explicar qué contiene el resultado, no usar etiquetas vagas como datos1.
• Una CTE mejora la estructura, pero no garantiza por sí sola mejor rendimiento.
• El orden final solo queda definido por el ORDER BY de la consulta exterior.