El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència

HOME

Totes les subconsultes de 07-01 tenien una cosa en comú: es calculaven una vegada i valien el mateix per a totes les files. Per això cap no va poder respondre la pregunta que va quedar oberta al final: quins productes superen la mitjana de la seva categoria? Aquell llindar no és un, en són sis —6,18 € per a Alimentació, 11,54 € per a Cosmètica natural, 8,90 € per a Begudes…— i cada fila necessita el seu.

Una subconsulta correlacionada fa exactament això: mira cap enfora, cap a la fila que la consulta externa està examinant en aquell instant, i es recalcula per a cadascuna. Canvia el model mental (deixa de ser una constant i passa a ser un bucle), canvia el cost i s'obre una família sencera de preguntes: l'última comanda de cada client, l'import de la seva comanda més cara, la ressenya més recent de cada producte.

Contingut

Com es reconeix una correlacionada
El model mental: una execució per fila
El cas canònic: cada producte enfront de la mitjana de la seva categoria
La variant: el més car de la seva categoria, i l'autocorrelació
Quatre casos més de BotigaVerda
L'abast dels àlies: qui veu qui
El cost: N execucions i què fa el planificador
Quan fer-la servir i quan delata que hi falta una altra cosa
Errors habituals i consells
Exercicis
Conclusió

Com es reconeix una correlacionada

El senyal és un de sol: dins de la subconsulta apareix un àlies que pertany a la consulta externa.

-- No correlacionada: tot el que esmenta és al seu propi FROM
SELECT AVG(preu) FROM productes;

-- Correlacionada: p.categoria_id no és al seu FROM, ve de fora
SELECT AVG(preu) FROM productes WHERE categoria_id = p.categoria_id;

Copia aquesta segona consulta a psql i executa-la sola:

ERROR:  missing FROM-clause entry for table "p"
LINE 1: ...T AVG(preu) FROM productes WHERE categoria_id = p.categori...
                                                           ^

Aquest error és el diagnòstic, no cap problema: et confirma que la subconsulta depèn del seu entorn i que només té sentit dins de la consulta que la conté. És la prova pràctica de 07-01, ara vista des de l'altre costat.

	No correlacionada	Correlacionada
Esmenta àlies externs	No	Sí
Executada sola	Funciona	`missing FROM-clause entry`
Avaluacions	1	1 per fila candidata
Es comporta com	Una constant	Una funció de la fila externa

El model mental: una execució per fila

Amplia el diagrama de l'ordre lògic que vas construint des del mòdul 2. La novetat és dins del pas 2: per cada fila que arriba al WHERE, la subconsulta correlacionada s'executa sencera i retorna el seu valor.

flowchart LR
    A["1 · FROM / JOIN"] --> B["2 · WHERE<br/>fila a fila"]
    B --> S{{"per CADA fila:<br/>executar la subconsulta<br/>amb els valors d'aquella fila"}}
    S --> B
    B --> C["3 · GROUP BY"] --> D["4 · HAVING"] --> E["5 · SELECT"] --> F["6 · ORDER BY"] --> G["7 · LIMIT"]

Conceptualment és un bucle imbricat: la consulta externa recorre les seves files i, a cada iteració, llança la consulta interna. Si al SELECT hi poses tres subconsultes correlacionades i la taula externa té 15 files, són 45 execucions.

Una traça de les primeres files de productes, amb la subconsulta AVG(preu) de la seva categoria:

Fila externa	`p.categoria_id`	Subconsulta executada	Retorna	`preu >` ?
1 · Oli d'oliva, 12,50 €	1	`AVG(preu) WHERE categoria_id = 1`	6,18	sí
2 · Arròs, 3,90 €	1	`AVG(preu) WHERE categoria_id = 1`	6,18	no
3 · Mel, 9,75 €	1	`AVG(preu) WHERE categoria_id = 1`	6,18	sí
4 · Pasta d'espelta, 2,80 €	1	`AVG(preu) WHERE categoria_id = 1`	6,18	no
6 · Crema d'àloe, 18,90 €	2	`AVG(preu) WHERE categoria_id = 2`	11,5375	sí

Fixa't en les quatre primeres files: el mateix càlcul repetit quatre vegades. Aquest malbaratament aparent és el motiu de la secció 7, i també la raó que els planificadors moderns reescriguin moltes correlacionades.

El cas canònic: cada producte enfront de la mitjana de la seva categoria

SELECT p.id,
       p.nom AS producte,
       cat.nom AS categoria,
       p.preu,
       ROUND((SELECT AVG(p2.preu)
              FROM productes AS p2
              WHERE p2.categoria_id = p.categoria_id), 2) AS mitjana_categoria
FROM productes  AS p
JOIN categories AS cat ON p.categoria_id = cat.id
WHERE p.preu > (SELECT AVG(p2.preu)
                FROM productes AS p2
                WHERE p2.categoria_id = p.categoria_id)
ORDER BY p.id;

id	producte	categoria	preu	mitjana_categoria
1	Oli d'oliva verge extra 500 ml	Alimentació	12.50	6.18
3	Mel de tarongina crua 500 g	Alimentació	9.75	6.18
6	Crema facial d'àloe vera 50 ml	Cosmètica natural	18.90	11.54
8	Oli corporal d'ametlles 200 ml	Cosmètica natural	14.25	11.54
10	Detergent ecològic concentrat 1 L	Llar sostenible	11.20	10.09
13	Espelmes de cera de soja (pack 2)	Llar sostenible	13.75	10.09
15	Te verd matcha cerimonial 30 g	Begudes	22.00	8.90
19	Desodorant natural en barra 50 g	Higiene personal	7.80	5.65

8 productes de 20, i la comparació amb 07-01 és molt instructiva: amb la mitjana global (9,035 €) en sortien 9. No en són 9 ni un subconjunt d'aquells:

Producte	Supera la mitjana global (9,035)?	Supera la mitjana de la seva categoria?
19 · Desodorant, 7,80 €	No	Sí (5,65 d'Higiene personal)
3 · Mel, 9,75 €	Sí	Sí (6,18 d'Alimentació)
12 · Bosses, 9,90 €	Sí	No (10,09 de Llar sostenible)
20 · Espirulina, 16,40 €	Sí	No (és l'únic de Complements: és la seva pròpia mitjana)

El desodorant és barat en termes absoluts però car dins de la seva categoria; les bosses són just el contrari. I el cas de l'espirulina és el més divertit: és l'únic producte de Complements, així que la mitjana de la seva categoria és el seu propi preu, i 16.40 > 16.40 és fals. Un producte tot sol a la seva categoria mai no pot superar la mitjana de la seva categoria.

Dos detalls d'escriptura que convé fixar:

L'expressió està repetida al SELECT i al WHERE, exactament com passava amb HAVING a 04-06. És obligatori (els àlies del SELECT no existeixen al WHERE) i és lleig. La solució neta són les CTE de 10-02.
Els àlies p i p2 són imprescindibles. Dins i fora és la mateixa taula productes, i sense àlies diferents WHERE categoria_id = categoria_id seria una tautologia: la subconsulta ignoraria la categoria, retornaria la mitjana global i la consulta deixaria d'estar correlacionada sense donar cap error. Hi tornarem a l'exercici 3.

La variant: el més car de la seva categoria, i l'autocorrelació

Canviant AVG per MAX i > per =, la mateixa estructura respon una altra pregunta clàssica:

SELECT p.id, p.nom AS producte, cat.nom AS categoria, p.preu
FROM productes  AS p
JOIN categories AS cat ON p.categoria_id = cat.id
WHERE p.preu = (SELECT MAX(p2.preu)
                FROM productes AS p2
                WHERE p2.categoria_id = p.categoria_id)
ORDER BY p.id;

id	producte	categoria	preu
1	Oli d'oliva verge extra 500 ml	Alimentació	12.50
6	Crema facial d'àloe vera 50 ml	Cosmètica natural	18.90
13	Espelmes de cera de soja (pack 2)	Llar sostenible	13.75
15	Te verd matcha cerimonial 30 g	Begudes	22.00
19	Desodorant natural en barra 50 g	Higiene personal	7.80
20	Càpsules d'espirulina 120 u	Complements	16.40

6 files, una per categoria. És el patró greatest-n-per-group, segurament el més demanat de tot el SQL analític.

Aquí la taula externa i la interna són la mateixa, i això enllaça directament amb el self join de 03-06: igual que allà uníeu empleats amb empleats per treure el cap de cadascú, aquí compares productes amb productes. La diferència és que el self join produeix parelles de files i l'autocorrelació produeix un valor calculat per a cada fila. Quan el que vols és un agregat per grup, la correlacionada acostuma a llegir-se millor.

I un advertiment sobre l'empat: si dos productes de la mateixa categoria compartissin el preu màxim, en sortirien tots dos, perquè tots dos compleixen la igualtat. Gairebé sempre és el que vols. Si necessitessis exactament un per categoria, o el segon, o un rànquing complet, l'eina correcta ja no és aquesta: són les funcions de finestra (ROW_NUMBER(), RANK()) de 10-03.

Quatre casos més de BotigaVerda

Una subconsulta correlacionada pot anar també al SELECT, com a columna calculada. Aquesta consulta respon tres preguntes de cop:

SELECT c.id,
       c.nom || ' ' || c.cognoms AS client,
       (SELECT COUNT(*) FROM comandes AS co WHERE co.client_id = c.id) AS comandes,
       (SELECT MAX(co.data_comanda) FROM comandes AS co WHERE co.client_id = c.id) AS ultima_comanda,
       (SELECT ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2)
        FROM comandes AS co
        JOIN linies_comanda AS lc ON lc.comanda_id = co.id
        WHERE co.client_id = c.id
        GROUP BY co.id
        ORDER BY 1 DESC
        LIMIT 1) AS comanda_mes_cara
FROM clients AS c
ORDER BY c.id;

id	client	comandes	ultima_comanda	comanda_mes_cara
1	Lucía Martínez Soler	3	2025-12-02	42.10
2	Carlos Ferrer Ibáñez	2	2025-09-09	32.76
3	Marta Sanchis Gil	1	2025-04-02	29.53
4	Javier Ortega Ruiz	2	2025-12-19	31.75
5	Ana Belmonte Roca	2	2026-01-27	28.10
6	Pau Llorens Vidal	2	2026-02-09	30.60
7	Sofia Moreira Costa	2	2026-01-13	64.88
8	Tiago Almeida Nunes	1	2025-07-15	44.60
9	Camille Dubois	2	2026-02-21	48.27
10	Julien Moreau	1	2025-10-01	66.90
11	Elena Navarro Puig	1	2025-10-22	30.30
12	Diego Ramos Herrera	1	2025-11-14	31.70
13	Núria Bosch Ferrer	0	(null)	(null)
14	Hugo Iglesias Pardo	0	(null)	(null)
15	Inés Carrasco Vega	0	(null)	(null)

Els 15 clients, inclosos els tres que no han comprat mai. Això és una diferència enorme respecte d'un INNER JOIN, que hauria retornat 12 files: una subconsulta al SELECT no elimina files de la consulta externa; retorna un valor o NULL, però la fila continua allà. Es comporta com un LEFT JOIN sense ser-ho.

I observa l'asimetria de les tres últimes files: comandes val 0 mentre que les altres dues valen NULL. No és cap incoherència, és 04-04: COUNT sobre un conjunt buit retorna 0; MAX i SUM, NULL. Si aquella columna ha d'alimentar un càlcul, embolcalla-la en COALESCE (06-04).

Quart cas: la ressenya més recent de cada producte. Aquí la correlació filtra per producte i LIMIT 1 retalla:

SELECT p.id, p.nom AS producte,
       (SELECT r.data      FROM ressenyes AS r WHERE r.producte_id = p.id
        ORDER BY r.data DESC LIMIT 1) AS ultima_ressenya,
       (SELECT r.puntuacio FROM ressenyes AS r WHERE r.producte_id = p.id
        ORDER BY r.data DESC LIMIT 1) AS puntuacio
FROM productes AS p
WHERE EXISTS (SELECT 1 FROM ressenyes AS r WHERE r.producte_id = p.id)
ORDER BY p.id;

id	producte	ultima_ressenya	puntuacio
1	Oli d'oliva verge extra 500 ml	2025-07-08	5
2	Arròs integral ecològic 1 kg	2025-09-19	5
5	Tomàquet triturat ecològic 400 g	2025-04-12	3
6	Crema facial d'àloe vera 50 ml	2025-08-14	4
10	Detergent ecològic concentrat 1 L	2026-01-10	4
12	Bosses reutilitzables de cotó (pack 5)	2025-11-03	3
15	Te verd matcha cerimonial 30 g	2025-05-02	5
16	Kombutxa de gingebre 750 ml	2025-06-20	2
18	Raspall de dents de bambú	2025-07-26	4

9 productes, els únics amb alguna ressenya; els altres 11 els exclou l'EXISTS (lliçó següent). I aquí treu el nas un defecte real d'aquest patró: hi ha dues subconsultes gairebé idèntiques per treure dues columnes de la mateixa fila, és a dir el doble de feina. Això es resol amb LATERAL (07-04) o amb funcions de finestra (10-03); amb una sola columna l'escriptura de dalt és perfectament raonable.

L'abast dels àlies: qui veu qui

La regla és asimètrica i cal memoritzar-la:

La subconsulta veu els àlies de la consulta externa. La consulta externa NO veu els àlies de la subconsulta.

-- ⚠️ INCORRECTA: p2 no existeix fora de la subconsulta
SELECT p.id, p.nom, p2.preu
FROM productes AS p
WHERE p.preu > (SELECT AVG(p2.preu) FROM productes AS p2 WHERE p2.categoria_id = p.categoria_id);

ERROR:  missing FROM-clause entry for table "p2"
LINE 1: SELECT p.id, p.nom, p2.preu
                            ^

La visibilitat va de dins cap enfora, mai a l'inrevés. Pensa en cada subconsulta com una funció que rep la fila externa com a paràmetre: pot llegir el que li passen, però el que passa al seu interior no s'exporta. Si necessites una columna de la taula interna al resultat, la resposta no és una subconsulta: és un JOIN (07-05).

Dues conseqüències pràctiques:

Quan la taula de dins i la de fora són la mateixa, els àlies són obligatoris. productes AS p fora, productes AS p2 dins. Sense ells, WHERE categoria_id = categoria_id es resol dins de la subconsulta i sempre és cert.
Si un nom de columna existeix a les dues taules i no el qualifiques, guanya el de dins. És la regla de resolució de noms de SQL: primer l'àmbit més proper, després els exteriors. És una manera subtilíssima d'escriure una consulta que "funciona" i respon una altra pregunta. Qualifica sempre totes les columnes dins d'una correlacionada.

El cost: N execucions i què fa el planificador

Conceptualment, una correlacionada és un bucle imbricat: N files externes × 1 execució interna. Amb productes són 20 execucions; amb una taula de dos milions de files, dos milions. Si a més la subconsulta fa un JOIN, la feina es multiplica.

Conceptualment. Perquè PostgreSQL no l'executa necessàriament així. El planificador reescriu moltes correlacionades en formes equivalents i molt més barates:

Forma escrita	En què la sol transformar	Efecte
`EXISTS (...)` correlacionat	Semi-join (hash o merge)	Una passada, no N
`NOT EXISTS (...)`	Anti-join	Una passada
`IN (SELECT ...)`	Semi-join	Una passada
Agregat correlacionat al `WHERE`	De vegades, agregació + join	Depèn
Agregat correlacionat al `SELECT`	Gairebé mai: s'executa per fila	N execucions reals

L'última fila és la que importa: les correlacionades al SELECT són les que més sovint es queden com a bucle. Amb 15 clients tant se val; amb 15 milions, una consulta amb tres subconsultes al SELECT pot trigar minuts on un LEFT JOIN amb GROUP BY triga segons.

Comprovar-ho de debò —veure el pla, mesurar el temps, saber si hi ha hagut semi-join o bucle— requereix EXPLAIN ANALYZE, i això és la lliçó 08-05. Fins llavors, queda't amb la intuïció i amb aquesta regla: no reescriguis per rendiment sense mesurar, però desconfia de les correlacionades al SELECT sobre taules grans.

Quan fer-la servir i quan delata que hi falta una altra cosa

Situació	Correlacionada?
Comparar cada fila amb un agregat del seu grup	Sí, és el seu cas natural
Preguntar si existeix alguna cosa relacionada (`EXISTS`)	Sí, i sempre (07-03)
Portar "l'últim", "el primer", "el màxim" de cada fila	Sí, o `LATERAL` (07-04)
Una o dues columnes calculades sobre una taula petita	Sí, es llegeix molt bé
Cinc columnes calculades sobre la mateixa taula relacionada	No: és un `LEFT JOIN` + `GROUP BY` disfressat
Un rànquing, un "top 3 per grup", un acumulat	No: funcions de finestra (10-03)
Necessites columnes de la taula interna al resultat	No: és un `JOIN` (07-05)

Els dos senyals d'alarma són clars. Si repeteixes la mateixa correlació tres o quatre vegades al SELECT, estàs recorrent la mateixa taula tres o quatre vegades per agrupar per la mateixa clau: això és un GROUP BY. I si apareix la paraula "rànquing", "posició", "el segon" o "acumulat", cap subconsulta no ho farà amb elegància; això són funcions de finestra.

Reescriptura 1: la mitjana per categoria, amb taula derivada

SELECT p.id, p.nom AS producte, cat.nom AS categoria,
       p.preu, ROUND(m.preu_mitja, 2) AS mitjana_categoria
FROM productes  AS p
JOIN categories AS cat ON p.categoria_id = cat.id
JOIN (SELECT categoria_id, AVG(preu) AS preu_mitja
      FROM productes GROUP BY categoria_id) AS m ON m.categoria_id = p.categoria_id
WHERE p.preu > m.preu_mitja
ORDER BY p.id;

Retorna exactament les mateixes 8 files de la secció 3. La diferència és que la mitjana de cada categoria es calcula una sola vegada —sis mitjanes, en una passada— en lloc de vint vegades. A més l'expressió deixa d'estar duplicada: es nomena m.preu_mitja i es fa servir dues vegades. Aquesta subconsulta al FROM s'anomena taula derivada i és el contingut de 07-04.

Reescriptura 2: el recompte de comandes, amb `LEFT JOIN` i `GROUP BY`

SELECT c.id,
       c.nom || ' ' || c.cognoms AS client,
       COUNT(co.id)         AS comandes,
       MAX(co.data_comanda) AS ultima_comanda
FROM clients AS c
LEFT JOIN comandes AS co ON co.client_id = c.id
GROUP BY c.id, c.nom, c.cognoms
ORDER BY c.id;

Retorna les mateixes 15 files que les dues primeres columnes de la secció 5, amb els mateixos 0 i NULL. Una sola passada per comandes en lloc de 30 subconsultes. Quan les columnes correlacionades comencen a acumular-se, aquesta és la reescriptura que toca. La comparació honesta de totes dues formes —llegibilitat, files, rendiment— és 07-05.

Errors habituals i consells

Oblidar els àlies quan la taula és la mateixa dins i fora. WHERE categoria_id = categoria_id és una tautologia: la correlació desapareix, la subconsulta calcula la mitjana global i no hi ha cap error.
No qualificar les columnes dins de la subconsulta. Si el nom existeix a les dues taules, guanya l'àmbit intern i la consulta respon una altra pregunta, en silenci.
Intentar fer servir un àlies intern a la consulta externa. missing FROM-clause entry for table "p2". La visibilitat va només de dins cap enfora.
Executar la subconsulta sola per depurar-la. No es pot: donarà aquest mateix error. Per provar-la, substitueix a mà el valor extern (WHERE p2.categoria_id = 1) i comprova que retorna el que esperes.
Esperar que una subconsulta al SELECT filtri files. No filtra: retorna un valor o NULL. Els 15 clients continuen apareixent.
Confondre el 0 de COUNT amb el NULL de MAX o SUM. Un client sense comandes té 0 comandes i NULL a qualsevol altra columna agregada.
Escriure una escalar correlacionada que retorna diverses files. more than one row returned by a subquery used as an expression: la correlació acota, però no garanteix unicitat. Afegeix-hi un agregat o un ORDER BY ... LIMIT 1.
Consell: escriu primer la subconsulta amb un valor fix. Comprova que AVG(preu) WHERE categoria_id = 1 dona 6,18 i després substitueix l'1 per p.categoria_id. Depurar les dues capes alhora és innecessàriament difícil.
Consell: si repeteixes la mateixa correlació en dues columnes, passa a LATERAL o a GROUP BY. Dues subconsultes idèntiques per treure dos camps de la mateixa fila són el doble de feina per res.
Consell: compta quantes vegades s'executarà. Files de la taula externa × subconsultes correlacionades. Si el número t'incomoda, planteja't la reescriptura de la secció 8 abans que ho faci producció.

Exercicis

Exercici 1

Qualitat vol saber quins productes tenen una puntuació mitjana superior a la mitjana global de totes les ressenyes. Escriu la consulta amb una subconsulta no correlacionada per a la mitjana global i mostra id, producte, nombre de ressenyes i mitjana (dos decimals), ordenat per mitjana descendent i id.

Després respon: hi ha alguna part correlacionada a la teva consulta? Per què canviaria la resposta si la pregunta fos "mitjana superior a la mitjana de la seva categoria"?

Exercici 2

Vendes necessita, per a cada client que hagi comprat, l'import de la seva última comanda (la de data més recent), juntament amb el seu nom i aquella data. Fes servir subconsultes correlacionades. Després indica: què passaria si un client tingués dues comandes el mateix dia, i com ho arreglaries?

Exercici 3

Un company ha escrit això per treure "els productes per sobre de la mitjana de la seva categoria" i s'estranya que li retorni les mateixes 9 files que la consulta amb la mitjana global de 07-01, en comptes de 8:

-- ⚠️ INCORRECTA
SELECT id, nom, preu
FROM productes
WHERE preu > (SELECT AVG(preu) FROM productes WHERE categoria_id = categoria_id);

Per què retorna 9 files i no 8?
Corregeix-la.
Quin hauria estat el resultat si en lloc de categoria_id = categoria_id hagués escrit categoria_id = proveidor_id? Explica'n el mecanisme, no cal el número exacte.

Solucions

Solució 1

SELECT p.id,
       p.nom AS producte,
       COUNT(*)                   AS ressenyes,
       ROUND(AVG(r.puntuacio), 2) AS mitjana
FROM ressenyes AS r
JOIN productes AS p ON r.producte_id = p.id
GROUP BY p.id, p.nom
HAVING AVG(r.puntuacio) > (SELECT AVG(puntuacio) FROM ressenyes)
ORDER BY mitjana DESC, p.id;

id	producte	ressenyes	mitjana
1	Oli d'oliva verge extra 500 ml	2	5.00
15	Te verd matcha cerimonial 30 g	1	5.00
2	Arròs integral ecològic 1 kg	2	4.50
6	Crema facial d'àloe vera 50 ml	2	4.50

4 productes dels 9 ressenyats superen la mitjana global, que és 4,0833… (49 punts entre 12 ressenyes). En queden fora el detergent i el raspall (4,00), el tomàquet i les bosses (3,00) i la kombutxa (2,00).

No hi ha correlació enlloc: la subconsulta SELECT AVG(puntuacio) FROM ressenyes no esmenta res de fora, s'executa una vegada i dona un número. És el mateix patró del tiquet mitjà de 07-01. Si la pregunta fos "superior a la mitjana de la seva categoria", la subconsulta hauria de filtrar per la categoria del producte del grup actual —WHERE p2.categoria_id = p.categoria_id— i passaria a ser correlacionada, avaluant-se una vegada per grup.

Solució 2

SELECT c.id,
       c.nom || ' ' || c.cognoms AS client,
       (SELECT MAX(co.data_comanda) FROM comandes AS co WHERE co.client_id = c.id) AS data,
       (SELECT ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2)
        FROM comandes AS co
        JOIN linies_comanda AS lc ON lc.comanda_id = co.id
        WHERE co.client_id = c.id
          AND co.data_comanda = (SELECT MAX(co2.data_comanda)
                                 FROM comandes AS co2 WHERE co2.client_id = c.id)) AS import
FROM clients AS c
WHERE EXISTS (SELECT 1 FROM comandes AS co WHERE co.client_id = c.id)
ORDER BY c.id;

id	client	data	import
1	Lucía Martínez Soler	2025-12-02	33.40
2	Carlos Ferrer Ibáñez	2025-09-09	32.76
3	Marta Sanchis Gil	2025-04-02	29.53
4	Javier Ortega Ruiz	2025-12-19	31.18
5	Ana Belmonte Roca	2026-01-27	28.10
6	Pau Llorens Vidal	2026-02-09	26.73
7	Sofia Moreira Costa	2026-01-13	47.00
8	Tiago Almeida Nunes	2025-07-15	44.60
9	Camille Dubois	2026-02-21	22.60
10	Julien Moreau	2025-10-01	66.90
11	Elena Navarro Puig	2025-10-22	30.30
12	Diego Ramos Herrera	2025-11-14	31.70

12 files. Hi ha tres nivells d'imbricació i dues correlacions diferents contra c.id. Compara el resultat amb el de la secció 5: l'última comanda de la Lucía val 33,40 € mentre que la seva comanda més cara val 42,10 €; la de la Camille són 22,60 € enfront de 48,27 €. Són preguntes diferents i convé no confondre-les en un informe.

Si un client tingués dues comandes el mateix dia, la subconsulta de l'import sumaria totes dues i retornaria un total inflat (no donaria error, perquè el GROUP BY no hi és i SUM agrega tot el que li arriba). L'arranjament és desempatar per clau primària: en lloc de filtrar per data_comanda = MAX(...), filtrar per co.id = (SELECT co2.id FROM comandes AS co2 WHERE co2.client_id = c.id ORDER BY co2.data_comanda DESC, co2.id DESC LIMIT 1). Qualsevol "l'últim" basat només en una data sense hora és fràgil; afegeix-hi sempre un desempat determinista.

Solució 3

1. Perquè categoria_id = categoria_id es resol sencer dins de la subconsulta: no hi ha cap àlies que distingeixi la taula de fora de la de dins, així que les dues aparicions es refereixen a la productes interna. La condició és "una columna igual a si mateixa", certa per a les 20 files, i la subconsulta acaba retornant la mitjana global, 9,035 € — d'aquí les 9 files. La consulta no està correlacionada en absolut, encara que ho sembli, i aquesta és la fallada: el filtre per categoria no s'aplica mai. És un error silenciós de manual.

2. La correcció és la de la secció 3: àlies diferents dins i fora.

-- ✅ CORRECTA
SELECT p.id, p.nom, p.preu
FROM productes AS p
WHERE p.preu > (SELECT AVG(p2.preu) FROM productes AS p2
                WHERE p2.categoria_id = p.categoria_id);

8 files, les de la secció 3.

3. categoria_id = proveidor_id tampoc no correlaciona: compara dues columnes de la mateixa taula interna. La subconsulta retornaria la mitjana dels productes la categoria dels quals coincideix numèricament amb el seu proveïdor —una condició sense cap sentit de negoci però perfectament vàlida—, i aquell únic número s'aplicaria a les 20 files. És el pitjor dels casos: una consulta que no dona error, sembla correlacionada, i respon una pregunta que ningú no ha fet. D'aquí la regla de qualificar sempre les columnes.

Conclusió

La correlació canvia la naturalesa d'una subconsulta:

Una subconsulta correlacionada referencia un àlies de la consulta externa; no es pot executar sola (missing FROM-clause entry) i s'avalua una vegada per cada fila candidata, com un bucle imbricat.
El cas canònic és comparar cada fila amb un agregat del seu propi grup: 8 productes superen la mitjana de la seva categoria, enfront dels 9 que superaven la mitjana global — i no són els mateixos, perquè el desodorant és car per a Higiene personal i les bosses són barates per a Llar sostenible.
La variant amb = MAX(...) dona el més car de cada categoria (6 files, una per categoria), el patró greatest-n-per-group, germà de l'autocorrelació i del self join de 03-06.
Al SELECT funciona com a columna calculada i no elimina files: els 15 clients continuen allà, amb 0 a COUNT i NULL a MAX i SUM.
La visibilitat és asimètrica: la subconsulta veu els àlies de fora, l'externa no veu els de dins. Quan la taula és la mateixa, els àlies diferents són obligatoris: sense ells la correlació s'evapora en silenci.
Conceptualment costa N execucions. PostgreSQL en reescriu moltes com a semi-joins o anti-joins, però les correlacionades al SELECT acostumen a quedar-se com a bucle; mesurar-ho de debò és 08-05.
I saps reconèixer quan no és l'eina: diverses correlacions repetides demanen un LEFT JOIN amb GROUP BY, i qualsevol rànquing demana funcions de finestra (10-03).

A la lliçó següent, EXISTS i NOT EXISTS, veuràs la forma més pura de subconsulta correlacionada: una que no retorna cap valor, només respon sí o no. Amb ella escriuràs per fi "clients que no han comprat mai" sense LEFT JOIN, descobriràs per què NOT EXISTS és segur amb nuls i NOT IN no, i resoldràs el problema clàssic de la divisió relacional: quins clients han comprat de totes les categories.

Subconsultes correlacionades

Contingut

Com es reconeix una correlacionada

El model mental: una execució per fila

El cas canònic: cada producte enfront de la mitjana de la seva categoria

La variant: el més car de la seva categoria, i l'autocorrelació

Quatre casos més de BotigaVerda

L'abast dels àlies: qui veu qui

El cost: N execucions i què fa el planificador

Quan fer-la servir i quan delata que hi falta una altra cosa

Reescriptura 1: la mitjana per categoria, amb taula derivada

Reescriptura 2: el recompte de comandes, amb `LEFT JOIN` i `GROUP BY`

Errors habituals i consells

Exercicis

Exercici 1

Exercici 2

Exercici 3

Solucions

Conclusió

Curs de SQL

Mòdul 1: Introducció a SQL

Mòdul 2: Consultes bàsiques de SQL

Mòdul 3: Treballar amb múltiples taules

Mòdul 4: Filtratge avançat de dades

Mòdul 5: Manipulació de dades

Mòdul 6: Funcions avançades de SQL

Mòdul 7: Subconsultes i consultes imbricades

Mòdul 8: Índexs i optimització del rendiment

Mòdul 9: Transaccions i concurrència

Mòdul 10: Temes avançats

Mòdul 11: SQL a la pràctica

Mòdul 12: Projecte final

Subconsultes correlacionades

Contingut

Com es reconeix una correlacionada

El model mental: una execució per fila

El cas canònic: cada producte enfront de la mitjana de la seva categoria

La variant: el més car de la seva categoria, i l'autocorrelació

Quatre casos més de BotigaVerda

L'abast dels àlies: qui veu qui

El cost: N execucions i què fa el planificador

Quan fer-la servir i quan delata que hi falta una altra cosa

Reescriptura 1: la mitjana per categoria, amb taula derivada

Reescriptura 2: el recompte de comandes, amb LEFT JOIN i GROUP BY

Errors habituals i consells

Exercicis

Exercici 1

Exercici 2

Exercici 3

Solucions

Conclusió

Curs de SQL

Mòdul 1: Introducció a SQL

Mòdul 2: Consultes bàsiques de SQL

Mòdul 3: Treballar amb múltiples taules

Mòdul 4: Filtratge avançat de dades

Mòdul 5: Manipulació de dades

Mòdul 6: Funcions avançades de SQL

Mòdul 7: Subconsultes i consultes imbricades

Mòdul 8: Índexs i optimització del rendiment

Mòdul 9: Transaccions i concurrència

Mòdul 10: Temes avançats

Mòdul 11: SQL a la pràctica

Mòdul 12: Projecte final

Reescriptura 2: el recompte de comandes, amb `LEFT JOIN` i `GROUP BY`