Aquests són els dos JOIN que més desconcerten qui comença, i per motius oposats. El SELF JOIN sembla impossible —com s'uneix una taula amb si mateixa sense entrar en un bucle?— i resulta ser l'única forma de recórrer les relacions reflexives que fa temps que veus des de 01-05: empleats.cap_id i clients.referit_per_id. El CROSS JOIN sembla un error —és el producte cartesià que a 03-01 presentàvem com a accident— i resulta ser una eina deliberada per generar combinacions completes.

Cap dels dos no és un "tipus" nou d'emparellament. El SELF JOIN és una tècnica que pot fer servir qualsevol tipus (INNER, LEFT...); el CROSS JOIN és el cas degenerat d'un JOIN sense condició.

1. SELF JOIN: unir una taula amb si mateixa
2. La jerarquia d'empleats
3. La xarxa de referits de clients
4. SELF JOIN no jeràrquic: parelles dins del mateix grup
5. Empleats de la mateixa ciutat
6. Jerarquies de profunditat arbitrària
7. CROSS JOIN: el producte cartesià deliberat
8. Quan és útil i quan és un accident
9. CROSS JOIN amb generate_series per a calendaris
10. Errors habituals i consells
11. Exercicis
12. Conclusió

Un SELF JOIN no té sintaxi pròpia. És un JOIN normal en què les dues taules són la mateixa:

Res de bucles infinits: el motor tracta cada aparició de la taula com una relació independent. És exactament el producte cartesià de 03-01, amb empleats als dos costats, filtrat per la condició de l'ON.

A 03-01 vam dir que els àlies de taula eren una comoditat. En un SELF JOIN són imprescindibles, i per una raó física: sense ells, el nom empleats designaria dues coses diferents alhora.

PostgreSQL ni tan sols intenta endevinar-ho: s'hi nega. Amb àlies diferents, cada aparició té identitat pròpia i tot funciona.

Convenció del curs: en un SELF JOIN, els àlies no s'abreugen per inicial sinó que es nomenen pel paper que fa cada còpia. empleats AS e i empleats AS cap; clients AS c i clients AS ref; productes AS p1 i productes AS p2 quan els dos papers són simètrics. Un àlies com e1/e2 és acceptable en parelles simètriques, però e/cap és sempre més llegible que e1/e2 quan els papers són diferents.

empleats.cap_id és una clau forana que apunta a empleats.id: la relació reflexiva 1:N que vas dibuixar al diagrama ER de 01-06. Cada empleat té com a molt un cap, i un cap pot tenir diversos subordinats.

7 files de 8. Falta Rosa Alcázar Vives, la directora general, perquè el seu cap_id és NULL i no casa amb ningú. És exactament el mecanisme de 03-02: una FK nul·la mai no troba parella.

I és un resultat perillós, perquè sembla complet. Un organigrama que no inclou la directora general és un organigrama equivocat.

8 files: l'equip complet. El NULL de la Rosa significa "arrel de la jerarquia", i en un informe es presentaria com a "—" o "Direcció" amb COALESCE (06-04).

Regla: en un SELF JOIN jeràrquic, l'arrel de l'arbre sempre té la FK a NULL. Si vols que aparegui, el JOIN ha de ser LEFT. És l'error més freqüent en construir organigrames, arbres de categories o estructures de carpetes.

Canviant el sentit de la condició s'obté la relació inversa. N'hi ha prou de llegir l'ON a l'inrevés:

Retorna les mateixes 7 parelles, presentades des del punt de vista del cap. Fixa't que la condició ON és idèntica; l'única cosa que canvia és quin àlies es diu com i quines columnes es projecten. És la mateixa lliçó de 03-04 sobre LEFT i RIGHT: l'emparellament no canvia, canvia la lectura.

clients.referit_per_id funciona igual, però amb un matís de negoci diferent: aquí el NULL no significa "arrel d'una jerarquia" sinó "va arribar pel seu compte".

15 files: 8 clients referits i 7 sense referidor, exactament els recomptes que 01-06 anunciava.

La xarxa que dibuixen aquestes dades:

Màrqueting vol premiar els clients que més gent han portat. La consulta parteix del referidor:

Tres clients portats per la Lucía, la primera clienta de BotigaVerda. Al mòdul 4 comptarem aquests referits per persona amb GROUP BY per saber qui encapçala el programa; aquí ens quedem al detall.

Fixa't en un detall subtil d'escriptura: encara que l'àlies ref estigui escrit primer al FROM, continua sent la taula "pare" de la relació. Qui va primer és una decisió de llegibilitat, no de significat: el que fixa la direcció és la condició ON c.referit_per_id = ref.id.

No tot SELF JOIN recorre una relació reflexiva declarada. Un altre ús molt freqüent és emparellar files que comparteixen un atribut: productes de la mateixa categoria, empleats de la mateixa ciutat, comandes del mateix dia.

L'equip de màrqueting vol dissenyar lots de dos productes de la mateixa categoria. Primer intent:

Això retorna 78 files, i té dos problemes greus:

La solució cap en tres caràcters: p1.id < p2.id.

6 files: les 6 parelles possibles entre els 4 productes de Cosmètica natural. Sense el WHERE de retall, la consulta retorna 29 parelles a tot el catàleg.

Aquest truc mereix un paràgraf sencer, perquè és el patró canònic i es reutilitza en mil llocs.

Per a dos productes qualssevol A i B de la mateixa categoria, el producte cartesià genera quatre combinacions:

La condició fa dues feines alhora amb un sol operador:

• < en lloc de <= elimina els parells reflexius (A, A), perquè cap id no és menor que si mateix.
• < en lloc de <> elimina el duplicat (B, A), perquè de les dues ordenacions possibles només una satisfà la desigualtat estricta.

L'aritmètica confirma l'efecte: amb n productes en una categoria, sense condició hi ha n² combinacions; amb <> n'hi ha n² − n; amb < n'hi ha exactament n(n−1)/2, que és el nombre de parelles de la combinatòria.

Nota: aquest és un JOIN amb una condició de desigualtat, el que a 03-01 anomenàvem non-equi join. La igualtat p1.categoria_id = p2.categoria_id defineix el grup; la desigualtat p1.id < p2.id selecciona una de les dues ordenacions. És habitual que un non-equi join acompanyi un d'igualtat, no que el substitueixi.

El mateix patró, aplicat a empleats. RH vol organitzar dinars d'equip per oficina i necessita les parelles de companys que treballen a la mateixa ciutat:

(10 primeres de 21 files.)

21 parelles, totes de València: set dels vuit empleats hi treballen, i 7 × 6 / 2 = 21. Laia Puig Sanchis no apareix a cap fila, perquè és l'única que treballa a Castelló i no té amb qui emparellar-se.

Aquest detall és important: un SELF JOIN d'aquest tipus exclou automàticament els elements únics del seu grup. Si volguessis un llistat en què la Laia també aparegués (amb NULL com a companya), et caldria un LEFT JOIN amb la mateixa condició a l'ON.

També convé notar que aquí ciutat admet NULL. Si dos empleats tinguessin la ciutat sense omplir, NULL = NULL no és cert i no s'emparellarien, cosa que en aquest cas és el correcte: no sabem si treballen junts.

Un SELF JOIN recorre exactament un nivell de la jerarquia. Per pujar dos nivells calen dos SELF JOIN encadenats:

Funciona, i a BotigaVerda n'hi ha prou perquè l'organigrama té només tres nivells. Però fixa't en la limitació: el nombre de nivells està codificat a la consulta. Si l'empresa creix a cinc nivells, cal reescriure-la afegint-hi dos LEFT JOIN més; si un empleat està a set nivells de la direcció, aquesta consulta no l'assolirà mai.

Per a jerarquies de profunditat desconeguda cal una altra eina: les CTE recursives (WITH RECURSIVE), que s'estudien a la lliçó 10-02. Amb elles es recorre un arbre de qualsevol profunditat amb una sola consulta, i es pot calcular el nivell de cada empleat o la cadena de comandament completa. El mateix val per a la xarxa de referits: "tots els clients que descendeixen de la Lucía, directament o indirectament" és una consulta recursiva, no un SELF JOIN.

El CROSS JOIN combina cada fila d'una taula amb cada fila de l'altra, sense cap condició. És el pas 1 del model mental de 03-01, sense el pas 2.

Retorna 6 × 20 = 120 files. Cap condició, cap ON: CROSS JOIN no admet ON, i si l'escrius dona error de sintaxi.

La mida creix com el producte dels cardinals, cosa que en taules reals és explosiva:

Aquesta última fila —un bilió de files— és la raó per la qual un CROSS JOIN accidental pot deixar sense memòria un servidor. La regla és simple: un CROSS JOIN només és acceptable quan almenys una de les dues taules és petita i de mida coneguda.

Són idèntiques per al motor. La primera és una declaració d'intencions; la segona és exactament el que apareix quan algú oblida la condició d'emparellament amb la sintaxi antiga de 03-01.

Convenció del curs: si vols un producte cartesià, escriu CROSS JOIN en majúscules i amb un comentari que expliqui per què. És la diferència entre "això està pensat" i "això és un descuit".

Aquest és el motiu pel qual el CROSS JOIN existeix en el dia a dia d'un analista.

Imagina l'informe "vendes per categoria i mes del 2025". Si el construeixes només amb les vendes reals, els mesos sense vendes simplement no apareixen: una categoria que no va vendre res a l'agost no tindrà fila d'agost, i el gràfic saltarà de juliol a setembre com si l'agost no existís.

La solució professional consisteix a generar primer l'esquelet complet —totes les combinacions categoria × mes— i unir-hi després les vendes amb un LEFT JOIN:

L'esquelet:

(8 primeres de 72 files.)

72 files = 6 categories × 12 mesos. No falta cap combinació, i sobre aquesta base l'informe queda quadrat encara que una categoria no vengui res en tot l'any. La part de sumar les vendes és del mòdul 4; l'esquelet és d'aquí.

L'altre cas clàssic no necessita ni taules: es construeix amb llistes literals fent servir VALUES (que estudiaràs a fons a 05-02).

Retorna 12 files: les 4 talles × 3 colors d'un catàleg tèxtil. És la llista de referències que caldria donar d'alta. BotigaVerda no ven roba, però el patró apareix a qualsevol botiga amb variants de producte.

generate_series és una funció generadora de files de PostgreSQL. Produeix una sèrie de valors i es fa servir al FROM com si fos una taula:

Amb dates és on resulta més útil, perquè admet un interval com a pas:

I creuada amb categories genera el calendari complet de l'informe anterior, ara amb dates reals:

72 files, a punt per rebre les vendes amb un LEFT JOIN.

Nota de dialecte: generate_series és específic de PostgreSQL. Els equivalents en altres motors:

Motor	Com generar una sèrie
PostgreSQL	generate_series(inici, fi [, pas])
SQL Server	GENERATE_SERIES (des del 2022); abans, una CTE recursiva o una taula de calendari
Oracle	CONNECT BY LEVEL <= n
MySQL 8+	CTE recursiva WITH RECURSIVE
SQLite	CTE recursiva, o l'extensió generate_series

L'alternativa portable en qualsevol motor és mantenir una taula de calendari permanent amb una fila per dia o per mes. És el que fan la majoria dels magatzems de dades, i evita dependre del dialecte.

Existeix una variant avançada del CROSS JOIN en què la taula de la dreta pot referir-se a columnes de l'esquerra:

Es diu CROSS JOIN LATERAL (o LEFT JOIN LATERAL) i serveix, per exemple, per a "les tres ressenyes més recents de cada producte". Necessita subconsultes, que són el mòdul 7, així que aquí només ho esmentem perquè reconeguis la paraula si la veus. LATERAL és estàndard SQL i està disponible a PostgreSQL, Oracle i SQL Server (on es diu CROSS APPLY / OUTER APPLY).

• Oblidar els àlies en un SELF JOIN. ERROR: table name "empleats" specified more than once. Cada còpia necessita el seu propi nom.
• Fer servir INNER JOIN en una jerarquia. Perds l'arrel de l'arbre: Rosa Alcázar Vives desapareix de l'organigrama perquè el seu cap_id és NULL. Fes servir LEFT JOIN.
• Oblidar la condició p1.id < p2.id en generar parelles. Obtens cada element emparellat amb si mateix i cada parella per duplicat: 78 files en comptes de 29.
• Fer servir <> en lloc de <. Elimina els parells reflexius però no els duplicats: 58 files en comptes de 29.
• Creure que un SELF JOIN recorre tota la jerarquia. En recorre exactament un nivell. Dos nivells, dos JOIN. Profunditat desconeguda, WITH RECURSIVE (10-02).
• Emparellar per una columna que admet NULL en un SELF JOIN de grup. Dos empleats amb ciutat a NULL no s'emparellen, perquè NULL = NULL no és cert.
• Escriure un CROSS JOIN amb la sintaxi de comes. És correcte, però indistingible d'un oblit. Escriu CROSS JOIN explícit.
• Intentar posar ON en un CROSS JOIN. No l'admet: si necessites condició, no és un CROSS JOIN, és un INNER JOIN.
• Creuar dues taules grans "per veure què surt". Comprova abans el producte dels seus recomptes. 10 000 × 10 000 són cent milions de files.
• Consell: dibuixa l'arbre abans d'escriure el SELF JOIN. Saber qui és pare i qui és fill evita invertir la condició ON, que és l'error més comú i el més difícil de veure.
• Consell: compta les parelles esperades abans d'executar. n(n−1)/2 per a les parelles d'un grup de n. Si el resultat no coincideix, saps immediatament que falta la condició o que en sobra.
• Consell: per a informes per període, construeix sempre primer l'esquelet. CROSS JOIN de dimensions + LEFT JOIN dels fets. És la forma estàndard que no hi hagi buits, i la faràs servir tan bon punt comencis a agregar al mòdul 4.

Màrqueting vol una fitxa del programa de referits que mostri, per a cada client: el seu nom complet, qui el va referir (o NULL si va arribar pel seu compte) i qui va referir el seu referidor (l'"avi" de la xarxa).

1. Escriu la consulta fent servir dos SELF JOIN encadenats sobre clients.
2. Restringeix-la als clients que sí que tenen referidor i comenta el resultat.
3. Per què aquesta consulta no pot respondre a "tots els clients que descendeixen de la Lucía, a qualsevol profunditat"?

Compres vol llançar lots de dos productes de la mateixa categoria el preu conjunt dels quals no superi els 10 €, fent servir només productes actius. Escriu la consulta que retorni la categoria, els dos productes amb els seus preus i el preu del lot, ordenada per preu del lot.

Explica quin paper fa cadascuna de les condicions de l'ON i del WHERE.

Respon raonant, sense executar:

1. Quantes files retorna SELECT * FROM empleats AS e1 CROSS JOIN empleats AS e2;?
2. I SELECT * FROM empleats AS e1 JOIN empleats AS e2 ON e1.id <> e2.id;?
3. I SELECT * FROM empleats AS e1 JOIN empleats AS e2 ON e1.id < e2.id;?
4. Escriu l'esquelet d'un informe proveïdor × trimestre del 2025 fent servir CROSS JOIN i generate_series. Quantes files té?

Solució 1

1. Amb dos SELF JOIN encadenats:

Retorna les 15 files de clients, amb dues columnes que van quedant a NULL a mesura que s'esgota la cadena.

2. Només els clients amb referidor (n'hi ha prou de canviar el primer LEFT JOIN per un INNER JOIN; el segon ha de continuar sent LEFT):

8 files. Només dos clients tenen "avi" a la xarxa: l'Ana (portada pel Carlos, que al seu torn va venir de la Lucía) i la Núria (portada per l'Ana, que va venir del Carlos). La resta descendeix directament d'algú que va arribar pel seu compte.

Fixa't que la cadena Lucía → Carlos → Ana → Núria té tres salts, i aquesta consulta només n'arriba a veure dos: a la fila de la Núria apareix el Carlos com a avi, però la Lucía —la besàvia— ja no hi cap.

3. Per què no serveix per a "tots els descendents de la Lucía": perquè el nombre de nivells està escrit a la consulta. Cada nivell addicional exigeix un LEFT JOIN més, i per respondre aquesta pregunta caldria conèixer per endavant la profunditat màxima de la xarxa. Amb una cadena de set referits, caldrien set JOIN. L'eina correcta és una CTE recursiva (WITH RECURSIVE), que recorre l'arbre fins a esgotar-lo amb una sola consulta: lliçó 10-02.

Solució 2

5 lots possibles, tots d'Alimentació i Begudes: són les dues categories amb productes barats. A Cosmètica natural, el lot més econòmic seria el bàlsam (4,60 €) amb el xampú (8,40 €), que ja suma 13 €.

Paper de cada condició:

Les condicions d'emparellament van a l'ON i les de filtratge al WHERE, seguint la convenció de 03-01. Com que tots els JOIN són INNER, aquí seria equivalent posar-les a qualsevol dels dos llocs (03-02), però la separació manté la consulta llegible.

Solució 3

1. CROSS JOIN d'empleats amb si mateixa: 8 × 8 = 64 files. Totes les combinacions, inclosos els vuit parells de cada empleat amb si mateix.

2. Amb ON e1.id <> e2.id: 56 files. S'eliminen els 8 parells reflexius (64 − 8), però cada parella continua apareixent dues vegades, en els dos ordres.

3. Amb ON e1.id < e2.id: 28 files. És 8 × 7 / 2, el nombre de parelles diferents de 8 elements. Cada parella una sola vegada i cap amb si mateixa.

La progressió 64 → 56 → 28 resumeix tota la secció 4.

4. Esquelet proveïdor × trimestre del 2025:

20 files = 5 proveïdors × 4 trimestres. Els trimestres generats comencen l'1 de gener, l'1 d'abril, l'1 de juliol i l'1 d'octubre del 2025; el límit superior és 2025-10-01 perquè generate_series inclou l'extrem final, i posar-hi 2025-12-01 produiria un cinquè valor no desitjat.

Sobre aquest esquelet, un LEFT JOIN amb les compres reals donaria l'informe trimestral per proveïdor sense buits, inclòs el proveïdor 5 (EcoNordic Supplies, inactiu), que apareixeria amb tots els seus trimestres a NULL.

Tanques els dos JOIN especials:

• Un SELF JOIN és un JOIN normal en què la mateixa taula apareix dues vegades. Els àlies són obligatoris —ERROR: table name specified more than once— i convé nomenar-los pel paper que fan: e / cap, c / ref, p1 / p2.
• La jerarquia d'empleats es recorre amb e.cap_id = cap.id. Amb INNER JOIN es perd l'arrel (7 files, sense la Rosa); amb LEFT JOIN apareix l'equip complet (8 files). En tota jerarquia, l'arrel té la FK a NULL.
• La xarxa de referits de clients funciona igual: 8 clients referits i 7 que van arribar pel seu compte. Lucía Martínez Soler en va portar tres.
• Per a parelles dins d'un mateix grup —productes de la mateixa categoria, empleats de la mateixa ciutat— el patró canònic és ON a.grup = b.grup AND a.id < b.id. El < estricte elimina d'un cop els parells reflexius i els duplicats invertits: 29 parelles en comptes de 78.
• Un SELF JOIN recorre un nivell per JOIN. Per a profunditat arbitrària calen CTE recursives (10-02).
• El CROSS JOIN és el producte cartesià explícit, sense ON. És un accident quan s'oblida la condició d'emparellament, i una eina quan genera deliberadament l'esquelet d'un informe (categoria × mes) o una matriu de variants (talla × color).
• generate_series de PostgreSQL produeix sèries numèriques i de dates utilitzables al FROM. Creuat amb una dimensió, dona calendaris complets sense buits; en altres motors se substitueix per CTE recursives o per una taula de calendari.

Només queda una forma de combinar taules que encara no coneixes. Tots els JOIN d'aquest mòdul afegeixen columnes: agafen una fila d'aquí, una d'allà i les enganxen de costat. A la lliçó següent, UNION, INTERSECT i EXCEPT, faràs el contrari: apilar resultats sencers en vertical, afegint files en lloc de columnes. Construiràs una llista unificada de contactes a partir de clients, empleats i proveidors; esbrinaràs a quines ciutats hi ha alhora clients i empleats; i tornaràs a trobar els tres productes mai venuts, aquesta vegada sense cap JOIN.