CTE vs subconsulta en SQL: claridad, reutilización y rendimiento

Una CTE da nombre a un resultado mediante WITH; una subconsulta incluye una consulta dentro de otra sentencia. Elige la forma que permita entender mejor las transformaciones. Una CTE no es automáticamente más rápida ni constituye una tabla permanente.

Aquí comparamos una CTE ordinaria, no recursiva, con una subconsulta como tabla derivada en FROM. Las subconsultas escalares, las correlacionadas y las CTE recursivas resuelven otros problemas; no son intercambiables en todos estos ejemplos.

Situación	Punto de partida útil
Un cálculo intermedio breve	Subconsulta
Varios pasos con nombres descriptivos	CTE
Reutilizar el resultado entre sentencias independientes	Considerar una vista o tabla temporal
Decidir por rendimiento	Medir en la base de datos real

Buscamos clientes con al menos 150 en pagos realizados. Los pagos reembolsados no deben contribuir al total.

Pruébalo en SQLite

Abre SQL Lab, pega esta preparación y ejecútala una vez. Después ejecuta cada consulta por separado. Los nombres de tablas y columnas se mantienen en inglés para usar el mismo SQL en todos los idiomas. La interfaz del laboratorio y las lecciones enlazadas están en inglés.

CREATE TABLE blog_payments (
  id INTEGER PRIMARY KEY, customer_id INTEGER, status TEXT, amount INTEGER
);
INSERT INTO blog_payments VALUES
  (1, 1, 'paid', 120), (2, 1, 'paid', 80), (3, 1, 'refunded', 90),
  (4, 2, 'paid', 60), (5, 3, 'paid', 200);

1. Usa una subconsulta como tabla derivada

La consulta interior filtra los pagos realizados y suma por cliente. La exterior filtra los totales. El alias totals identifica la tabla derivada. Los clientes 1 y 3 cumplen la condición con 200 cada uno; el cliente 2 tiene solo 60.

SELECT customer_id, paid_total
FROM (
  SELECT customer_id, SUM(amount) AS paid_total
  FROM blog_payments
  WHERE status = 'paid'
  GROUP BY customer_id
) AS totals
WHERE paid_total >= 150
ORDER BY customer_id;

Resultado esperado

customer_id	paid_total
1	200
3	200

2. Nombra el mismo resultado con una CTE

El resultado es idéntico. WITH coloca el cálculo intermedio antes del SELECT principal y lo llama paid_totals. El nombre existe únicamente dentro de esta sentencia. Si ejecutas otro SELECT independiente, paid_totals no será una tabla almacenada que puedas consultar.

WITH paid_totals AS (
  SELECT customer_id, SUM(amount) AS paid_total
  FROM blog_payments
  WHERE status = 'paid'
  GROUP BY customer_id
)
SELECT customer_id, paid_total
FROM paid_totals
WHERE paid_total >= 150
ORDER BY customer_id;

Resultado esperado

customer_id	paid_total
1	200
3	200

3. Encadena transformaciones sin anidación profunda

La primera CTE calcula los totales pagados. La segunda clasifica a los clientes; la consulta principal resume los segmentos. Así separas filtrado, agregación por cliente, clasificación e informe final. El segmento high contiene dos clientes y 400 de ingresos; standard contiene uno y 60.

WITH paid_totals AS (
  SELECT customer_id, SUM(amount) AS paid_total
  FROM blog_payments
  WHERE status = 'paid'
  GROUP BY customer_id
), customer_segments AS (
  SELECT customer_id, paid_total,
    CASE WHEN paid_total >= 150 THEN 'high' ELSE 'standard' END AS segment
  FROM paid_totals
)
SELECT segment, COUNT(*) AS customers, SUM(paid_total) AS revenue
FROM customer_segments
GROUP BY segment
ORDER BY segment;

Resultado esperado

segment	customers	revenue
high	2	400
standard	1	60

Practica y comprueba tu respuesta

Cambia el umbral de 150 a 210 en las dos primeras consultas. Ambas deben devolver cero filas. Después incluye todos los estados de pago y restaura el umbral de 150: el cliente 1 llega a 290 y el cliente 3 mantiene 200. Esto comprueba que el filtro de filas define el resultado de negocio, no la elección entre CTE y subconsulta.

Para evaluar el rendimiento, compara planes de ejecución y tiempos en el motor y los datos reales. PostgreSQL puede integrar ciertas CTE en la consulta principal o materializarlas según la consulta; su documentación explica las condiciones y las opciones. SQLite tiene su propio comportamiento de planificación. Nombrar un resultado no garantiza una sola ejecución física ni una caché.

No añadas opciones de materialización como hábito general de optimización. Una investigación útil registra versión, índices, número de filas, plan y tiempos de varias ejecuciones. Este ejemplo pequeño enseña estructura y corrección; no permite elegir un ganador para producción.

Usa una CTE cuando un nombre significativo elimina ambigüedad. Usa una subconsulta breve cuando mantiene junta la lógica relacionada. En ambos casos, especifica las columnas necesarias en vez de SELECT * y comprueba los recuentos antes y después de los JOIN para detectar duplicaciones accidentales.

Documentación y próximos pasos

Prueba las lecciones de CTE y subconsultas en FROM. Los ejemplos funcionan en SQLite y utilizan WITH ordinario, también disponible en PostgreSQL y MySQL 8. No usan opciones de materialización específicas del motor.

• Documentación oficial de SQLite
• Documentación oficial del motor