Todas las subconsultas de 07-01 tenían algo en común: se calculaban una vez y valían lo mismo para todas las filas. Por eso ninguna pudo responder a la pregunta que quedó abierta al final: ¿qué productos superan la media de su categoría? Ese umbral no es uno, son seis —6,18 € para Alimentación, 11,54 € para Cosmética natural, 8,90 € para Bebidas…— y cada fila necesita el suyo.

Una subconsulta correlacionada hace exactamente eso: mira hacia fuera, hacia la fila que la consulta externa está examinando en ese instante, y se recalcula para cada una. Cambia el modelo mental (deja de ser una constante y pasa a ser un bucle), cambia el coste y se abre una familia entera de preguntas: el último pedido de cada cliente, el importe de su pedido más caro, la reseña más reciente de cada producto.

1. Cómo se reconoce una correlacionada
2. El modelo mental: una ejecución por fila
3. El caso canónico: cada producto frente a la media de su categoría
4. La variante: el más caro de su categoría, y la autocorrelación
5. Cuatro casos más de TiendaVerde
6. El alcance de los alias: quién ve a quién
7. El coste: N ejecuciones y qué hace el planificador
8. Cuándo usarla y cuándo delata que falta otra cosa
9. Errores Comunes y Consejos
10. Ejercicios
11. Conclusión

La señal es una sola: dentro de la subconsulta aparece un alias que pertenece a la consulta externa.

Copia esa segunda consulta en psql y ejecútala sola:

Ese error es el diagnóstico, no un problema: te confirma que la subconsulta depende de su entorno y que solo tiene sentido dentro de la consulta que la contiene. Es la prueba práctica de 07-01, ahora vista desde el otro lado.

Amplía el diagrama del orden lógico que vienes construyendo desde el módulo 2. La novedad está dentro del paso 2: por cada fila que llega al WHERE, la subconsulta correlacionada se ejecuta entera y devuelve su valor.

Conceptualmente es un bucle anidado: la consulta externa recorre sus filas y, en cada iteración, lanza la consulta interna. Si en el SELECT pones tres subconsultas correlacionadas y la tabla externa tiene 15 filas, son 45 ejecuciones.

Una traza de las primeras filas de productos, con la subconsulta AVG(precio) de su categoría:

Fíjate en las cuatro primeras filas: el mismo cálculo repetido cuatro veces. Ese desperdicio aparente es el motivo de la sección 7, y también la razón de que los planificadores modernos reescriban muchas correlacionadas.

8 productos de 20, y la comparación con 07-01 es muy instructiva: con la media global (9,035 €) salían 9 productos. No son 9 ni un subconjunto de ellos:

El desodorante es barato en términos absolutos pero caro dentro de su categoría; las bolsas son justo lo contrario. Y el caso de la espirulina es el más divertido: es el único producto de Complementos, así que la media de su categoría es su propio precio, y 16.40 > 16.40 es falso. Un producto solo en su categoría nunca puede superar la media de su categoría.

Dos detalles de escritura que conviene fijar:

• La expresión está repetida en el SELECT y en el WHERE, exactamente como pasaba con HAVING en 04-06. Es obligatorio (los alias del SELECT no existen en el WHERE) y es feo. La solución limpia son las CTE de 10-02.
• Los alias p y p2 son imprescindibles. Dentro y fuera es la misma tabla productos, y sin alias distintos WHERE categoria_id = categoria_id sería una tautología: la subconsulta ignoraría la categoría, devolvería la media global y la consulta dejaría de estar correlacionada sin dar ningún error. Volveremos a ello en el ejercicio 3.

Cambiando AVG por MAX y > por =, la misma estructura responde a otra pregunta clásica:

6 filas, una por categoría. Es el patrón greatest-n-per-group, seguramente el más pedido de todo el SQL analítico.

Aquí la tabla externa y la interna son la misma, y eso enlaza directamente con el self join de 03-06: igual que allí unías empleados con empleados para sacar el jefe de cada uno, aquí comparas productos con productos. La diferencia es que el self join produce parejas de filas y la autocorrelación produce un valor calculado para cada fila. Cuando lo que quieres es un agregado por grupo, la correlacionada suele leerse mejor.

Y una advertencia sobre el empate: si dos productos de la misma categoría compartieran el precio máximo, saldrían los dos, porque los dos cumplen la igualdad. Casi siempre es lo que quieres. Si necesitaras exactamente uno por categoría, o el segundo, o un ranking completo, la herramienta correcta ya no es esta: son las funciones de ventana (ROW_NUMBER(), RANK()) de 10-03.

Una subconsulta correlacionada puede ir también en el SELECT, como columna calculada. Esta consulta responde a tres preguntas de golpe:

Los 15 clientes, incluidos los tres que no han comprado nunca. Eso es una diferencia enorme respecto a un INNER JOIN, que habría devuelto 12 filas: una subconsulta en el SELECT no elimina filas de la consulta externa; devuelve un valor o NULL, pero la fila sigue ahí. Se comporta como un LEFT JOIN sin serlo.

Y observa la asimetría de las tres últimas filas: pedidos vale 0 mientras que las otras dos valen NULL. No es una incoherencia, es 04-04: COUNT sobre un conjunto vacío devuelve 0; MAX y SUM, NULL. Si esa columna va a alimentar un cálculo, envuélvela en COALESCE (06-04).

Cuarto caso: la reseña más reciente de cada producto. Aquí la correlación filtra por producto y LIMIT 1 recorta:

9 productos, los únicos con alguna reseña; los otros 11 los excluye el EXISTS (lección siguiente). Y aquí asoma un defecto real de este patrón: hay dos subconsultas casi idénticas para sacar dos columnas de la misma fila, o sea el doble de trabajo. Eso se resuelve con LATERAL (07-04) o con funciones de ventana (10-03); con una sola columna la escritura de arriba es perfectamente razonable.

La regla es asimétrica y hay que memorizarla:

La subconsulta ve los alias de la consulta externa. La consulta externa NO ve los alias de la subconsulta.

La visibilidad va de dentro hacia fuera, nunca al revés. Piensa en cada subconsulta como una función que recibe la fila externa como parámetro: puede leer lo que le pasan, pero lo que ocurre en su interior no se exporta. Si necesitas una columna de la tabla interna en el resultado, la respuesta no es una subconsulta: es un JOIN (07-05).

Dos consecuencias prácticas:

• Cuando la tabla de dentro y la de fuera son la misma, los alias son obligatorios. productos AS p fuera, productos AS p2 dentro. Sin ellos, WHERE categoria_id = categoria_id se resuelve dentro de la subconsulta y siempre es cierto.
• Si un nombre de columna existe en las dos tablas y no lo cualificas, gana la de dentro. Es la regla de resolución de nombres de SQL: primero el ámbito más cercano, luego los exteriores. Es una forma sutilísima de escribir una consulta que "funciona" y responde a otra pregunta. Cualifica siempre todas las columnas dentro de una correlacionada.

Conceptualmente, una correlacionada es un bucle anidado: N filas externas × 1 ejecución interna. Con productos son 20 ejecuciones; con una tabla de dos millones de filas, dos millones. Si además la subconsulta hace un JOIN, se multiplica el trabajo.

Conceptualmente. Porque PostgreSQL no la ejecuta necesariamente así. El planificador reescribe muchas correlacionadas en formas equivalentes y mucho más baratas:

La última fila es la que importa: las correlacionadas en el SELECT son las que más a menudo se quedan como bucle. Con 15 clientes da igual; con 15 millones, una consulta con tres subconsultas en el SELECT puede tardar minutos donde un LEFT JOIN con GROUP BY tarda segundos.

Comprobarlo de verdad —ver el plan, medir el tiempo, saber si hubo semi-join o bucle— requiere EXPLAIN ANALYZE, y eso es la lección 08-05. Hasta entonces, quédate con la intuición y con esta regla: no reescribas por rendimiento sin medir, pero desconfía de las correlacionadas en el SELECT sobre tablas grandes.

Las dos señales de alarma son claras. Si repites la misma correlación tres o cuatro veces en el SELECT, estás recorriendo la misma tabla tres o cuatro veces para agrupar por la misma clave: eso es un GROUP BY. Y si aparece la palabra "ranking", "posición", "el segundo" o "acumulado", ninguna subconsulta lo hará con elegancia; eso son funciones de ventana.

Devuelve exactamente las mismas 8 filas de la sección 3. La diferencia es que la media de cada categoría se calcula una sola vez —seis medias, en una pasada— en lugar de veinte veces. Además la expresión deja de estar duplicada: se nombra m.precio_medio y se usa dos veces. Esa subconsulta en el FROM se llama tabla derivada y es el contenido de 07-04.

Devuelve las mismas 15 filas que las dos primeras columnas de la sección 5, con los mismos 0 y NULL. Una sola pasada por pedidos en lugar de 30 subconsultas. Cuando las columnas correlacionadas empiezan a acumularse, esta es la reescritura que toca. La comparación honesta de ambas formas —legibilidad, filas, rendimiento— es 07-05.

• Olvidar los alias cuando la tabla es la misma dentro y fuera. WHERE categoria_id = categoria_id es una tautología: la correlación desaparece, la subconsulta calcula la media global y no hay ningún error.
• No cualificar las columnas dentro de la subconsulta. Si el nombre existe en las dos tablas, gana el ámbito interno y la consulta responde a otra pregunta, en silencio.
• Intentar usar un alias interno en la consulta externa. missing FROM-clause entry for table "p2". La visibilidad va solo de dentro hacia fuera.
• Ejecutar la subconsulta sola para depurarla. No se puede: dará ese mismo error. Para probarla, sustituye a mano el valor externo (WHERE p2.categoria_id = 1) y comprueba que devuelve lo que esperas.
• Esperar que una subconsulta en el SELECT filtre filas. No filtra: devuelve un valor o NULL. Los 15 clientes siguen apareciendo.
• Confundir el 0 de COUNT con el NULL de MAX o SUM. Un cliente sin pedidos tiene 0 pedidos y NULL en cualquier otra columna agregada.
• Escribir una escalar correlacionada que devuelve varias filas. more than one row returned by a subquery used as an expression: la correlación acota, pero no garantiza unicidad. Añade un agregado o un ORDER BY ... LIMIT 1.
• Consejo: escribe primero la subconsulta con un valor fijo. Comprueba que AVG(precio) WHERE categoria_id = 1 da 6,18 y después sustituye el 1 por p.categoria_id. Depurar las dos capas a la vez es innecesariamente difícil.
• Consejo: si repites la misma correlación en dos columnas, pasa a LATERAL o a GROUP BY. Dos subconsultas idénticas para sacar dos campos de la misma fila son el doble de trabajo por nada.
• Consejo: cuenta cuántas veces se ejecutará. Filas de la tabla externa × subconsultas correlacionadas. Si el número te incomoda, plantéate la reescritura de la sección 8 antes de que lo haga producción.

Calidad quiere saber qué productos tienen una puntuación media superior a la media global de todas las reseñas. Escribe la consulta con una subconsulta no correlacionada para la media global y muestra id, producto, número de reseñas y media (dos decimales), ordenado por media descendente y id.

Después responde: ¿es correlacionada alguna parte de tu consulta? ¿Por qué la respuesta cambiaría si la pregunta fuera "media superior a la media de su categoría"?

Ventas necesita, para cada cliente que haya comprado, el importe de su último pedido (el de fecha más reciente), junto a su nombre y esa fecha. Usa subconsultas correlacionadas. Después indica: ¿qué pasaría si un cliente tuviera dos pedidos el mismo día, y cómo lo arreglarías?

Un compañero ha escrito esto para sacar "los productos por encima de la media de su categoría" y se extraña de que le devuelva las mismas 9 filas que la consulta con la media global de 07-01, en vez de 8:

1. ¿Por qué devuelve 9 filas y no 8?
2. Corrígela.
3. ¿Cuál habría sido el resultado si en lugar de categoria_id = categoria_id hubiera escrito categoria_id = proveedor_id? Explica el mecanismo, no hace falta el número exacto.

Solución 1

4 productos de los 9 reseñados superan la media global, que es 4,0833… (49 puntos entre 12 reseñas). Fuera quedan el detergente y el cepillo (4,00), el tomate y las bolsas (3,00) y la kombucha (2,00).

No hay correlación en ninguna parte: la subconsulta SELECT AVG(puntuacion) FROM resenas no menciona nada de fuera, se ejecuta una vez y da un número. Es el mismo patrón del ticket medio de 07-01. Si la pregunta fuera "superior a la media de su categoría", la subconsulta tendría que filtrar por la categoría del producto del grupo actual —WHERE p2.categoria_id = p.categoria_id— y pasaría a ser correlacionada, evaluándose una vez por grupo.

Solución 2

12 filas. Hay tres niveles de anidamiento y dos correlaciones distintas contra c.id. Compara el resultado con el de la sección 5: el último pedido de Lucía vale 33,40 € mientras que su pedido más caro vale 42,10 €; el de Camille son 22,60 € frente a 48,27 €. Son preguntas distintas y conviene no confundirlas en un informe.

Si un cliente tuviera dos pedidos el mismo día, la subconsulta del importe sumaría los dos y devolvería un total inflado (no daría error, porque el GROUP BY no está y SUM agrega todo lo que le llega). El arreglo es desempatar por clave primaria: en lugar de filtrar por fecha_pedido = MAX(...), filtrar por pe.id = (SELECT pe2.id FROM pedidos AS pe2 WHERE pe2.cliente_id = c.id ORDER BY pe2.fecha_pedido DESC, pe2.id DESC LIMIT 1). Cualquier "el último" basado solo en una fecha sin hora es frágil; añade siempre un desempate determinista.

Solución 3

1. Porque categoria_id = categoria_id se resuelve entero dentro de la subconsulta: no hay ningún alias que distinga la tabla de fuera de la de dentro, así que las dos apariciones se refieren a la productos interna. La condición es "una columna igual a sí misma", cierta para las 20 filas, y la subconsulta acaba devolviendo la media global, 9,035 € — de ahí las 9 filas. La consulta no está correlacionada en absoluto, aunque lo parezca, y ese es el fallo: el filtro por categoría no se aplica nunca. Es un error silencioso de manual.

2. La corrección es la de la sección 3: alias distintos dentro y fuera.

8 filas, las de la sección 3.

3. categoria_id = proveedor_id tampoco correlaciona: compara dos columnas de la misma tabla interna. La subconsulta devolvería la media de los productos cuya categoría coincide numéricamente con su proveedor —una condición sin ningún sentido de negocio pero perfectamente válida—, y ese único número se aplicaría a las 20 filas. Es el peor de los casos: una consulta que no da error, parece correlacionada, y responde a una pregunta que nadie ha hecho. De ahí la regla de cualificar siempre las columnas.

La correlación cambia la naturaleza de una subconsulta:

• Una subconsulta correlacionada referencia un alias de la consulta externa; no se puede ejecutar sola (missing FROM-clause entry) y se evalúa una vez por cada fila candidata, como un bucle anidado.
• El caso canónico es comparar cada fila con un agregado de su propio grupo: 8 productos superan la media de su categoría, frente a los 9 que superaban la media global — y no son los mismos, porque el desodorante es caro para Higiene personal y las bolsas son baratas para Hogar sostenible.
• La variante con = MAX(...) da el más caro de cada categoría (6 filas, una por categoría), el patrón greatest-n-per-group, hermano de la autocorrelación y del self join de 03-06.
• En el SELECT funciona como columna calculada y no elimina filas: los 15 clientes siguen ahí, con 0 en COUNT y NULL en MAX y SUM.
• La visibilidad es asimétrica: la subconsulta ve los alias de fuera, la externa no ve los de dentro. Cuando la tabla es la misma, los alias distintos son obligatorios: sin ellos la correlación se evapora en silencio.
• Conceptualmente cuesta N ejecuciones. PostgreSQL reescribe muchas como semi-joins o anti-joins, pero las correlacionadas en el SELECT suelen quedarse como bucle; medirlo de verdad es 08-05.
• Y sabes reconocer cuándo no es la herramienta: varias correlaciones repetidas piden un LEFT JOIN con GROUP BY, y cualquier ranking pide funciones de ventana (10-03).

En la lección siguiente, EXISTS y NOT EXISTS, verás la forma más pura de subconsulta correlacionada: una que no devuelve ningún valor, solo responde sí o no. Con ella escribirás por fin "clientes que no han comprado nunca" sin LEFT JOIN, descubrirás por qué NOT EXISTS es seguro con nulos y NOT IN no, y resolverás el problema clásico de la división relacional: qué clientes han comprado de todas las categorías.