Una consulta es una tabla que puedes reutilizar

La idea

Toda consulta devuelve una tabla. Una tabla de una fila y una columna es un número. Una tabla de una columna es una lista de valores. Una tabla con varias columnas es... una tabla. Así que en cualquier lugar donde SQL espera un número, una lista o una tabla, puedes poner una consulta entre paréntesis.

Eso es una subconsulta: una consulta usada como parte de otra. La consulta interna se ejecuta, se convierte en un valor, una lista o una tabla, y la consulta externa sigue como si hubieras escrito ese resultado a mano.

Por qué existe

Muchas preguntas tienen un número que todavía no conoces. "Productos con precio superior al promedio" necesita el promedio. "Los productos premium de cada categoría" necesita un promedio por categoría. "El valor de vida de un cliente" necesita los ingresos por pedido antes de sumar los pedidos por cliente.

Sin subconsultas ejecutarías la primera consulta, copiarías la respuesta y la pegarías en la segunda. Eso se rompe el día que cambian los datos, y no puedes pasarle el resultado a nadie más. Una subconsulta mantiene ambas preguntas en una sola instrucción, así que la respuesta se actualiza sola y la lógica queda escrita.

Cómo funciona por dentro

Tres lugares donde puede ir una subconsulta

Como un solo valor (subconsulta escalar). Debe devolver exactamente una fila y una columna. La usas en WHERE unit_price > (SELECT AVG(unit_price) FROM products) o en la lista del SELECT. Si devuelve dos filas, obtienes un error, lo cual es mejor que una respuesta equivocada en silencio.

Como una lista o una prueba. IN (SELECT …) comprueba si un valor está en la lista devuelta. EXISTS (SELECT …) comprueba si la consulta interna devuelve alguna fila. Las columnas que selecciona no importan, por eso la gente escribe SELECT 1.

Como una tabla. Una subconsulta en FROM se trata como una tabla con alias. Así agregas primero y unes después: FROM (SELECT order_id, SUM(…) AS total FROM order_items GROUP BY order_id) t.

Correlacionada o no

Una subconsulta que no menciona a la consulta externa se evalúa una vez, como muestra el primer diagrama. Una subconsulta correlacionada hace referencia a la fila externa (WHERE o.customer_id = c.customer_id). Conceptualmente se evalúa otra vez por cada fila externa, y cada vez usa los valores de esa fila. "Los productos premium de cada categoría" es correlacionada: el promedio con el que compara depende de la categoría del producto.

EXISTS es un sí o no por fila, así que nunca multiplica filas. Un JOIN con returns produciría una fila por devolución. Si solo necesitas saber si existe una devolución, EXISTS es más seguro y a menudo más claro.

CTE: ponerle nombre a los pasos

Una expresión de tabla común (CTE) es una subconsulta con nombre, escrita al principio con WITH. Las matemáticas no cambian. Lo que cambia es que puedes leer la consulta de arriba abajo, como una receta, y probar cada paso por separado.

El hábito más útil que esto te da es agregar antes de unir. El costo de envío vive en orders, las líneas de pedido viven en order_items, y unirlas directamente repite cada costo una vez por línea. Si sumas primero las líneas en una fila por pedido y luego unes, el nivel de detalle se mantiene en una fila por pedido. Un CTE hace visible ese paso en lugar de esconderlo dentro de un join largo.

Dos cosas más. Un CTE existe solo para la instrucción a la que pertenece; no se guarda. Y un CTE puede usar los CTE escritos antes que él, así se construye una cadena de pasos. Los CTE recursivos (un CTE que se refiere a sí mismo) generan filas, por ejemplo un calendario con una fila por día, para poder mostrar los días con cero pedidos.

Leer una consulta anidada

Léela de adentro hacia afuera. Busca los paréntesis más internos, ejecuta esa parte sola y mira qué devuelve: un número, una lista, una tabla. Luego reemplázala mentalmente por ese resultado y lee la siguiente capa hacia afuera. Así también se depura: cuando una consulta larga da una respuesta rara, pega cada pieza interna en su propia pestaña y compara su número de filas con algo en lo que confíes. La mayoría de los errores en consultas anidadas son un problema de nivel de detalle escondido en una capa, como un paso que devuelve una fila por línea de pedido cuando el siguiente suponía una fila por pedido.

Cuál elegir

Para un número, usa una subconsulta escalar. Para "existe una fila relacionada", usa EXISTS (y NOT EXISTS para "no existe"). Para todo lo que tenga dos o más pasos, o donde querrás revisar un resultado intermedio, usa un CTE.

Errores comunes

"Una subconsulta es más lenta, así que siempre debo usar un JOIN"

Las bases de datos modernas reescriben muchas subconsultas como joins por su cuenta, y la elección tiene más que ver con lo que se lee mejor y con responder la pregunta correcta. EXISTS suele ser mejor que un join porque no puede crear filas duplicadas. Optimiza cuando la consulta ya sea correcta y hayas medido que es lenta.

"Un CTE guarda su resultado como una tabla"

Un CTE es un nombre para una consulta, válido para una sola instrucción. No se guarda nada, y la siguiente instrucción no lo ve. Si necesitas que el resultado permanezca, creas una tabla o una vista, que es otra herramienta.

"NOT IN y NOT EXISTS significan lo mismo"

Coinciden en todas las filas sin NULL y se separan en cuanto la lista interna contiene un NULL: x NOT IN (1, NULL) nunca es verdadero, así que la consulta no devuelve nada. NOT EXISTS no tiene ese problema. Cuando quieras "filas sin coincidencia", usa NOT EXISTS o un LEFT JOIN … IS NULL.