L'INNER JOIN és el JOIN per defecte, el que ja vas fer servir sense anomenar-lo a la lliçó anterior i el que escriuràs en vuit de cada deu consultes de la teva vida professional. La seva regla és d'una simplicitat absoluta: només sobreviuen les files que troben parella a banda i banda. Tota la resta desapareix, en silenci i sense avís.

I aquí hi ha el parany. Un INNER JOIN mai no dona error per perdre files; simplement et retorna menys de les que esperaves. En aquesta lliçó aprendràs no només a escriure'l, sinó a predir quantes files retorna i per què, que és l'habilitat que distingeix qui entén els JOIN de qui els copia. Veuràs desaparèixer tres clients de BotigaVerda i la meitat de les seves comandes, i entendràs que aquestes desaparicions són la definició mateixa de l'operador.

1. INNER JOIN enfront de JOIN a seques
2. El diagrama de conjunts
3. Exemples progressius sobre BotigaVerda
4. La consulta canònica de quatre taules
5. Quines files es perden i per què
6. Condicions addicionals: ON enfront de WHERE
7. Claus no úniques i la multiplicació de files
8. L'ordre de les taules no canvia el resultat
9. Errors habituals i consells
10. Exercicis
11. Conclusió

Les dues formes següents són exactament la mateixa consulta:

INNER és opcional perquè l'estàndard SQL defineix el JOIN sense qualificar com a intern. És el contrari del que passa amb LEFT OUTER JOIN, on l'opcional és OUTER.

Quina escriure? Hi ha dues escoles. Una prefereix JOIN per brevetat; l'altra prefereix INNER JOIN perquè, en una consulta que barreja diversos tipus, veure la paraula INNER al costat d'un LEFT deixa explícita la intenció i evita que algú pensi que s'ha oblidat un modificador.

Convenció del curs: escriurem JOIN quan tota la consulta sigui interna, i INNER JOIN explícit quan convisqui amb un LEFT, un RIGHT o un FULL a la mateixa consulta. És la pràctica més estesa en equips professionals.

La forma clàssica de visualitzar els JOIN és amb dos conjunts que se solapen. L'INNER JOIN retorna només la intersecció:

I com a flux de files, reprenent el model mental de 03-01:

Aquest quadre vermell —"es descarten a banda i banda"— és tot el que cal recordar de l'INNER JOIN. Les lliçons 03-03, 03-04 i 03-05 consisteixen precisament a recuperar el que aquí es llença.

Ja ho vas veure a 03-01; ho recuperem com a punt de partida, ara ampliat amb el proveïdor. Tres taules, dos ON:

Fixa't en un detall que només és possible amb JOIN: hem filtrat pel nom de la categoria, no pel seu id. Abans d'aquest mòdul hauries hagut de buscar a mà que "Cosmètica natural" és la categoria 2 i escriure WHERE categoria_id = 2. Ara la consulta es llegeix com es pensa.

La pregunta amb què vam tancar el mòdul 2: qui va fer cada comanda.

20 files, les mateixes que té comandes. Ja hi és per fi "Camille Dubois" on abans hi havia un 9.

Dues observacions:

• Els noms es repeteixen: Lucía Martínez Soler apareix tres vegades perquè va fer tres comandes (1, 5 i 15). És normal: unim pel costat "molts" de la relació 1:N.
• La concatenació c.nom || ' ' || c.cognoms és la de 02-02, amb el seu advertiment sobre NULL inclòs. Aquí és segura perquè totes dues columnes són NOT NULL.

(10 primeres de 47 files.)

Aquí es veu una cosa que al mòdul 2 només podíem intuir: la línia 1 té preu_unitari 11.95, mentre que l'oli costa avui 12,50 €. És el preu històric del qual parlava 01-06. Un error clàssic seria calcular l'import amb p.preu en comptes de lc.preu_unitari: obtindries 25,00 € en lloc de 23,90 € i estaries reescrivint la història comercial de l'empresa.

Regla del curs: l'import d'una línia es calcula sempre amb lc.preu_unitari, mai amb p.preu. La taula productes diu quant costa avui; linies_comanda diu quant es va cobrar aleshores.

Aquesta és la consulta més important del curs. Respon a "què va comprar cada client i per quant?" i necessita quatre taules: el detall és a linies_comanda, el client penja de comandes i el nom del producte de productes.

Observa la forma del camí: no és una cadena lineal, és una estrella amb linies_comanda al centre. D'ella en surten dues branques: una cap a la comanda i el seu client, una altra cap al producte. Això és normal quan la taula de partida té diverses claus foranes.

(12 primeres de 47 files.)

47 files: una per línia de comanda. Desa aquesta consulta, perquè és la base de gairebé tot el que ve després. Al mòdul 4 hi afegiràs GROUP BY c.id i SUM(import) per respondre "quant ha gastat cada client?"; al mòdul 7 la faràs servir com a subconsulta; al mòdul 10 la convertiràs en una vista. L'esquelet no canvia.

Quatre taules, tres condicions ON. Torna a comprovar-se la regla de 03-01: N taules, N-1 emparellaments.

Arribem al cor de la lliçó. Un INNER JOIN descarta silenciosament les files sense parella, i a BotigaVerda això té dues conseqüències molt visibles.

clients té 15 files. Vegem qui sobreviu a un INNER JOIN amb comandes:

12 files, no 15. Falten els clients 13 (Núria Bosch Ferrer), 14 (Hugo Iglesias Pardo) i 15 (Inés Carrasco Vega). Per què? Perquè cap d'ells no ha fet cap comanda, així que al producte cartesià no existeix cap combinació en què co.client_id valgui 13, 14 o 15. La condició ON no les troba i desapareixen.

El balanç de recomptes:

Les tres xifres són diferents i totes tres són correctes: responen a preguntes diferents. Si et demanen "el llistat de clients amb la seva activitat" i lliures 12 de 15, has esborrat de l'informe tres persones.

Avís: aquest DISTINCT és exactament el símptoma del qual parlava 02-04. Apareix aquí perquè estem fent servir una eina —l'INNER JOIN— que no és l'adequada per a la pregunta "quins clients han comprat". L'eina correcta arriba a 03-03.

El mateix fenomen, des de l'altre costat. comandes.empleat_id admet NULL perquè les comandes web no porten comercial assignat:

10 files de 20. Han desaparegut les comandes 1, 3, 5, 7, 9, 11, 13, 15, 17 i 19: totes les que tenen empleat_id IS NULL.

El mecanisme és el que vas veure a 02-03 amb empleat_id = NULL: NULL no és igual a res, ni tan sols a un altre NULL. La condició ON co.empleat_id = e.id s'avalua a NULL (que no és TRUE) per a aquelles deu files, així que cap parella no les salva.

I aquí hi ha el dany real: la meitat de la facturació de BotigaVerda ha desaparegut de l'informe. Si la direcció pregunta "quantes comandes hem tingut aquest any?" i respons amb aquesta consulta, la teva resposta serà la meitat de la veritat.

Resumint, un INNER JOIN perd files per dos motius:

En tots dos casos la resposta és la mateixa família d'operadors, i per això existeixen les lliçons 03-03, 03-04 i 03-05. L'INNER JOIN no està malament: simplement respon a la pregunta "dona'm el que casa", i de vegades la pregunta del negoci és una altra.

Res no impedeix posar condicions extra dins de l'ON, més enllà de l'emparellament. Aquestes dues consultes demanen el mateix: les comandes de clients portuguesos.

Resultats idèntics. I no és casualitat: en un INNER JOIN són sempre equivalents. Torna al diagrama de l'ordre lògic de 03-01 i entendràs per què. En un JOIN intern no existeix el pas 1d —no es reintrodueix cap fila sense parella—, així que filtrar durant l'emparellament o just després produeix el mateix conjunt.

Si són equivalents, on posar-les? Per llegibilitat:

I ara l'avís més important d'aquesta lliçó: aquesta equivalència és EXCLUSIVA de l'INNER JOIN. En un LEFT JOIN, moure una condició de l'ON al WHERE canvia el resultat, i el canvia d'una manera que sembla una fallada de dades i no una fallada de consulta. La lliçó 03-03 ho demostra amb la mateixa pregunta escrita de les dues maneres i els seus dos resultats diferents. Agafa des d'ara el costum de separar emparellament i filtratge: quan arribis al LEFT JOIN, aquest costum et salvarà.

Fins ara tots els JOIN de la lliçó han conservat el nombre de files de la taula de partida. Això passa quan uneixes des del costat "molts" cap al costat "un" (de productes a categories, de comandes a clients): cada fila troba exactament una parella.

A l'inrevés, la cosa canvia.

Tres comandes han produït vuit files. La comanda 1 apareix tres vegades (té tres línies), la 2 dues vegades i la 3 tres vegades. Les dades de capçalera —data, estat, i també despeses_enviament si les haguéssim demanat— es repeteixen a cada línia.

La regla general:

En unir per una columna no única al costat dret, cada fila de l'esquerra es duplica tantes vegades com parelles trobi.

Això sembla inofensiu mentre només mires files de detall. Es torna perillós al mòdul 4, quan comencis a sumar. Imagina aquesta consulta:

Les despeses d'enviament de la comanda 1 són 4,95 € una vegada, però després del JOIN aquesta xifra apareix en tres files. La suma donaria 14,85 € només per a aquella comanda: el triple del real. I no hi haurà cap error, cap avís, només un número equivocat en un informe de direcció.

El mateix passaria en sumar salari després d'unir empleats amb comandes, o en comptar clients després d'unir clients amb linies_comanda.

Aprèn a detectar-ho ara: després de cada JOIN que afegeixis, pregunta't "quantes files de la dreta hi pot haver per cada fila de l'esquerra?". Si la resposta és "més d'una", qualsevol valor de l'esquerra que sumis després estarà multiplicat. Al mòdul 4 veuràs les tècniques per evitar-ho; de moment n'hi ha prou de reconèixer el símptoma.

Un truc de verificació que pots aplicar avui mateix: si el recompte de files de la teva consulta coincideix amb el de la taula de detall (47 per a linies_comanda), estàs al nivell de granularitat correcte per treballar amb imports de línia.

En un INNER JOIN, A JOIN B i B JOIN A retornen el mateix conjunt de files. L'operació és commutativa.

Totes dues retornen les mateixes 20 files. L'única cosa que pot canviar és l'ordre de les columnes si fas servir SELECT *, i l'ordre de les files si no hi poses ORDER BY (el de sempre des de 02-01).

Amb tres o més taules també és associativa: (A JOIN B) JOIN C equival a A JOIN (B JOIN C), sempre que les condicions ON estiguin ben posades.

Que sigui commutatiu té dues conseqüències pràctiques:

1. Escriu les taules en l'ordre en què s'entén la pregunta. Si l'informe és "comandes amb el seu client", comença per comandes. Si és "clients i les seves comandes", comença per clients. El resultat és el mateix i la consulta es llegeix millor.
2. L'optimitzador no et fa cas. PostgreSQL reordena lliurement els INNER JOIN per triar el pla més barat: pot començar per la taula més petita, o per la que tingui el filtre més selectiu, amb independència de com ho escriguis. El teu ordre és documentació per a humans, no una instrucció per al motor. (Ho veuràs al mòdul 8.)

I un advertiment per al que ve. El LEFT JOIN no és commutatiu: A LEFT JOIN B i B LEFT JOIN A retornen coses diferents. Allà l'ordre en què escrius les taules és part del significat, no de l'estil.

• Donar per fet que l'INNER JOIN conserva totes les files. Perd les que no casen a cap dels dos costats, sense dir res. Comprova el recompte contra la taula de partida.
• Oblidar que una FK amb NULL mai no casa. Les 10 comandes web desapareixen en unir amb empleats. NULL = 4 no és fals: és NULL, i NULL no és TRUE.
• Calcular l'import amb p.preu en comptes de lc.preu_unitari. Retorna el preu d'avui aplicat a una venda de fa un any. Amb la línia 1 la diferència és d'1,10 €; amb un catàleg real, de milers d'euros.
• Sumar valors de capçalera després d'unir amb el detall. SUM(co.despeses_enviament) després de JOIN linies_comanda compta cada comanda tantes vegades com línies tingui. És la causa núm. 1 d'informes inflats.
• Posar el DISTINCT com a pedaç. Si necessites DISTINCT per arreglar un JOIN, gairebé sempre el JOIN és el que està mal plantejat (02-04). Pensa quina granularitat vol de debò la pregunta.
• Creure que l'equivalència ON/WHERE és general. Només val per a INNER JOIN. En LEFT JOIN canvia el resultat (03-03).
• Emparellar per columnes del tipus correcte però del concepte equivocat. ON lc.producte_id = co.id no dona error, compara enters amb enters, i retorna dades sense sentit.
• Consell: valida cada JOIN per separat. Escriu primer FROM linies_comanda lc JOIN comandes co ON ... amb LIMIT 5, comprova-ho, i només llavors afegeix la tercera taula. Depurar una consulta de cinc taules escrita d'una tirada és un suplici.
• Consell: memoritza els tres recomptes de BotigaVerda. 20 productes, 20 comandes, 47 línies. Si una consulta de detall no retorna 47 files, saps immediatament que passa alguna cosa.
• Consell: posa nom als àlies de columna quan uneixis taules amb columnes homònimes. c.nom AS client i p.nom AS producte eviten que el resultat tingui dues columnes anomenades nom.

L'equip de producte vol revisar les opinions rebudes. Escriu una consulta que retorni, per a cada ressenya: el seu id, el nom del producte ressenyat, el nom complet de qui la va escriure, la puntuació i la data. Ordena per id de ressenya.

Després respon: quantes files retorna, i per què aquest número no coincideix amb el nombre de productes del catàleg?

Administració necessita el detall de les devolucions. Escriu una consulta que mostri, per a cada devolució: el seu id, la data, l'import, el motiu, l'id i l'estat de la comanda retornada, i el nom complet del client afectat.

Indica quantes taules has necessitat, quantes condicions ON i per què.

Camille Dubois (client 9) ha trucat reclamant el detall de tot el que ha comprat. Fent servir la consulta canònica de quatre taules de la secció 4, obtén el seu historial complet: comanda, data, estat de la comanda, producte, quantitat i import de la línia.

Després respon a aquestes dues preguntes:

1. Quantes files retorna i quantes comandes representen?
2. Si en comptes d'INNER JOIN entre comandes i clients haguessis unit comandes amb empleats, apareixerien les mateixes comandes de la Camille? Raona la resposta mirant les dades.

Solució 1

12 files, exactament les de ressenyes. Partim d'aquesta taula i les seves dues claus foranes (producte_id, client_id) són obligatòries i vàlides: cada ressenya troba un producte i un client, un i només un.

Per què no coincideix amb els 20 productes: perquè la consulta va de ressenyes a productes, no a l'inrevés. Només apareixen els 9 productes diferents que tenen alguna ressenya (l'oli, l'arròs i la crema hi apareixen dues vegades cadascun). Els 11 productes sense cap ressenya —entre ells el desodorant i les càpsules d'espirulina— no existeixen per a aquesta consulta. Per veure'ls caldria partir de productes i fer servir un LEFT JOIN: és just el que fa la lliçó següent.

Solució 2

Tres taules i dues condicions ON, seguint la regla N-1 de 03-01. En calen tres perquè el client no és assolible des de devolucions en un sol salt: devolucions només coneix comanda_id, i és comandes qui coneix client_id. El camí obligatori és devolucions → comandes → clients.

Un detall que valida les dades: la devolució 2 val 34,02 €, exactament l'import de la línia 24 (2 unitats de crema facial a 18,90 € amb 10 % de descompte). No és casualitat: es va retornar aquell producte concret de la comanda de la Camille.

Solució 3

1. 4 files que representen 2 comandes: la 10 (lliurada, dues línies) i la 20 (pendent, dues línies). És la multiplicació de files de la secció 7 en acció: el nombre de files és el de línies, no el de comandes. Si volguessis el nombre de comandes de la Camille, aquesta consulta no és l'eina: et caldria COUNT(DISTINCT co.id) del mòdul 4.

2. No. La comanda 10 té empleat_id = 4 (Óscar Peris Blasco) i apareixeria; la comanda 20 té empleat_id = 6 (Marc Estévez Roig) i també. En aquest cas concret sí que sortirien totes dues... però és pura sort: totes dues comandes de la Camille tenen comercial assignat. Prova amb Lucía Martínez Soler (client 1), les tres comandes de la qual —1, 5 i 15— són totes web amb empleat_id IS NULL: un INNER JOIN amb empleats faria desaparèixer el 100 % del seu historial. Aquest és el perill que motiva la lliçó següent.

L'INNER JOIN ja no té secrets:

• JOIN i INNER JOIN són el mateix. El curs fa servir JOIN en consultes purament internes i INNER JOIN explícit quan hi conviuen diversos tipus.
• Retorna només la intersecció: les files que troben parella a banda i banda. Tot el que no casa es descarta en silenci.
• Saps escriure cadenes de tres i quatre taules i tens la consulta canònica de detall de vendes (linies_comanda + comandes + clients + productes, 47 files), que reutilitzaràs als mòduls 4, 7 i 10.
• Saps què es perd: els clients 13, 14 i 15 en unir clients amb comandes (12 clients de 15), i les 10 comandes web en unir comandes amb empleats (10 files de 20). Les dues causes són la FK amb NULL i l'absència de fila filla.
• En un INNER JOIN, posar una condició extra a ON o a WHERE és equivalent; en un LEFT JOIN no ho serà, i aquest és el parany de la lliçó vinent.
• Unir cap al costat "molts" multiplica files: 20 comandes es converteixen en 47 en afegir linies_comanda. És la causa número u de les sumes inflades que veuràs al mòdul 4.
• L'INNER JOIN és commutatiu i associatiu: l'ordre de les taules és qüestió de llegibilitat, no de significat. El LEFT JOIN no ho serà.

A la lliçó següent, LEFT JOIN, recuperarem tot el que aquí hem llençat. Tornaran la Núria, l'Hugo i la Inés amb les seves comandes buides; tornaran les espelmes de soja, el desodorant i l'espirulina que ningú no ha comprat mai; tornaran les deu comandes web amb el seu comercial a NULL. Aprendràs el patró anti-join per respondre directament a "quins clients no han comprat mai?" i veuràs, amb la mateixa consulta escrita de dues formes, per què col·locar una condició a ON o a WHERE deixa de ser indiferent.