La lliçó anterior va acabar amb una llista de preguntes que encara no sabies respondre, i totes tenien la mateixa forma: comparar cada fila amb el resultat d'una altra consulta. Quins productes superen el preu mitjà del catàleg? Quins clients tenen un tiquet mitjà per sobre de la mitjana general —la pregunta que 04-06 va deixar explícitament reservada per a aquesta lliçó? Quines comandes inclouen el producte més car?

La resposta a totes elles és la mateixa eina: una subconsulta, una consulta escrita entre parèntesis dins d'una altra. Aquí n'aprendràs el vocabulari, els tres tipus de subconsulta segons el que retornen, com es fan servir amb IN, ANY i ALL, i els tres errors clàssics que produeixen des d'un missatge vermell fins a —molt pitjor— un resultat buit sense cap avís.

1. Què és una subconsulta: vocabulari, correlació i tipus
2. Subconsulta escalar al WHERE
3. La pregunta pendent de 04-06: el tiquet mitjà
4. Subconsultes de llista: IN, ANY/SOME i ALL
5. Els tres errors clàssics
6. On pot aparèixer una subconsulta
7. Errors habituals i consells
8. Exercicis
9. Conclusió

Una subconsulta (o consulta imbricada) és una sentència SELECT completa, escrita entre parèntesis, que apareix dins d'una altra sentència SQL. El motor l'executa i fa servir el seu resultat com si fos un valor, una llista o una taula.

La consulta que conté l'altra és la consulta externa (outer query); la de dins, la subconsulta (subquery). Una subconsulta pot contenir-ne una altra, sense més límit que la llegibilitat. Tres regles de sintaxi sense excepció: els parèntesis són obligatoris; la subconsulta s'escriu sencera (SELECT, FROM, WHERE, GROUP BY… el que calgui); i un ORDER BY dins d'una subconsulta gairebé mai no serveix de res, llevat que vagi acompanyat d'un LIMIT.

Aquesta és la distinció que estructura el mòdul sencer, i convé fixar-la abans que cap altra.

La prova pràctica és infal·lible: selecciona la subconsulta, executa-la sola i mira què passa. La primera retorna 9.0350000000000000. La segona dona ERROR: missing FROM-clause entry for table "p", perquè p no existeix fora de la consulta externa. Tota aquesta lliçó tracta de les no correlacionades.

La segona classificació, transversal a l'anterior, es refereix a la forma del resultat, i determina on pot aparèixer la subconsulta i amb quins operadors es combina.

Les escalars i les de taula cobreixen el 99 % del SQL que escriuràs. Les de fila són elegants però rares: WHERE (preu, stock) = (SELECT MAX(preu), 40 FROM productes) retorna una única fila, el matcha (22,00 € i 40 unitats).

Nota de dialecte: els constructors de fila (a, b) = (...) funcionen a PostgreSQL, MySQL i MariaDB; SQLite i SQL Server no els admeten i exigeixen dues condicions unides amb AND.

El cas d'ús més comú de tots: comparar cada fila amb un valor calculat sobre el conjunt. La pregunta és "quins productes estan per sobre del preu mitjà del catàleg?", i l'intent ingenu és aquest:

És el mateix error de 04-06 i per la mateixa raó: quan s'executa el WHERE (pas 2 de l'ordre lògic) el motor mira una fila cada vegada i no ha calculat cap agregat. La subconsulta ho resol perquè és una altra consulta, amb el seu propi recorregut complet per la taula:

9 files: 9 productes de 20 superen els 9,035 € de preu mitjà (SELECT ROUND(AVG(preu), 4) FROM productes → 9.0350, la xifra de 04-04). L'important és com s'executa: PostgreSQL avalua la subconsulta una sola vegada, obté 9.0350000000000000 i substitueix l'expressió per aquell número; a partir d'aquí la consulta externa és un WHERE preu > 9.0350000000000000 corrent. No hi ha cap bucle: és una constant calculada al vol. I compte amb un detall que importa: la comparació es fa amb el valor sense arrodonir — amb altres dades, un cèntim decideix si una fila hi entra o en surt. No arrodoneixis mai el valor de comparació; arrodoneix només allò que mostres.

A 04-06 vas calcular el tiquet mitjà de cada client i vas comprovar que la mitjana global dels 20 tiquets és de 36,40 €, però no vas poder unir les dues coses: HAVING sap comparar l'agregat d'un grup amb una constant o amb un altre agregat del mateix grup, mai amb un agregat calculat sobre un altre conjunt de files. Una subconsulta escalar al HAVING és la peça que faltava.

Primer, el valor de referència. La mitjana global no és la mitjana de les 47 línies (això donaria els 15,49 € d'import mitjà de línia), sinó la mitjana dels 20 tiquets:

I ara la mateixa expressió, injectada al HAVING:

Tres clients de dotze, exactament els que 04-06 va anticipar. Pas a pas:

1. FROM + dos JOIN: es parteix de les 47 línies de detall i es puja fins al client; és la consulta canònica de detall del curs. El GROUP BY forma 12 grups, un per client comprador (els clients 13, 14 i 15 ja els va descartar l'INNER JOIN).
2. La subconsulta escalar s'avalua una vegada i retorna 36.39725, la mitjana exacta sense arrodonir.
3. HAVING compara el tiquet mitjà de cada grup amb aquella constant i en descarta nou grups.
4. SELECT projecta i arrodoneix. L'arrodoniment és només de presentació: la comparació ja s'ha fet amb tota la precisió.

I una lectura de negoci: els tres són clients estrangers, cap espanyol no supera la mitjana (el més proper és la Lucía, amb 35,87 €). Els ports a Portugal i França costen 9,90 € i 12,50 € enfront dels 4,95 € nacionals, i el client ho compensa fent comandes més grans. Això sí, dos dels tres tenen una sola comanda: el cas realment sòlid és la Sofia, amb dos tiquets de 64,88 € i 47,00 €.

Per què va al HAVING i no al WHERE: la condició compara un agregat del grup amb la constant, i el WHERE no pot fer servir agregats. La subconsulta només aporta el número amb què comparar.

Quan la subconsulta retorna diverses files d'una sola columna, la forma natural de fer-la servir és IN. Ja coneixes l'operador de 04-02 amb una llista escrita a mà; ara la llista la calcula una altra consulta.

(3 primeres de 12 files: els clients 1 a 12, els 12 compradors.) Aquí hi ha un detall cabdal que reprendrem a 07-05: la subconsulta retorna 20 valors (un per comanda, amb repeticions), però el resultat té 12 files. IN no multiplica: pregunta si el valor és a la llista i respon sí o no una sola vegada per fila externa. Un INNER JOIN amb comandes hauria retornat 20 files.

Un segon exemple, amb una subconsulta agregada: productes de les categories que tenen més de tres productes.

17 productes de 20. Les categories amb més de tres referències són Alimentació (5), Cosmètica natural (4), Llar sostenible (4) i Begudes (4); en queden fora Higiene personal (productes 18 i 19) i Complements (el 20). La subconsulta és una consulta agregada completa, amb el seu GROUP BY i el seu HAVING: pots calcular un conjunt de claus amb tota la maquinària del mòdul 4 i fer-lo servir com a filtre.

L'equivalència amb = ANY. L'estàndard defineix IN com a sucre sintàctic de = ANY: c.id IN (sub) i c.id = ANY (sub) produeixen el mateix pla i el mateix resultat. IN es llegeix millor i és el que veuràs al codi real; = ANY importa perquè explica la família sencera:

SOME és un sinònim exacte d'ANY que no fa servir ningú. La forma que de debò apareix és > ALL, i la seva pregunta natural és "més car que qualsevol dels d'aquella categoria":

5 productes costen més que tots els d'Alimentació, el sostre de la qual és l'oli d'oliva (12,50 €). L'escriptura > (SELECT MAX(preu) FROM productes WHERE categoria_id = 1) retorna el mateix i es llegeix millor. Amb ANY en lloc d'ALL la condició seria "més car que el més barat d'Alimentació" (1,95 €) i en sortirien 19 productes.

Compte amb els conjunts buits. Si la subconsulta no retorna cap fila, > ALL és cert per a totes (no hi ha contraexemple) i > ANY és fals per a totes (no hi ha cap cas favorable). Impecable en lògica i desconcertant a la pràctica: preu > ALL (SELECT preu FROM productes WHERE categoria_id = 99) retorna els 20 productes.

Alimentació té cinc productes, la subconsulta retorna cinc preus i > no sap amb quin comparar. Els arranjaments són tres i cadascun respon a una pregunta diferent: > (SELECT MAX(preu) ...) i > ALL (...) diuen "més car que el més car"; > ANY (...) diu "més car que algun". És el menys perillós dels tres perquè és sorollós, i és un error de dades, no de sintaxi: la mateixa consulta funcionaria si la categoria tingués un sol producte i rebentaria en donar d'alta el segon. Si la subconsulta retorna diverses columnes, el missatge és ERROR: subquery must return only one column.

Cap missatge, cap advertiment. La categoria 99 no existeix, la subconsulta no troba files, AVG sobre un conjunt buit retorna NULL (04-04) i preu > NULL s'avalua a UNKNOWN per a les vint files. Com que el WHERE només deixa passar el que és TRUE (04-03), el resultat es buida en silenci. Aquest és el més perillós dels tres, perquè un informe buit sembla un informe legítim: "aquest mes no n'hi va haver cap". Com defensar-se'n: executa sempre la subconsulta sola abans d'imbricar-la; embolcalla-la en COALESCE quan hi hagi un valor per omissió sensat (> COALESCE((SELECT AVG(...)...), 0), 06-04); i si la pregunta és d'existència, fes servir EXISTS, que mai no retorna NULL (07-03).

A 04-02 el vas anomenar "l'error més car de SQL". Amb subconsultes és molt més fàcil de cometre, perquè ja no veus la llista: la calcula una altra consulta i no saps si porta nuls.

I tanmateix saps que hi ha cinc empleats sense comandes: només el 4, el 5 i el 6 apareixen a comandes. El que ha passat és que la subconsulta retorna {4, 5, 6, NULL} — les 10 comandes web tenen empleat_id a NULL. Per a l'empleada 1:

El mateix per a les vuit files. Amb un sol NULL a la llista, NOT IN no pot retornar TRUE mai. Els tres arranjaments —filtrar els nuls a dins, NOT EXISTS (07-03) i l'anti-join de 03-03— són aquests:

Els tres retornen el mateix:

5 files. I hi ha una asimetria que sorprèn tothom: IN sí que funciona amb nuls. 4 IN (4, 5, 6, NULL) és TRUE, perquè n'hi ha prou que una comparació encerti. El problema és exclusiu de la negació. La comparació de les quatre formes de respondre "què no casa" arriba a 07-03, i la seva taula definitiva, a 07-05.

Gairebé a qualsevol lloc on càpiga un valor o una taula:

Dos exemples del mòdul 5 revisitats, ara que saps com es diuen: UPDATE productes SET preu = ROUND(preu * 1.05, 2) WHERE id NOT IN (SELECT producte_id FROM linies_comanda) puja un 5 % el que no s'ha venut mai, i DELETE FROM ressenyes WHERE client_id NOT IN (SELECT id FROM clients) neteja ressenyes òrfenes. Els dos són segurs perquè linies_comanda.producte_id i clients.id són NOT NULL. Si alguna admetés nuls, l'UPDATE no modificaria res i el DELETE no esborraria res — la trampa de 5.3, ara amb conseqüències sobre les dades. I on no pot aparèixer una subconsulta: al GROUP BY, ni —a PostgreSQL— dins d'una restricció CHECK.

• Escriure un agregat al WHERE. aggregate functions are not allowed in WHERE. El que necessites és una subconsulta escalar.
• Fer servir una subconsulta multifila on s'espera un valor. more than one row returned by a subquery used as an expression. Afegeix-hi un agregat, un ORDER BY ... LIMIT 1, o canvia a IN/ANY/ALL.
• No comprovar que l'escalar retorna alguna cosa. Si retorna zero files val NULL, el filtre es buida en silenci i l'informe sembla correcte. És l'error més car de la lliçó.
• NOT IN sobre una columna que admet nuls. Zero files, sempre. Fes servir NOT EXISTS o filtra els nuls dins de la subconsulta.
• Arrodonir el valor de comparació. > ROUND(AVG(preu), 2) no és > AVG(preu). Arrodoneix en mostrar, no en comparar. I evita l'ORDER BY dins d'una subconsulta de llista: no aporta res i costa temps.
• Consell: executa sempre la subconsulta sola primer. És la tècnica de depuració número u del mòdul; si no es pot executar sola, és correlacionada.
• Consell: si necessites columnes de l'altra taula al resultat, no és una subconsulta: és un JOIN. La subconsulta filtra i calcula; no aporta columnes al SELECT extern (07-05).

Màrqueting vol una llista de productes cars mesurats amb un patró concret: els que superen la mitjana de preus de la categoria Cosmètica natural. Mostra id, nom, categoria i preu, ordenats per preu descendent, i no escriguis el llindar a mà. Després respon: quants dels productes que en surten són de Cosmètica natural, i per què els altres dos d'aquesta categoria no hi apareixen?

Un company vol saber quins clients no han escrit mai una ressenya i ha escrit això:

1. Funciona? Justifica la resposta mirant l'esquema de 01-06, sense executar-la.
2. Dona'n el resultat i digues quants d'aquests clients sí que han comprat alguna vegada.
3. Reescriu-la amb NOT EXISTS i amb un anti-join, i explica per què aquí les tres són equivalents.

Direcció pregunta: "quines comandes inclouen el producte més car del catàleg?". Escriu una única consulta que retorni l'id de la comanda, la data, el client i l'estat, sense escriure a mà ni el nom ni l'id d'aquell producte. (Pista: necessitaràs dues subconsultes, una dins de l'altra.) Indica després què passaria si hi hagués dos productes empatats al preu màxim.

Solució 1

6 files. El llindar calculat és de 11,5375 € (46,15 € entre 4 productes). Només dos són de Cosmètica natural, la crema d'àloe i l'oli corporal; els altres dos de la categoria —xampú 8,40 € i bàlsam 4,60 €— queden per sota de la seva pròpia mitjana, que està estirada cap amunt precisament pels dos cars.

El punt de fons: el llindar surt d'una categoria però s'aplica a tot el catàleg, perquè una subconsulta no correlacionada s'avalua una vegada i val igual per a les vint files. Comparar cada producte amb la mitjana de la seva pròpia categoria exigeix una de correlacionada: lliçó següent.

Solució 2

1. Sí que funciona, i se sap sense executar-la: ressenyes.client_id està declarada NOT NULL (01-06, taula 3.8), així que la subconsulta no pot retornar cap NULL i NOT IN es comporta correctament. La sospita era el reflex correcte davant de qualsevol NOT IN; l'esquema l'esvaeix. 2. El resultat:

6 clients de 15, i tres d'ells sí que han comprat: l'Ana (2 comandes), en Julien (1) i en Diego (1). Els altres tres són els coneguts 13, 14 i 15, que no han comprat mai i per tant no podien ressenyar res. Distingir els dos grups importa: a l'Ana, en Julien i en Diego se'ls pot demanar una opinió; a la Núria, l'Hugo i la Inés cal vendre'ls alguna cosa primer.

3. Les dues reescriptures, amb el mateix resultat de sis files:

Les tres són equivalents aquí per una única raó: ressenyes.client_id és NOT NULL. Si demà es permetessin ressenyes anònimes amb client_id nul, la versió amb NOT IN passaria a retornar zero files i les altres dues continuarien funcionant. L'equivalència depèn de l'esquema, no de la sintaxi.

Solució 3

3 comandes. El producte més car és el Te verd matcha cerimonial 30 g (22,00 €) i s'ha venut tres vegades. Hi ha dos nivells: la subconsulta interna retorna un valor escalar (l'id del producte més car) i la intermèdia, una llista de comanda_id.

Si hi hagués empat, la interna continuaria retornant una sola fila —LIMIT 1 talla arbitràriament— i perdries en silenci les comandes de l'altre producte. La versió robusta canvia l'= per un IN i elimina el LIMIT: WHERE lc.producte_id IN (SELECT id FROM productes WHERE preu = (SELECT MAX(preu) FROM productes)). Amb les dades actuals retorna el mateix, però no es trenca el dia que algú doni d'alta un segon producte de 22,00 €. ORDER BY ... LIMIT 1 dins d'una subconsulta és còmode i fràgil.

Has obert la porta del mòdul:

• Una subconsulta és una consulta entre parèntesis dins d'una altra; la que la conté és la consulta externa. Segons el que retornen n'hi ha tres tipus —escalar, de fila i de taula—, i cadascun admet uns operadors diferents.
• La distinció que organitza el mòdul és no correlacionada (no esmenta res de fora, s'executa sola, s'avalua una vegada) enfront de correlacionada (fa servir un àlies extern, no es pot executar aïllada, s'avalua una vegada per fila).
• Una escalar al WHERE resol "per sobre de la mitjana": 9 dels 20 productes superen els 9,035 € del catàleg. I una escalar al HAVING tanca la pregunta que 04-06 va deixar pendent: en Julien, la Sofia i en Tiago són els únics amb un tiquet mitjà superior als 36,40 € de mitjana global.
• IN (SELECT ...) filtra per una llista calculada —12 clients compradors, 17 productes de categories amb més de tres referències— i equival a = ANY; > ALL compara amb el màxim i > ANY amb el mínim. I coneixes els tres errors clàssics: l'escalar amb diverses files (sorollosa), l'escalar amb zero files (silenciosa: dona NULL i buida el resultat) i NOT IN amb nuls (zero files garantides).

Totes les subconsultes d'aquesta lliçó tenen una cosa en comú: es calculen una vegada i valen el mateix per a totes les files. Per això cap no ha pogut respondre a "quins productes superen la mitjana de la seva categoria": aquell llindar és diferent per a cada fila. A la lliçó següent, subconsultes correlacionades, la subconsulta començarà a mirar cap enfora —a la fila que la consulta externa està examinant en aquell moment— i s'executarà una vegada per cadascuna d'elles. Canvia el model mental, canvia el cost i apareix tota una família de preguntes noves.