Subconsultas escalares y multivalor

1. Qué es

Una subconsulta es un SELECT dentro de otro SELECT. Se escribe entre paréntesis y el motor la resuelve primero.

Lo que decide cómo puedes usarla es qué forma tiene lo que devuelve:

Este tema son las dos primeras. Y las dos existen para lo mismo: comparar una fila contra algo que hay que calcular antes — la media, el máximo, una lista.

2. Subconsulta escalar

Devuelve exactamente un valor. Se comporta como si escribieras el número a mano.

SELECT product_name, unit_price,
       (SELECT round(avg(unit_price), 2) FROM products) AS media
FROM products
ORDER BY unit_price DESC
LIMIT 3;

      product_name       | unit_price | media
-------------------------+------------+-------
 Côte de Blaye           |     263.50 | 28.83
 Thüringer Rostbratwurst |     123.79 | 28.83
 Mishi Kobe Niku         |      97.00 | 28.83

El mismo valor en todas las filas, porque la subconsulta no depende de la fila: se calcula una vez para toda la consulta. (La que sí depende de la fila es la correlacionada, tema siguiente.)

Y lo que hace útil eso: comparar cada fila contra el agregado, que sin subconsulta es imposible en una sola pasada.

SELECT product_name, unit_price,
       round(unit_price - (SELECT avg(unit_price) FROM products), 2) AS vs_media
FROM products
ORDER BY vs_media DESC LIMIT 3;

      product_name       | unit_price | vs_media
-------------------------+------------+----------
 Côte de Blaye           |     263.50 |   234.67
 Thüringer Rostbratwurst |     123.79 |    94.96
 Mishi Kobe Niku         |      97.00 |    68.17

En el WHERE:

SELECT count(*) FROM products WHERE unit_price > (SELECT avg(unit_price) FROM products);   -- 25

25 productos de 77 están por encima de la media. No 38: la media está arrastrada hacia arriba por los caros (funciones-de-agregacion).

Varias en el mismo SELECT, sin FROM:

SELECT (SELECT count(*) FROM orders)    AS pedidos,
       (SELECT count(*) FROM customers) AS clientes,
       (SELECT count(*) FROM products)  AS productos;

 pedidos | clientes | productos
---------+----------+-----------
     830 |       91 |        77

Una fila de resumen a partir de tres tablas distintas, sin ningún JOIN. Es el patrón de las tarjetas de un cuadro de mando.

2.1 Las dos formas de romperla

Devuelve más de una fila → error:

SELECT product_name FROM products
WHERE unit_price > (SELECT unit_price FROM products WHERE category_id = 1);

ERROR:  more than one row returned by a subquery used as an expression

Este error es una suerte. Aparece cuando los datos crecen: la consulta funcionó en pruebas con una fila y revienta en producción con dos. Si esperas una sola fila, garantízalo con un LIMIT 1 y un ORDER BY que decida cuál, o con una agregación (max, min) que por definición devuelve una.

Devuelve cero filas → NULL, sin error:

SELECT product_name,
       (SELECT max(unit_price) FROM products WHERE category_id = 99) AS inexistente
FROM products LIMIT 2;

 product_name | inexistente
--------------+-------------
 Chai         |
 Chang        |

Este es el peligroso. Una subconsulta vacía no protesta: devuelve NULL, y ese NULL se propaga por toda la expresión (null-y-la-logica-de-tres-valores). Si va en un WHERE, la condición nunca se cumple y la consulta devuelve cero filas.

3. Subconsulta multivalor: IN, ANY, ALL

Devuelve una columna con varias filas. Hace falta un operador que sepa comparar contra un conjunto.

IN con subconsulta:

SELECT count(*) FROM products
WHERE category_id IN (SELECT category_id FROM categories
                      WHERE category_name IN ('Beverages', 'Seafood'));

 count
-------
    24

Fíjate en los dos IN distintos: el de fuera con subconsulta, el de dentro con lista literal. Los dos son válidos y hacen cosas distintas.

ANY contra ALL:

SELECT count(*) FROM products WHERE unit_price > ANY (SELECT unit_price FROM products WHERE category_id = 1);  -- 75
SELECT count(*) FROM products WHERE unit_price > ALL (SELECT unit_price FROM products WHERE category_id = 1);  --  0

75 contra 0.

• > ANY = más caro que el más barato de las bebidas. Casi todos lo son.
• > ALL = más caro que el más caro de las bebidas, que es Côte de Blaye a 263,50 — el producto más caro de toda la base. Nadie lo supera.

En la práctica casi siempre es más claro escribir el agregado: > (SELECT max(unit_price) ...) dice lo mismo que > ALL y se lee sin pensar. ANY y ALL se conocen sobre todo para leer código ajeno.

3.1 La trampa: ALL sobre un conjunto vacío

SELECT count(*) FROM products
WHERE unit_price > ALL (SELECT unit_price FROM products WHERE product_id > 900);

 count
-------
    77

Los 77. No hay ningún producto con id mayor que 900, así que la condición "mayor que todos los de un conjunto vacío" es cierta por vacuidad: no hay ninguno que la incumpla.

Es lógicamente correcto y prácticamente venenoso: un filtro que se supone restrictivo deja pasar la tabla entera el día que la subconsulta se queda sin filas.

3.2 La otra trampa: ALL con NULL

SELECT count(*) FROM products WHERE unit_price > ALL (SELECT unit_price FROM products WHERE category_id = 6);                       -- 1
SELECT count(*) FROM products WHERE unit_price > ALL (SELECT unit_price FROM products WHERE category_id = 6 UNION ALL SELECT NULL); -- 0

1 contra 0. El máximo de la categoría 6 es 123,79, así que solo Côte de Blaye (263,50) lo supera. Añadir un solo NULL al conjunto lo borra: 263.50 > NULL es UNKNOWN, y ALL exige que todas las comparaciones sean ciertas.

Y <> ALL es literalmente NOT IN, con la misma trampa:

SELECT count(*) FROM employees WHERE employee_id <> ALL (SELECT reports_to FROM employees);                        -- 0  ❌
SELECT count(*) FROM employees WHERE employee_id <> ALL (SELECT reports_to FROM employees WHERE reports_to IS NOT NULL);  -- 7  ✅

Los mismos 0 contra 7 del tema anti-join-y-semi-join, con otra sintaxis. La conclusión no cambia: para negar, NOT EXISTS.

4. Cuándo subconsulta y cuándo JOIN

El criterio no es el rendimiento —PostgreSQL reescribe casi todo esto internamente— sino cuál se lee mejor. Un WHERE unit_price > (SELECT avg(unit_price) FROM products) dice exactamente lo que hace; la versión con JOIN contra una subconsulta agregada dice lo mismo con el triple de ruido.

5. Errores comunes

1. Subconsulta escalar que devuelve más de una fila. Error en tiempo de ejecución, a veces solo cuando crecen los datos.
2. Subconsulta escalar vacía. NULL silencioso que anula la expresión o vacía el WHERE.
3. NOT IN / <> ALL sobre una columna con nulos. Cero filas.
4. ALL sobre un conjunto vacío. Cierto para todo.
5. Usar subconsulta cuando necesitas columnas de la otra tabla. Acabas escribiendo cinco subconsultas escalares donde cabía un JOIN.
6. Repetir la misma subconsulta escalar varias veces. Se calcula una vez, así que no es un problema de rendimiento — pero sí de mantenimiento: el día que cambia el criterio hay que tocarla en cinco sitios. Ahí es donde entra un CTE (cte-with).
7. Olvidar los paréntesis. Error de sintaxis, este avisa.

6. Diferencias con SQL Server

Este tema es completamente portable, trampas incluidas. ALL sobre vacío es cierto en los dos, y NOT IN con nulos falla igual en los dos: es el estándar SQL, no una decisión de motor.

7. Ejercicios

1. Los productos que cuestan más que la media de su tabla, con la diferencia respecto a esa media.
2. El pedido con el flete más alto, mostrando id, cliente y flete.
3. Los clientes que están en alguno de los países donde también hay proveedores.
4. Los productos más caros que el más caro de la categoría Seafood.
5. Explica por qué esta consulta devuelve todas las filas y cómo la arreglarías: sql SELECT count(*) FROM orders WHERE freight > ALL (SELECT freight FROM orders WHERE ship_country = 'Narnia');

8. Pregunta de negocio

Dirección quiere una ficha de una línea por producto para la reunión: nombre, precio, cuánto se aleja del precio medio de la empresa, y qué porcentaje de la facturación total representa. Solo los productos que estén por encima de la media.