Subconsultas correlacionadas

1. Qué es

Una subconsulta correlacionada menciona una columna de la consulta de fuera. Eso lo cambia todo:

-- Normal: la media de TODOS los productos
(SELECT avg(unit_price) FROM products)

-- Correlacionada: la media de LA CATEGORÍA DE ESTA FILA
(SELECT avg(p2.unit_price) FROM products p2 WHERE p2.category_id = p.category_id)
                                                                   ↑ viene de fuera

La segunda no se puede copiar y pegar en una ventana nueva: p.category_id no existe fuera de su consulta. Esa es la prueba de que está correlacionada.

Y para lo que sirve: comparar cada fila contra su propio grupo. "¿Está este producto por encima de la media de su categoría?" no se puede responder con un agregado global.

2. Ejemplos sobre Northwind

La media del grupo, en cada fila:

SELECT p.product_name, p.unit_price, p.category_id,
       (SELECT round(avg(p2.unit_price), 2)
        FROM products p2
        WHERE p2.category_id = p.category_id) AS media_categoria
FROM products p
ORDER BY p.product_id LIMIT 5;

         product_name         | unit_price | category_id | media_categoria
------------------------------+------------+-------------+-----------------
 Chai                         |      18.00 |           1 |           37.98
 Chang                        |      19.00 |           1 |           37.98
 Aniseed Syrup                |      10.00 |           2 |           22.85
 Chef Anton's Cajun Seasoning |      22.00 |           2 |           22.85
 Chef Anton's Gumbo Mix       |      21.35 |           2 |           22.85

El valor cambia con la categoría. Compáralo con el tema anterior, donde la media global salía igual en las 77 filas.

En el WHERE, y la comparación que enseña la diferencia:

-- Por encima de la media GLOBAL
SELECT count(*) FROM products p
WHERE p.unit_price > (SELECT avg(unit_price) FROM products);                              -- 25

-- Por encima de la media DE SU CATEGORÍA
SELECT count(*) FROM products p
WHERE p.unit_price > (SELECT avg(p2.unit_price) FROM products p2
                      WHERE p2.category_id = p.category_id);                              -- 27

25 contra 27. Y no es solo que cambie el número: cambian los productos. Un queso de 30 € está por encima de la media global (28,83) pero por debajo de la de lácteos. La segunda pregunta es casi siempre la que quiere el negocio.

Varias correlacionadas a la vez:

SELECT c.company_name,
       (SELECT count(*)         FROM orders o WHERE o.customer_id = c.customer_id) AS pedidos,
       (SELECT max(o.order_date) FROM orders o WHERE o.customer_id = c.customer_id) AS ultima
FROM customers c
ORDER BY pedidos DESC LIMIT 4;

    company_name    | pedidos |   ultima
--------------------+---------+------------
 Save-a-lot Markets |      31 | 1998-05-01
 Ernst Handel       |      30 | 1998-05-05
 QUICK-Stop         |      28 | 1998-04-14
 Folk och fä HB     |      19 | 1998-04-27

Los 91 clientes salen, incluidos los dos que no han comprado (con pedidos = 0 y ultima en NULL). Es lo mismo que un LEFT JOIN + GROUP BY, escrito de otra forma — y aquí no hay riesgo de fan-out (joins-multiples-y-granularidad), porque cada subconsulta agrega por su cuenta.

El máximo por grupo:

SELECT p.category_id, p.product_name, p.unit_price
FROM products p
WHERE p.unit_price = (SELECT max(p2.unit_price) FROM products p2
                      WHERE p2.category_id = p.category_id)
ORDER BY p.category_id;

 category_id |      product_name       | unit_price
-------------+-------------------------+------------
           1 | Côte de Blaye           |     263.50
           2 | Vegie-spread            |      43.90
           3 | Sir Rodney's Marmalade  |      81.00
           4 | Raclette Courdavault    |      55.00
           5 | Gnocchi di nonna Alice  |      38.00
           6 | Thüringer Rostbratwurst |     123.79
           7 | Manjimup Dried Apples   |      53.00
           8 | Carnarvon Tigers        |      62.50

Este es el patrón clásico de "el más X de cada grupo", y es el que resuelven mejor las funciones de ventana (ranking-row-number-rank-ntile) y LATERAL (lateral).

2.1 El detalle de los empates

SELECT count(*) FROM orders o
WHERE o.order_date = (SELECT max(o2.order_date) FROM orders o2
                      WHERE o2.customer_id = o.customer_id);      -- 90
SELECT count(DISTINCT customer_id) FROM orders;                   -- 89

90 filas para 89 clientes. Uno tiene dos pedidos el mismo último día:

SELECT customer_id, count(*) FROM orders o
WHERE o.order_date = (SELECT max(o2.order_date) FROM orders o2 WHERE o2.customer_id = o.customer_id)
GROUP BY 1 HAVING count(*) > 1;

 customer_id | count
-------------+-------
 LACOR       |     2

Es el mismo LACOR del tema distinct-y-duplicados, con sus dos pedidos del 24 de marzo de 1998. Y aquí está lo importante:

Un = (SELECT max(...)) correlacionado devuelve todas las filas empatadas, no una. Si esperabas "una fila por cliente", la consulta te da 90 donde creías 89, y cualquier conteo posterior sale mal por uno.

Si quieres exactamente una por grupo, hay que desempatar — y ahí DISTINCT ON o ROW_NUMBER() lo hacen mejor.

3. El coste: una vez por fila

EXPLAIN ANALYZE lo enseña sin ambigüedad:

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT c.company_name, (SELECT count(*) FROM orders o WHERE o.customer_id = c.customer_id)
FROM customers c;

 Seq Scan on customers c (actual rows=91 loops=1)
   SubPlan 1
     ->  Aggregate (actual rows=1 loops=91)
           ->  Bitmap Heap Scan on orders o (actual rows=9 loops=91)
                 ->  Bitmap Index Scan on idx_orders_customer_id (actual rows=9 loops=91)

loops=91. La subconsulta se ejecutó 91 veces, una por cliente. La versión con LEFT JOIN:

 HashAggregate (actual rows=91 loops=1)
   ->  Hash Right Join (actual rows=832 loops=1)
         ->  Seq Scan on orders o (actual rows=830 loops=1)
         ->  Hash (actual rows=91 loops=1)

Una sola pasada por cada tabla. Con 91 clientes da igual; con 500.000 no.

Eso es el problema N+1: una consulta para la lista y una por cada elemento. Aquí lo agrava que hay dos correlacionadas en el ejemplo anterior — 182 ejecuciones para 91 filas.

La regla no es "no uses correlacionadas". Es: si la correlacionada agrega sobre una tabla grande y la consulta externa devuelve muchas filas, conviértela en un JOIN con una subconsulta ya agregada, o en una función de ventana.

Y dos matices que evitan el dogmatismo:

• El índice importa mucho. Arriba, cada vuelta usa idx_orders_customer_id. Sin índice sobre la columna de correlación, cada vuelta sería un escaneo completo — y ahí sí es catastrófico.
• PostgreSQL no siempre repite el trabajo. EXISTS correlacionado lo reescribe como semi-join en una sola pasada (anti-join-y-semi-join). El SubPlan con loops=N aparece sobre todo con agregaciones en el SELECT.

4. Correlacionadas que sí conviene escribir

EXISTS siempre es correlacionado, y es el mejor caso de todos:

SELECT c.company_name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

Se convierte en un semi-join y para en cuanto encuentra la primera fila. No agrega nada.

Filtrar por un agregado del grupo sin arrastrar un GROUP BY a toda la consulta:

SELECT count(*) FROM customers c
WHERE (SELECT count(*) FROM orders o WHERE o.customer_id = c.customer_id) > 10;   -- 28

28 clientes con más de 10 pedidos. La alternativa con JOIN + GROUP BY + HAVING da lo mismo; esta se lee como la frase.

El "más reciente de cada grupo", cuando no hay ventanas disponibles.

5. Errores comunes

1. Olvidar el alias de la tabla externa. Sin p y p2 distintos, WHERE category_id = category_id compara la columna consigo misma: cierto siempre, y el resultado es la media global disfrazada de media por grupo. No da error.
2. Correlacionada con agregado sobre una tabla grande. N+1.
3. Esperar una fila por grupo con = (SELECT max(...)). Los empates dan más.
4. Subconsulta correlacionada que devuelve varias filas. Mismo error que en el tema anterior.
5. No indexar la columna de correlación. Cada vuelta es un escaneo completo.
6. Repetir la misma correlacionada cinco veces en el SELECT. Cinco recorridos. Es el momento de un LATERAL o un JOIN agregado.
7. Usar una correlacionada donde bastaba una función de ventana. avg(...) OVER (PARTITION BY category_id) hace lo del primer ejemplo en una pasada (partition-by-y-order-by).

6. Diferencias con SQL Server

Sintaxis idéntica y problema de rendimiento idéntico. El N+1 de una correlacionada agregada es igual de caro en los dos motores, y en los dos la solución es la misma: pre-agregar o usar ventanas.

7. Ejercicios

1. Cada producto con el precio máximo de su categoría al lado y la diferencia.
2. Los clientes que han hecho más pedidos que la media de pedidos por cliente.
3. El último pedido de cada empleado: id, fecha y empleado.
4. Los productos que son el más barato de su categoría.
5. Explica por qué esta consulta devuelve la media global en todas las filas en vez de la media por categoría: sql SELECT product_name, category_id, (SELECT avg(unit_price) FROM products WHERE category_id = category_id) AS media FROM products;

8. Pregunta de negocio

Compras quiere saber qué productos están "caros para lo que son": los que superan en más de un 50% el precio medio de su categoría. Quieren el nombre, el precio, la media de la categoría y cuánto se pasan, en porcentaje.