A la lliçó anterior vas perdre tres clients, tres productes i deu comandes sense que ningú t'avisés. El LEFT JOIN és l'eina que els recupera. La seva regla és una sola frase: conserva totes les files de la taula esquerra, casin o no casin amb la dreta; quan no casen, les columnes de la dreta s'omplen amb NULL.

Sona senzill, i ho és. Però el LEFT JOIN amaga el parany més famós de tot l'SQL intermedi: posar una condició a l'ON o al WHERE deixa de ser indiferent. La diferència no dona error, no dona avís, i produeix dos resultats diferents que semblen igualment plausibles. Mitja lliçó està dedicada al fet que no hi caiguis mai.

1. La regla del LEFT JOIN i el diagrama de conjunts
2. LEFT JOIN = LEFT OUTER JOIN
3. D'on surten exactament els NULL
4. Els tres casos reals de BotigaVerda
5. El patró anti-join: trobar el que no casa
6. El parany: condició a l'ON enfront de condició al WHERE
7. LEFT JOIN encadenats
8. Quan la taula dreta té diverses files per cada esquerra
9. Errors habituals i consells
10. Exercicis
11. Conclusió

Com a flux de files, ampliant el diagrama de 03-02:

Aquest pas extra —"s'afegeixen igualment amb NULL a la dreta"— és el pas 1d de l'ordre lògic que vas veure a 03-01. Passa dins del FROM, després d'aplicar l'ON i abans del WHERE. Tot el que explica aquesta lliçó es dedueix d'aquest fet.

La paraula "esquerra" és literal: és la taula que escrius abans de la paraula LEFT JOIN.

D'aquí en surt la conseqüència més important per escriure bé: el LEFT JOIN no és commutatiu. clients LEFT JOIN comandes conserva els 15 clients; comandes LEFT JOIN clients conserva les 20 comandes. Són preguntes diferents.

Com triar el costat esquerre: digues-te la pregunta de negoci en veu alta i busca el substantiu que ha d'aparèixer complet a l'informe. "Vull tots els clients, amb les seves comandes si en tenen" → clients és l'esquerra. "Vull totes les comandes, amb el seu comercial si en tenen" → comandes és l'esquerra.

Les dues escriptures són idèntiques:

OUTER és opcional i pràcticament ningú no l'escriu. L'adjectiu extern (outer) descriu tota la família: LEFT, RIGHT i FULL són joins externs perquè conserven files que queden fora de l'emparellament; INNER és el join intern perquè només retorna el que queda dins.

En aquest curs escrivim LEFT JOIN.

Aquest punt es malinterpreta sovint, així que convé ser precisos: els NULL que apareixen en un LEFT JOIN no eren a la base de dades. Els fabrica el motor en el moment de construir el resultat.

Quan una fila de l'esquerra no troba parella, PostgreSQL la incorpora igualment al resultat i omple totes les columnes de la taula dreta amb NULL. No només la de la clau: totes.

D'aquí en surten dues conseqüències que faràs servir constantment:

1. Pots detectar l'absència mirant qualsevol columna de la dreta. Si co.id IS NULL en un LEFT JOIN des de clients, és que no hi va haver parella. És la base de l'anti-join de la secció 5.
2. Convé mirar una columna NOT NULL de la dreta. Si triessis una columna que pot ser nul·la de debò a les dades, no sabries distingir "no hi va haver parella" de "hi va haver parella i aquella dada era buida". Per això l'anti-join s'escriu sempre contra la clau primària de la taula dreta: co.id, lc.id, r.id. Una PK mai no és NULL de forma legítima.

Un tercer efecte, més subtil, és l'aritmètica amb nuls que vas veure a 02-02: qualsevol operació amb NULL dona NULL. Si calcules co.despeses_enviament * 2 sobre una fila sense parella, el resultat és NULL, no zero. La funció COALESCE que ho resol s'estudia a 06-04; el tractament complet dels nuls, a 04-03.

Els tres buits deliberats del conjunt de dades existeixen precisament per a aquesta lliçó.

23 files. Compara amb 03-02:

Ja hi són la Núria, l'Hugo i la Inés, amb tota la part de comandes a NULL. L'aritmètica és exacta i val la pena interioritzar-la: el resultat d'un LEFT JOIN té tantes files com l'INNER JOIN més una fila per cada fila esquerra òrfena.

Mateix patró, ara al catàleg. Retallem a cinc productes per veure el contrast amb claredat:

Els productes 12 i 18 s'han venut tres vegades cadascun i ocupen tres files. Els productes 13, 19 i 20 no s'han venut mai i ocupen una fila amb tot a NULL.

Sense el WHERE de retall, la consulta completa retorna 50 files: les 47 de linies_comanda més les 3 dels productes mai venuts.

El tercer cas és diferent dels dos anteriors, i convé fixar-se en el matís. Aquí no falta la fila filla: la clau forana és NULL.

20 files: les 20 comandes. Enfront de les 10 que retornava l'INNER JOIN de 03-02. Els NULL de la columna comercial signifiquen aquí una cosa perfectament llegible per al negoci: comanda entrada per la web, sense comercial assignat. Deu de vint, exactament la proporció que 01-06 descrivia.

Fixa't en un detall: comercial val NULL per partida doble. Primer perquè co.empleat_id és NULL i no casa amb res; i segon perquè, encara que casés, la concatenació e.nom || ' ' || e.cognoms sobre columnes nul·les dona NULL (el parany de 02-02).

Fins ara hem fet servir el LEFT JOIN per conservar el que no casa. Ara el farem servir per quedar-nos només amb això, que és una de les consultes més demanades a qualsevol empresa: clients inactius, productes sense rotació, factures sense cobrar, usuaris sense verificar.

La tècnica es diu anti-join i s'escriu en dos moviments:

1. Un LEFT JOIN que conserva tot el de l'esquerra.
2. Un WHERE <clau primària de la dreta> IS NULL que es queda només amb les files que no van trobar parella.

Tres files. Aquesta és exactament la llista que demanaria el departament de màrqueting per llançar una campanya de primera compra.

Tres files, i amb un diagnòstic diferent per a cadascuna: les espelmes no es venen perquè no hi ha estoc; l'espirulina perquè està descatalogada (actiu = false); el desodorant té estoc i està actiu, així que el seu problema és comercial. Un JOIN ben plantejat no només retorna dades: apunta a la causa.

Sobre IS NULL: és l'operador correcte per preguntar per nuls, perquè = NULL mai no és cert (ho vas veure a 02-03 amb empleat_id = NULL retornant 0 files). El seu tractament complet, juntament amb IS NOT NULL i COALESCE, és la lliçó 04-03.

Nota: al mòdul 7 veuràs dues altres formes d'escriure el mateix: NOT EXISTS amb una subconsulta correlacionada, i NOT IN. Totes tres tenen el mateix objectiu i diferent comportament davant dels nuls. L'anti-join amb LEFT JOIN és el que pots escriure avui, i és perfectament idiomàtic.

Aquí hi ha el contingut més important de la lliçó. Para atenció a la pregunta, perquè la clau és allà:

"Dona'm tots els clients, amb les seves comandes lliurades."

Fixa't en el "tots": l'informe ha de llistar els 15 clients, tinguin o no comandes lliurades. L'escriurem de les dues maneres possibles.

18 files i els 15 clients presents. Els que no tenen cap comanda lliurada apareixen amb NULL: la Núria, l'Hugo i la Inés perquè no han comprat mai, i Ana Belmonte Roca perquè les seves dues comandes estan cancellat i pagat, cap de lliurada. L'Ana és el cas més interessant: existeix a comandes, però cap de les seves comandes no supera la condició de l'ON.

Canviem exactament dues paraules: AND passa a ser WHERE.

14 files i només 12 clients. Han desaparegut l'Ana, la Núria, l'Hugo i la Inés. El LEFT JOIN s'ha comportat com un INNER JOIN.

Torna a l'ordre lògic de 03-01 i segueix el recorregut de la fila de la Núria a cada versió:

El pas 1d sí que afegeix la Núria a les dues versions. La diferència és després: a la versió B, el WHERE avalua co.estat = 'lliurat' sobre una fila el co.estat de la qual és NULL. I NULL = 'lliurat' no és FALSE, és NULL, que tampoc no és TRUE, així que la fila es descarta. És el mateix mecanisme de 02-03 actuant en un lloc nou.

La regla que cal gravar: en un LEFT JOIN, qualsevol condició al WHERE sobre una columna de la taula dreta converteix el LEFT en un INNER, perquè les files omplertes amb NULL no la poden satisfer.

Les tres són vàlides: cadascuna respon a una pregunta diferent. L'error no és fer servir el WHERE, és fer-lo servir creient que fa el de l'ON.

Un cas que sí que és segur: una condició al WHERE sobre una columna de la taula esquerra no degrada res. WHERE c.pais = 'Espanya' filtra clients, i els que passin el filtre conserven el seu NULL a la dreta sense problema. El perill és exclusivament el de les columnes de la dreta.

Amb tres o més taules el LEFT JOIN manté la seva lògica, però apareix una regla que sorprèn: un INNER JOIN col·locat després d'un LEFT JOIN anul·la l'efecte del LEFT.

Volem "tots els clients, amb les seves comandes i el comercial de cada comanda". Escrit amb LEFT als dos salts:

I ara el mateix camí amb un INNER JOIN al segon salt:

El raonament: els JOIN es resolen d'esquerra a dreta, així que (clients LEFT JOIN comandes) produeix 23 files; sobre aquestes 23, l'INNER JOIN amb empleats exigeix que co.empleat_id casi amb un empleat. Les 3 files de clients sense comandes tenen co.empleat_id = NULL i cauen; les 10 files de comandes web tenen empleat_id = NULL i també cauen. En queden 10.

Regla pràctica: un cop has obert una cadena amb LEFT JOIN, tots els salts posteriors sobre aquella branca han de ser LEFT JOIN. Un sol INNER al mig desfà tota la feina, i ho fa sense cap avís.

És un error molt fàcil de cometre en ampliar una consulta existent: algú afegeix JOIN productes ON ... al final d'una consulta que començava amb LEFT JOIN, i l'informe perd files d'un dia per l'altre sense que ningú toqui el LEFT.

El LEFT JOIN no protegeix de la multiplicació de files de 03-02. Continua havent-hi una fila per parella:

Quatre productes produeixen set files: dues ressenyes per als productes 1, 2 i 6, i una fila buida per al 4. Sobre el catàleg sencer, productes LEFT JOIN ressenyes retorna 23 files: les 12 ressenyes més els 11 productes sense cap.

El compte general d'un LEFT JOIN és sempre aquest:

I de nou l'avís del mòdul 4: si sobre el primer cas sumessis c.data_registre, o comptessis clients, obtindries xifres inflades, perquè la Lucía hi apareix tres vegades. Un LEFT JOIN conserva files; no les deduplica.

• Posar una condició sobre la taula dreta al WHERE. Degrada el LEFT JOIN a INNER JOIN sense dir res. És *l'*error d'aquesta lliçó: 18 files contra 14, i quatre clients desapareguts.
• Barrejar un INNER JOIN després d'un LEFT JOIN. Mateix efecte, en cadena: 23 files es converteixen en 10.
• Escriure les taules a l'inrevés. comandes LEFT JOIN clients no és clients LEFT JOIN comandes. El LEFT JOIN no és commutatiu; l'esquerra és la que es conserva sencera.
• Fer l'anti-join contra una columna que admet nuls. WHERE co.empleat_id IS NULL no distingeix "no hi va haver parella" de "hi va haver parella amb la dada buida". Compara sempre contra la clau primària de la dreta.
• Intentar un anti-join amb INNER JOIN. INNER JOIN ... WHERE dreta.id IS NULL retorna sempre zero files: l'INNER ja ha llençat aquelles files.
• Fer servir = NULL en lloc d'IS NULL. WHERE co.id = NULL retorna zero files sempre (02-03).
• Suposar que NULL al resultat significa zero. co.despeses_enviament és NULL, no 0.00, per a un client sense comandes. Operar-hi propaga el nul (COALESCE, a 06-04).
• Oblidar que el LEFT JOIN continua multiplicant files. Conserva els orfes, però no impedeix que un client amb tres comandes ocupi tres files.
• Consell: escriu primer l'INNER JOIN, comprova el recompte, i després canvia'l a LEFT. La diferència entre tots dos recomptes et diu exactament quants orfes hi ha.
• Consell: per a l'anti-join, digues la pregunta en veu alta. "Clients sense comandes", "productes mai venuts", "factures sense cobrar". El "sense" i el "mai" són el senyal que toca LEFT JOIN ... WHERE ... IS NULL.
• Consell: sospita de qualsevol WHERE que anomeni la taula dreta d'un LEFT JOIN. Llevat que sigui un IS NULL deliberat, gairebé sempre hauria de ser a l'ON.

L'equip de producte vol saber quines referències del catàleg no han rebut cap ressenya, per llançar una campanya de sol·licitud d'opinions. Escriu la consulta que retorni l'id, el nom, la categoria i el preu d'aquests productes, ordenats per id.

Després respon: per què el JOIN amb categories pot ser un INNER JOIN sense que això trenqui l'anti-join?

Logística necessita el llistat complet de les 20 comandes amb la informació de la seva devolució, si n'hi va haver: id de la comanda, data, estat, despeses d'enviament i —quan hi hagi devolució— el seu motiu i import.

1. Escriu la consulta.
2. Escriu després la variant que retorna només les comandes que no van tenir devolució, i indica quantes files dona.

Un company et passa aquesta consulta i et diu: "vull tots els clients amb les seves comandes pagades amb targeta, però em falten clients".

1. Explica què està fent realment aquesta consulta.
2. Corregeix-la perquè retorni el que ell vol.
3. Prediu quantes files retorna cada versió i quants clients diferents apareixen a cadascuna.

Solució 1

11 files, exactament els 11 productes sense ressenya que anunciava 01-06. Només 9 dels 20 productes han estat ressenyats alguna vegada.

Per què el JOIN amb categories pot ser INNER: perquè la secció 7 adverteix dels INNER JOIN que apareixen després d'un LEFT JOIN sobre la mateixa branca. Aquí no és el cas: categories penja de productes per una FK que apunta a una categoria que sempre existeix, així que aquell INNER JOIN no descarta cap producte. Les 20 files de partida continuen sent 20 abans d'aplicar el LEFT JOIN amb ressenyes. La regla precisa és: un INNER JOIN és segur mentre no pugui eliminar files de la taula protagonista.

(Si categoria_id admetés NULL amb dades reals nul·les, aquell INNER JOIN sí que perdria productes i caldria escriure'l també com a LEFT JOIN.)

Solució 2

1. Totes les comandes amb la seva devolució, si n'hi va haver:

Retorna 20 files: les 3 amb devolució (comandes 6, 10 i 13) mostren motiu i import; les altres 17 mostren *(null)* en aquestes dues columnes.

2. Només les comandes sense devolució (anti-join):

17 files = 20 comandes − 3 devolucions. Falten justament les comandes 6, 10 i 13.

Solució 3

1. Què fa realment. La condició co.metode_pagament = 'targeta' és al WHERE i s'aplica a una columna de la taula dreta. Tota fila de client sense comanda arriba al WHERE amb co.metode_pagament a NULL, i NULL = 'targeta' no és TRUE. Resultat: el LEFT JOIN es degrada a INNER JOIN i la consulta retorna només els clients que tenen almenys una comanda pagada amb targeta. És una pregunta legítima, però no la que ell volia.

2. Correcció: moure la condició a l'ON.

3. Predicció de files. Les comandes amb metode_pagament = 'targeta' són els ids 1, 3, 5, 6, 8, 10, 11, 13, 15, 16 i 19: 11 comandes, de 9 clients diferents (la Lucía hi apareix tres vegades).

Els 6 clients que apareixen amb NULL a la versió corregida són: en Tiago (8), en Julien (10), en Diego (12), la Núria (13), l'Hugo (14) i la Inés (15). Els tres últims perquè no han comprat mai; els tres primers perquè van comprar, però pagant amb PayPal o transferència.

El LEFT JOIN és, a la pràctica, el JOIN que més problemes de negoci resol:

• Conserva totes les files de la taula esquerra, casin o no, omplint amb NULL les columnes de la dreta. LEFT JOIN i LEFT OUTER JOIN són el mateix.
• Els NULL els fabrica el motor en construir el resultat; no eren a les dades. Per això pots detectar l'absència mirant la clau primària de la taula dreta.
• Has recuperat els tres buits de BotigaVerda: els clients 13, 14 i 15 (23 files en comptes de 20), els productes 13, 19 i 20 (50 files en comptes de 47) i les 10 comandes web (20 files en comptes de 10).
• El patró anti-join LEFT JOIN ... WHERE dreta.id IS NULL respon a les preguntes amb "sense" i "mai": tres clients que no han comprat, tres productes que ningú no ha venut, onze productes sense ressenyes.
• Saps que una condició sobre la taula dreta al WHERE degrada el LEFT JOIN a INNER JOIN: 18 files i 15 clients contra 14 files i 12 clients, amb la mateixa pregunta escrita de dues formes. Si vols "tots els X amb els seus Y que compleixin Z", la condició va a l'ON.
• Un INNER JOIN encadenat després d'un LEFT JOIN anul·la el seu efecte: 23 files es queden en 10. Oberta una branca amb LEFT, continua amb LEFT.
• El LEFT JOIN no deduplica: continua multiplicant files quan la dreta en té diverses per cada esquerra.

A la lliçó següent, RIGHT JOIN, veurem el simètric exacte del que acabes d'aprendre. Comprovaràs que A RIGHT JOIN B i B LEFT JOIN A retornen exactament el mateix, entendràs per què la majoria de guies d'estil prefereixen el LEFT tot i això, i veuràs per fi Irene Salvador Mira i Daniel Vercher Lluch, els dos empleats que mai no han gestionat una comanda.