Contenido de esta guía
La diferencia esencial
Una subconsulta es un SELECT incluido dentro de otra consulta. Una CTE —common table expression— también parte de una consulta auxiliar, pero le asigna un nombre mediante WITH para poder referirse a ese resultado dentro de la misma sentencia.
Cuando la subconsulta está en FROM, ambas formas pueden representar exactamente el mismo paso lógico. La elección suele ser de estructura: una subconsulta mantiene el cálculo cerca del lugar donde se usa; una CTE lo mueve al principio y le da un nombre que puede hacer más legible una consulta larga.
Esto no significa que toda subconsulta pueda sustituirse mecánicamente por una CTE. Una subconsulta escalar en una expresión o una subconsulta correlacionada dentro de EXISTS cumple otra función y a menudo resulta más natural en su ubicación original.
Comparación rápida
| Criterio | Subconsulta | CTE |
|---|---|---|
| Dónde se define | Dentro de otra parte de la consulta | Al comienzo con WITH |
| Nombre propio | Solo si actúa como tabla derivada y recibe alias | Sí, el nombre identifica el resultado intermedio |
| Lectura por etapas | Útil cuando el paso es corto y local | Útil cuando quieres separar etapas con nombres claros |
| Varias referencias | Una tabla derivada concreta se referencia donde aparece | Una CTE puede referenciarse varias veces dentro de la sentencia, sujeto a las reglas del motor |
| Recursión | No es su uso normal | Las CTE recursivas tienen sintaxis y reglas específicas |
| Rendimiento | No hay un ganador universal: el optimizador y la consulta concreta importan | |
Mismo problema, mismo resultado
Queremos sumar el importe de pedidos enviados por cliente y después mostrar el nombre del cliente. Primero resolvemos el resumen por cliente_id y luego lo unimos con clientes.
Versión con subconsulta en FROM
SELECT c.nombre, r.total_enviado
FROM clientes AS c
JOIN (
SELECT cliente_id,
ROUND(SUM(total), 2) AS total_enviado
FROM pedidos
WHERE estado = 'enviado'
GROUP BY cliente_id
) AS r ON r.cliente_id = c.id
ORDER BY r.total_enviado DESC, c.nombre;Versión con CTE
WITH enviados_por_cliente AS (
SELECT cliente_id,
ROUND(SUM(total), 2) AS total_enviado
FROM pedidos
WHERE estado = 'enviado'
GROUP BY cliente_id
)
SELECT c.nombre, e.total_enviado
FROM clientes AS c
JOIN enviados_por_cliente AS e
ON e.cliente_id = c.id
ORDER BY e.total_enviado DESC, c.nombre;| nombre | total_enviado |
|---|---|
| Sara Gómez | 1112.00 |
| Marta Ruiz | 215.90 |
| Lucía Vega | 148.90 |
| Ana Torres | 128.40 |
| Elena Rey | 98.00 |
| Noa Vidal | 61.50 |
| Iván Mora | 54.00 |
Las dos consultas devuelven las mismas siete filas en la base educativa. La diferencia está en cómo se expresa el resultado intermedio: como tabla derivada dentro de JOIN o como CTE con nombre al inicio.
Cuándo elegir una subconsulta
- El paso es corto y solo importa en un punto. Mantenerlo junto al
JOIN, filtro o expresión puede evitar saltos visuales. - Necesitas una expresión escalar. Por ejemplo,
precio > (SELECT AVG(precio) FROM productos)expresa de forma directa una comparación contra un único valor. - La pregunta es de existencia. Una subconsulta correlacionada con
EXISTSsuele comunicar mejor “¿existe una fila relacionada?”.
Una subconsulta no es peor por estar anidada. El problema aparece cuando acumulas varios niveles, repites la misma lógica o el lector necesita reconstruir demasiadas dependencias a la vez.
Cuándo elegir una CTE
- Quieres nombrar una etapa.
enviados_por_clienteexplica qué contiene el resultado antes de que la consulta principal lo use. - La sentencia tiene varias etapas. Varias CTE separadas por comas pueden convertir una consulta extensa en una secuencia legible.
- Necesitas referirte al mismo resultado con nombre más de una vez. La CTE ofrece ese nombre dentro del alcance de la sentencia, aunque la forma de ejecución depende del motor.
- Necesitas recursión. Las jerarquías o recorridos recursivos requieren una CTE recursiva en los motores que la soportan.
Una CTE existe solo durante la sentencia que la consume. No reemplaza una vista, una tabla temporal ni una tabla permanente cuando necesitas conservar o reutilizar resultados entre sentencias distintas.
No elijas por el mito de “CTE más rápida”
No existe una regla fiable que diga que una CTE sea siempre más rápida que una subconsulta, ni al revés. Los optimizadores pueden integrar, materializar o volver a ejecutar resultados intermedios según el motor, la versión y la forma de la consulta.
- PostgreSQL: una CTE no recursiva y sin efectos laterales puede integrarse en la consulta principal en ciertos casos; también ofrece controles
MATERIALIZEDyNOT MATERIALIZED. - MySQL 8.4: documenta que CTE y tablas derivadas pueden ser construcciones intercambiables en casos sencillos y que la optimización puede implicar fusión o materialización.
- SQLite: describe las CTE ordinarias como una forma de factorizar subconsultas y deja al planificador decidir estrategias salvo indicaciones específicas de materialización.
- SQL Server: una CTE define un resultado con nombre dentro de una sola sentencia y su documentación advierte que las referencias externas no implican un resultado materializado persistente.
Si el rendimiento importa, compara planes y tiempos con datos representativos en tu motor. La legibilidad es una razón válida para elegir una forma; una supuesta ventaja automática de velocidad no lo es.
Errores frecuentes al decidir
Convertir cualquier subconsulta en CTE sin mejorar nada
Si una subconsulta de dos líneas es clara y solo se usa una vez, moverla arriba puede añadir distancia sin aportar estructura. El objetivo es reducir carga cognitiva, no aumentar el número de nombres.
Creer que una CTE guarda datos para la consulta siguiente
El nombre de la CTE solo tiene alcance dentro de la sentencia asociada. Si terminas la sentencia y ejecutas otro SELECT, ese nombre ya no existe.
Confundir claridad lógica con plan físico
Que la consulta esté escrita “por etapas” no obliga al motor a ejecutarla literalmente en ese orden. La sintaxis ayuda a razonar; el optimizador decide el plan físico.
Comprueba lo aprendido
Reescribe una tabla derivada como CTE
Parte de una consulta que resume los pedidos por estado dentro de FROM. Reescribe ese resumen como una CTE llamada resumen_estado y devuelve estado y importe ordenados de mayor a menor.
Criterio de éxito: el resultado mantiene una fila por estado y la CTE se consume en la misma sentencia.
WITH resumen_estado AS (...).GROUP BY estado y SUM(total).FROM resumen_estado.Solución razonada
WITH resumen_estado AS (
SELECT estado,
ROUND(SUM(total), 2) AS importe
FROM pedidos
GROUP BY estado
)
SELECT estado, importe
FROM resumen_estado
ORDER BY importe DESC;La CTE encapsula la agregación y la consulta exterior se ocupa de presentar y ordenar el resultado. Podrías escribir la misma lógica como tabla derivada; aquí el nombre resumen_estado hace explícita la etapa.
Qué debes recordar
- Una subconsulta coloca un
SELECTdentro de otra parte de la sentencia. - Una CTE asigna un nombre a un resultado auxiliar mediante
WITHy existe solo durante esa sentencia. - Para pasos cortos y locales, una subconsulta puede ser la forma más directa.
- Para varias etapas o nombres intermedios claros, una CTE puede mejorar la lectura.
- Subconsultas escalares y correlacionadas no deben convertirse mecánicamente en CTE.
- No asumas que CTE o subconsulta gana en rendimiento: revisa el plan en el motor real.