Hi ha una família de preguntes que no necessita cap valor: només un sí o un no. Ha comprat aquest client alguna vegada? Té aquest producte alguna ressenya? Conté aquesta comanda algun article de cosmètica? No importa quants, ni quins, ni quant sumen: importa si existeix almenys un.

EXISTS és l'operador que respon exactament això, i és probablement la subconsulta que més escriuràs en la teva vida professional. La seva negació, NOT EXISTS, resol l'altra meitat —clients que no han comprat, productes que ningú no ha venut— i ho fa amb una propietat que cap de les alternatives no té: és immune als valors nuls. En aquesta lliçó veuràs totes dues, les compararàs amb NOT IN, amb l'anti-join de 03-03 i amb EXCEPT de 03-07, i tancaràs amb el problema clàssic de la divisió relacional.

1. EXISTS: un predicat, no un valor
2. Per què tant se val el que posi al SELECT intern
3. Quatre casos de BotigaVerda amb EXISTS
4. NOT EXISTS: els quatre buits del conjunt de dades
5. NOT EXISTS enfront de NOT IN: la diferència que importa
6. Les quatre formes de respondre "què no casa"
7. Curtcircuit: EXISTS enfront de COUNT(*) > 0
8. Divisió relacional: la doble negació
9. Errors habituals i consells
10. Exercicis
11. Conclusió

EXISTS (subconsulta) és un predicat: retorna TRUE o FALSE, mai NULL. La seva regla és d'una simplicitat radical:

EXISTS és TRUE tan bon punt la subconsulta produeix la seva primera fila. Si no en produeix cap, és FALSE.

(4 primeres de 12 files: els 12 clients que han comprat alguna vegada.)

Tres propietats el distingeixen de tot el que has vist: retorna TRUE/FALSE i mai NULL, així que és immune a la lògica de tres valors de 04-03; no mira el contingut de la subconsulta, tant se val quines columnes retorni; i s'atura a la primera fila, no compta ni suma ni ordena.

I una quarta, decisiva: EXISTS és correlacionat a la pràctica sempre. Un EXISTS sense correlació —WHERE EXISTS (SELECT 1 FROM comandes)— és TRUE o FALSE per a totes les files per igual, així que retorna tota la taula o cap fila. No és cap error, però tampoc no serveix de res. La condició co.client_id = c.id és la que fa la feina: converteix "hi ha comandes?" en "hi ha comandes d'aquest client?".

Aquestes quatre escriptures són exactament equivalents i produeixen el mateix pla d'execució:

L'última n'és la demostració: 1/0 és una divisió per zero i no dona error, perquè PostgreSQL no avalua mai la llista de columnes d'un EXISTS. Només comprova si la consulta produeix files. La projecció es descarta abans de calcular-se.

D'aquí la fórmula SELECT 1, la que veuràs al 90 % del codi professional: és la manera més curta de dir "no m'interessa el contingut". SELECT * és igual de correcte i algunes guies el prefereixen perquè subratlla el mateix. Tria'n una i sigues coherent; en aquest curs fem servir SELECT 1.

El que sí que importa dins de l'EXISTS és el WHERE: la correlació. Un EXISTS el WHERE del qual no esmenta la fila externa està mal plantejat gairebé segur.

Productes amb almenys una ressenya. L'EXISTS no diu quantes ni de quina puntuació: només que n'hi ha alguna.

(5 primeres de 9 files; les altres quatre són els productes 12, 15, 16 i 18.) 9 dels 20 productes. Compara-ho amb un INNER JOIN sobre ressenyes: aquell hauria retornat 12 files, una per ressenya, amb l'oli, l'arròs i la crema repetits. EXISTS no multiplica mai files, i aquest és el seu avantatge més pràctic enfront del JOIN.

Comandes que contenen algun producte de cosmètica natural. Aquí la subconsulta porta el seu propi JOIN:

6 comandes de 20. La comanda 9 conté dos articles de cosmètica (xampú i bàlsam) i apareix una sola vegada: amb JOIN n'haurien sortit 7 files i hauria calgut un DISTINCT. Empleats que han gestionat alguna comanda, mateix patró:

3 dels 8 empleats, exactament els que anunciava 01-06.

Nota de dialecte: a PostgreSQL EXISTS retorna un boolean de debò, així que el pots fer servir com a columna: SELECT c.id, EXISTS (SELECT 1 FROM comandes co WHERE co.client_id = c.id) AS ha_comprat FROM clients c retorna true/false per als 15 clients. SQL Server i Oracle no ho permeten i obliguen a embolcallar-ho en un CASE WHEN EXISTS (...) THEN 1 ELSE 0 END. MySQL 8 i SQLite sí que ho accepten, retornant 1 o 0.

NOT EXISTS és la negació literal: TRUE quan la subconsulta no produeix cap fila. És l'eina natural per a totes les preguntes amb "sense", "mai" o "cap", i respon els quatre buits deliberats de BotigaVerda amb la mateixa plantilla.

I la mateixa plantilla, canviant només la taula de dins, dona els productes mai venuts:

Amb NOT EXISTS (SELECT 1 FROM ressenyes AS r WHERE r.producte_id = p.id) sobre productes obtens els 11 productes sense ressenya, i amb NOT EXISTS (SELECT 1 FROM comandes AS co WHERE co.empleat_id = e.id) sobre empleats, els 5 empleats sense comandes (Rosa, Andrés, Beatriz, Irene i Daniel).

Quatre preguntes de negoci, una sola plantilla. Aquesta uniformitat és la raó principal per preferir NOT EXISTS: no cal decidir quina columna comprovar amb IS NULL, ni amoïnar-se pels nuls, ni recordar quina taula va a l'esquerra.

Ara la demostració central del mòdul. La mateixa pregunta, dues escriptures, dos resultats diferents.

La primera retorna zero files i la segona cinc. La causa, que ja vas diagnosticar a 07-01, són les 10 comandes del canal web amb empleat_id a NULL: la llista és {4, 5, 6, NULL} i 1 <> NULL és UNKNOWN, així que NOT IN no pot ser TRUE per a cap fila. NOT EXISTS no pateix el mateix perquè no compara el valor extern amb una llista: pregunta si la subconsulta produeix files. Per a l'empleada 1, SELECT 1 FROM comandes WHERE empleat_id = 1 no en produeix cap —les files amb empleat_id nul tampoc no satisfan aquella igualtat i es descarten com qualsevol altra que no casi— i NOT EXISTS és TRUE. La lògica de tres valors actua dins de la subconsulta, on només decideix quines files es retornen, i mai no s'escapa al predicat.

La regla, en una frase: NOT EXISTS és segur amb nuls i NOT IN no. Si tries NOT EXISTS per costum, mai no t'hauràs de preguntar si la columna de la subconsulta admet nuls.

A 03-07 es va anunciar que les quatre escriptures es compararien al mòdul 7. Aquí les tens, resolent la mateixa pregunta —clients que no han comprat mai— i retornant totes les mateixes 3 files (13, 14 i 15):

La recomanació del curs: fes servir NOT EXISTS. És segur, universal, deixa disponibles totes les columnes de la taula externa i es llegeix igual que la pregunta de negoci. L'anti-join és igual d'idiomàtic i de vegades més natural si ja estaves unint aquelles taules. EXCEPT, només quan comparis conjunts de la mateixa forma i no necessitis més columnes. I NOT IN, únicament si garanteixes que la columna de la subconsulta és NOT NULL — i encara aleshores, no hi guanyes res. Aquesta taula es tanca i s'amplia amb el criteri de rendiment a 07-05.

És temptador escriure "n'hi ha algun?" com a "el recompte és més gran que zero?". Funciona i dona el mateix resultat —12 files totes dues—, però no la mateixa feina:

EXISTS fa curtcircuit: tan bon punt troba una fila, deixa de buscar; COUNT no pot, perquè per saber quantes n'hi ha les ha de veure totes. Amb 20 comandes la diferència és inmesurable; amb taules reals separa una consulta instantània d'un recorregut complet. I hi ha un tercer argument, el més important en el dia a dia: EXISTS diu el que vols dir. El mateix amb la negació: COUNT(*) = 0 és NOT EXISTS escrit d'una manera més cara.

Arribem al problema clàssic. La pregunta sembla innocent:

Quins clients han comprat productes de totes les categories?

I no es pot respondre amb EXISTS a seques, perquè EXISTS parla d'"algun", no de "tots". El truc és reformular la frase fins a convertir el "tots" en dos "caps" encadenats:

Aquesta reformulació —que en lògica és ∀x P(x) ≡ ¬∃x ¬P(x)— es tradueix literalment a SQL:

Cap client, i el resultat no és decebedor sinó diagnòstic: Complements no ha venut mai (0,00 € a les xifres del curs), així que ningú no pot haver comprat de les sis categories. La consulta és correcta; el que falla és la pregunta. Reformulada sobre les quatre categories principals —Alimentació, Cosmètica natural, Llar sostenible i Begudes— n'hi ha prou d'acotar la subconsulta intermèdia:

Una sola clienta. La Lucía és l'única que ha comprat de les quatre categories principals, i ho ha aconseguit amb tres comandes molt diferents entre si: la 1 (oli, arròs i infusió), la 5 (detergent, fregall i bosses) i la 15 (mel, xampú i fregall). És exactament el perfil que un equip de màrqueting voldria identificar: el client que ha explorat el catàleg sencer.

Llegeix-la de dins cap enfora, i en tres frases:

I un advertiment: hi ha dues correlacions en joc. La interna fa servir c.id (dos nivells cap enfora) i cat.id (un). Si n'oblides una, la consulta continua essent vàlida i retorna una barbaritat. Prova la subconsulta interna amb valors fixos (c.id = 1, cat.id = 3) abans d'acoblar les tres capes.

Nota: hi ha una alternativa més llegible amb GROUP BY i HAVING COUNT(DISTINCT p.categoria_id) = 4, més curta i sovint més ràpida. La doble negació mereix entendre's perquè és l'única que funciona quan el conjunt de referència no és un simple recompte —"totes les categories actives", "tots els productes d'un catàleg que canvia"— i perquè és la fórmula que reconeixeràs en llegir codi d'altri.

• Escriure un EXISTS sense correlació. WHERE EXISTS (SELECT 1 FROM comandes) és TRUE per a totes les files i retorna la taula sencera: la condició que enllaça amb la fila externa és l'essencial.
• Creure que SELECT * dins d'un EXISTS és més lent. No ho és: la llista de columnes no s'avalua mai, i SELECT 1 i SELECT * produeixen el mateix pla.
• Fer servir NOT IN sobre una columna que admet nuls. Zero files, sense cap avís; NOT EXISTS no té aquest problema mai. I escriure COUNT(*) > 0 compta totes les files per respondre una cosa que es contesta amb la primera.
• Posar un ORDER BY o un LIMIT dins d'un EXISTS. No canvien el resultat i afegeixen feina. I confondre NOT EXISTS amb EXISTS (... WHERE NOT ...). "No té cap comanda lliurada" és NOT EXISTS (... estat = 'lliurat'); "té alguna comanda no lliurada" és EXISTS (... estat <> 'lliurat'). Són preguntes diferents.
• Consell: tradueix la pregunta paraula per paraula. "Clients que han comprat" → EXISTS. "Clients que no han comprat mai" → NOT EXISTS. "Clients que han comprat de totes" → NOT EXISTS ( ... NOT EXISTS ( ... )). I aquí, compte a no oblidar una de les dues correlacions: no dona error i el resultat és brossa.
• Consell: fes servir EXISTS quan el JOIN t'obligaria a un DISTINCT. Si només vols saber si hi ha relació i no necessites dades de l'altra taula, evita la multiplicació de files d'arrel. I prova sempre les subconsultes amb valors fixos —substitueix c.id per 1— abans de muntar la correlació.

Atenció al client vol una campanya d'opinions dirigida a clients que han comprat alguna vegada però no han escrit mai una ressenya. Escriu la consulta amb EXISTS i NOT EXISTS a la mateixa clàusula WHERE, mostrant id, nom complet, país i nombre de comandes.

Després respon: quants clients queden fora per cadascuna de les dues condicions?

Un company necessita les comandes que no contenen cap producte d'alimentació i ha escrit això:

1. Explica per què està malament i quina pregunta respon realment.
2. Escriu-la correctament amb NOT EXISTS i dona el nombre de files.
3. La podries resoldre amb un anti-join? I amb NOT IN? Justifica si serien segures.

Compres vol saber quins proveïdors no tenen cap producte sense vendre: aquells dels quals totes les seves referències s'han venut almenys una vegada. Escriu-ho amb doble NOT EXISTS. (Pista: mateixa estructura que la divisió relacional, però el conjunt de referència són els productes del mateix proveïdor.)

Solució 1

3 clients. El repartiment dels 15:

Els tres són objectius perfectes: han comprat, estan satisfets o no ho sabem, i no els hem demanat mai la seva opinió. Fixa't que les dues condicions són inseparables: només amb NOT EXISTS (ressenyes) en sortirien 6, incloent-hi tres que no han comprat res.

Solució 2

1. Què està malament. La consulta respon a "comandes que contenen algun producte que no és d'alimentació", que és gairebé el contrari. Una comanda amb oli (categoria 1) i kombutxa (categoria 4) té una línia que compleix categoria_id <> 1, així que hi apareix — encara que sí que porta alimentació. L'error és de quantificador: el WHERE d'un JOIN filtra línies, i la pregunta parla de comandes. El DISTINCT dissimula el símptoma (les files repetides) sense tocar-ne la causa.

2. La versió correcta:

(5 primeres de 10 files; les altres són les comandes 12, 13, 16, 18 i 19.) 10 comandes de 20 no porten ni un sol producte d'alimentació. La consulta original en retornava 18 —totes les que tenen alguna línia d'una altra categoria—, i vuit d'elles sí que compren alimentació. Observa el canvi de lògica: la condició p.categoria_id = 1 s'escriu en positiu dins de l'EXISTS, i la negació l'aporta el NOT. Aquest és el patró de tota pregunta amb "cap".

3. Amb anti-join, sí: FROM comandes co LEFT JOIN (linies_comanda lc JOIN productes p ON p.id = lc.producte_id AND p.categoria_id = 1) ON lc.comanda_id = co.id WHERE lc.id IS NULL funciona, però cal portar la condició de categoria a l'ON (si va al WHERE degrada el LEFT a INNER, 03-03), cosa que la fa força més fràgil d'escriure. Amb NOT IN també funcionaria —co.id NOT IN (SELECT lc.comanda_id FROM linies_comanda lc JOIN productes p ON ... WHERE p.categoria_id = 1)— i seria segura perquè linies_comanda.comanda_id és NOT NULL. Però continuaries depenent d'una garantia de l'esquema que demà pot canviar: NOT EXISTS no depèn de res.

Solució 3

3 proveïdors de 5. Quadra amb els tres productes mai venuts: el 19 (Desodorant) és de Maison Nature, i el 13 (Espelmes) i el 20 (Espirulina) són d'EcoNordic Supplies. Aquests dos en queden fora; els altres tres han col·locat el seu catàleg sencer.

L'estructura és idèntica a la de la secció 8 amb una única diferència: el conjunt de referència està correlacionat (p.proveidor_id = pr.id) en lloc de ser la taula sencera de categories. És la variant més útil del patró a la pràctica, i la seva lectura literal és "no hi ha cap producte seu del qual no existeixi cap venda".

EXISTS és la subconsulta que més escriuràs:

• EXISTS és un predicat: retorna TRUE tan bon punt la subconsulta produeix una fila, i mai NULL. No mira les columnes del SELECT intern —d'aquí la fórmula SELECT 1, i fins i tot SELECT 1/0 funciona— i fa curtcircuit.
• A la pràctica sempre és correlacionat: la condició que enllaça amb la fila externa és el que li dona sentit.
• No multiplica files. Els 9 productes amb ressenya surten 9 vegades, no 12; les 6 comandes amb cosmètica surten 6, no 7. On un JOIN demanaria DISTINCT, EXISTS no el necessita.
• NOT EXISTS resol els quatre buits de BotigaVerda amb una sola plantilla: 3 clients sense comprar, 3 productes sense vendre, 11 productes sense ressenya i 5 empleats sense comandes. I sobretot: és segur amb nuls i NOT IN no — la mateixa pregunta dona 0 files amb NOT IN i 5 amb NOT EXISTS, per les 10 comandes web amb empleat_id nul.
• Coneixes les quatre formes de respondre "què no casa" —NOT EXISTS, NOT IN, anti-join i EXCEPT— amb les seves diferències en nuls, columnes disponibles, duplicats i portabilitat; la recomanació és NOT EXISTS. I saps traduir un "tots" amb la doble negació: cap client no ha comprat de les sis categories (Complements no va vendre mai) i només la Lucía ho ha fet de les quatre principals.

A la lliçó següent, subconsultes a SELECT, FROM i WHERE, deixaràs de mirar el què per mirar el on: què canvia segons la clàusula on col·loques la subconsulta, per què una taula derivada al FROM necessita àlies obligatòriament, com s'agrega dues vegades seguides per arribar per fi al tiquet mitjà de 36,40 € des del seu origen, i com es creuen dos agregats de granularitat diferent perquè els 727,95 € de producte i els 118,25 € de ports quadrin sense inflar-se.