Subconsultas y CTEs

Diagrama de SQL: tablas unidas con JOIN, filtrado SELECT y agregacion GROUP BY. — SQL: consultas, joins, agregacion y optimizacion de consultas.

Mapa conceptual

SQL
- Básico
  - SELECT
  - WHERE
  - ORDER BY
- JOINs
  - INNER
  - LEFT
  - FULL
- Agregación
  - GROUP BY
  - HAVING
- Avanzado
  - Subconsultas
  - CTEs
  - Ventana
- Rendimiento
  - EXPLAIN
  - Índices

Subconsulta escalar

Una subconsulta escalar devuelve un único valor que se usa donde iría un número, por ejemplo en WHERE precio mayor que el promedio de la tabla. El promedio se calcula en una consulta interna y alimenta la externa. Es la forma más directa de comparar filas contra una estadística global.

Subconsulta de lista

Devuelve una columna de valores para alimentar un IN: productos que pertenecen a cierta categoría, o clientes con al menos una devolución. El IN filtra por pertenencia a ese conjunto calculado.

Subconsulta correlacionada

Es una subconsulta interna que referencia valores de la consulta externa y se re-evalúa por cada fila candidata. Potente para comprobaciones tipo «existe un pedido más reciente», pero costosa porque su trabajo se multiplica por el número de filas externas.

Existir frente a contar

Para preguntar si hay al menos una coincidencia se usa EXISTS, que corta en cuanto encuentra una, en lugar de contar todas. Suele ser más eficiente que un IN sobre una subconsulta grande y maneja bien los NULL, porque no compara valores sino presencia de filas.

La cláusula WITH (CTE)

WITH introduce una expresión de tabla común (CTE): con nombre un paso intermedio y luego lo usas como si fuera una tabla temporal. Las CTE convierten una maraña de subconsultas anidadas en una secuencia legible de etapas con nombre.

Por qué preferir CTE

Legibilidad: cada paso tiene nombre y propósito explícito, se lee de arriba abajo.
Reutilización: la misma CTE se puede referenciar varias veces dentro de la consulta.
Depuración: puedes aislar una CTE y probarla por separado.
Composición: varias CTE encadenadas modelan un flujo paso a paso.

CTE recursiva

Una CTE puede referirse a sí misma para recorrer jerarquías o generar secuencias: la estructura de árbol de una organización o una cadena de dependencias. Consta de un caso base y una parte recursiva unida por UNION ALL. Es la herramienta natural para datos anidados.

Anatomía de una CTE

WITH seguido de un nombre y, entre paréntesis, la consulta que la produce.
Una o más CTE separadas por comas, cada una pudiendo usar las anteriores.
La consulta principal que consume las CTE como tablas.

Del subselect a la CTE

Toda subconsulta se puede reescribir como CTE y viceversa, en esencia. La diferencia es de claridad: anidar cuatro subconsultas crea un barril ilegible; cuatro CTE nombradas cuentan una historia. En producción, la versión legible gana casi siempre.

Rendimiento comparado

Algunos motores materializan la CTE una sola vez y la reutilizan; otros la expanden como una subconsulta en cada referencia. El comportamiento depende del sistema, así que conviene revisar el plan de ejecución cuando la consulta es grande y crítica.

Cada mecanismo de composición resuelve una forma distinta de anidar lógica.
Estructura	Devolución	Buen uso
Subconsulta escalar	un valor	comparar filas contra un resumen global
Subconsulta de lista	una columna	alimentar un IN de pertenencia
Correlacionada con EXISTS	verdadero o falso	comprobar presencia fila a fila
CTE con WITH	una tabla intermedia	descomponer un análisis en pasos nombrados

¿Qué ventaja principal aporta una CTE frente a subconsultas anidadas?

Nombres que documentan

Un buen nombre de CTE describe su contenido: ventas_mensuales, clientes_vip, ultimo_pedido. La consulta final se lee entonces casi como lenguaje natural y se vuelve autoexplicativa, un activo para quien la herede.

Cuándo NO conviene

Para un filtro puntual que solo se usa una vez, una subconsulta en línea puede ser más clara que abrir una CTE. El criterio es la reaparición: si el paso intermedio se usa una sola vez y es trivial, mantenlo dentro; si se repite o complica, nómbralo.

Errores frecuentes

Re-evaluar una subconsulta correlacionada sobre millones de filas sin notar el coste.
Olvidar el UNION ALL adecuado en una CTE recursiva y provocar un bucle infinito.
Referenciar una CTE antes de que esté definida en la lista de WITH.

Una CTE recursiva es apropiada para recorrer una jerarquía de padre e hijo.

Plan mental de composición

Ante un problema complejo, identifica primero los resultados intermedios y ponles nombre. Cada uno se vuelve una CTE. Al final queda un guión: preparar, calcular, combinar, presentar. Ese enfoque por capas es transferible a cualquier motor.

Composición de consultas

Paso intermedio con nombre

CTE (WITH)

Devuelve un solo valor

Subconsulta escalar

Se re-evalúa por cada fila externa

Subconsulta correlacionada

Corta al primer hallazgo

EXISTS

Recorre jerarquías

CTE recursiva

Toca una tarjeta para ver la respuesta.

Ordena los pasos para atacar un análisis complejo con CTE:

Arrastra cada ficha a su categoría (o tócala y luego toca la categoría). También puedes usar el teclado.

Use The Index, LukeRendimiento de SQL, subconsultas y planes de ejecución.

Video complementario

Recurso audiovisual para reforzar los conceptos.

Reescribe como CTE una consulta que lista clientes cuyo gasto supera el gasto medio, y explica qué paso intermedio aíslas y por qué mejora la legibilidad frente a la subconsulta anidada.

Tu texto se guarda sólo en este dispositivo.

SQLZooEjercicios interactivos de SQL en línea.

Subconsultas y CTEs

Herramientas de la lección

Sobre este contenido

Ir a