Tot el que has fet en aquest mòdul consisteix a emparellar files en horitzontal: agafar una fila de comandes, buscar-li la parella a clients i enganxar-les de costat per obtenir una fila més ampla. Els JOIN afegeixen columnes.

Existeix una segona forma de combinar, completament diferent: apilar resultats en vertical. S'executen dues consultes independents, cadascuna amb les seves pròpies columnes, i les seves files s'ajunten una sota l'altra. Els operadors de conjunts —UNION, INTERSECT i EXCEPT— afegeixen files.

Aquesta lliçó tanca el mòdul 3. Amb ella tindràs les dues maneres de combinar informació en SQL i sabràs quina demanar en cada cas.

1. JOIN enfront dels operadors de conjunts
2. Les regles de compatibilitat
3. UNION i UNION ALL
4. La llista unificada de contactes
5. INTERSECT: el que és a tots dos
6. EXCEPT: el que és al primer i no al segon
7. EXCEPT enfront de l'anti-join de 03-03
8. INTERSECT ALL i EXCEPT ALL
9. Precedència i parèntesis
10. ORDER BY i LIMIT sobre el resultat combinat
11. Suport per motor
12. Errors habituals i consells
13. Exercicis
14. Conclusió

La diferència clau és la segona meitat de la tercera fila: els operadors de conjunts no necessiten que les taules estiguin relacionades. Pots unir els resultats de dues consultes sobre taules que no comparteixen ni una clau forana, sempre que les seves columnes encaixin.

Perquè dues consultes es puguin combinar hi ha tres regles, i totes tres són estrictes:

1. Mateix nombre de columnes.

2. Tipus compatibles, columna a columna, en el mateix ordre.

La columna 1 de la primera consulta es combina amb la columna 1 de la segona, la 2 amb la 2, etc. Els noms no importen; importa la posició. PostgreSQL aplica les seves regles de conversió implícita: INTEGER i NUMERIC es combinen sense problema, VARCHAR i TEXT també, però DATE i VARCHAR no.

3. Els noms de columna del resultat els posa la primera consulta.

La columna del resultat es diu contacte. L'àlies de la segona consulta s'ignora completament, i aquesta és una font clàssica de confusió en llegir codi aliè.

Conseqüència pràctica: escriu els àlies a la primera consulta i no et molestis a repetir-los a les altres (encara que fer-ho ajuda a documentar què és cada columna).

Què passa si una taula no té una columna que l'altra sí que té? A BotigaVerda, clients i proveidors tenen email, però empleats no. La solució és omplir el buit amb un literal:

El ::varchar és un cast explícit. Sense ell, PostgreSQL de vegades pot inferir el tipus del NULL a partir de l'altra branca, però no sempre; escriure'l evita l'error failed to determine data type of column. Els casts s'estudien a fons a la lliçó 06-04.

UNION apila les files de dues consultes i elimina els duplicats. UNION ALL les apila i no elimina res.

Vegem-ho amb les ciutats on BotigaVerda té presència:

11 files. I la mateixa consulta amb UNION ALL retorna 23 files: les 15 ciutats de clients (amb València repetida quatre vegades i Barcelona dues) més les 8 d'empleats (amb València set vegades).

UNION sense ALL fa una feina extra que moltes vegades no només és innecessària, sinó incorrecta:

• És més lent. Deduplicar exigeix ordenar tot el resultat o mantenir una estructura hash a la memòria. Amb milions de files, la diferència és enorme.
• Pot esborrar files legítimes. Si dos clients diferents es diguessin igual i visquessin a la mateixa ciutat, SELECT nom, ciutat FROM clients UNION ... els col·lapsaria en un. Hauries perdut un client sense assabentar-te'n.
• Compara la fila sencera. Dues files són duplicades només si totes les seves columnes coincideixen. Afegir una columna id al SELECT fa que UNION deixi d'eliminar res, i llavors només n'estàs pagant el cost.

Regla del curs: fes servir UNION ALL per defecte. Recorre a UNION únicament quan l'eliminació de duplicats sigui l'objectiu explícit de la consulta, com al llistat de ciutats de dalt. És la mateixa filosofia que la de DISTINCT a 02-04: si el necessites per "arreglar" un resultat, revisa abans si la consulta està ben plantejada.

Aquesta és també la raó per la qual l'emulació del FULL OUTER JOIN a MySQL (03-05) fa servir UNION i no UNION ALL: allà les dues meitats sí que produeixen les mateixes files coincidents, i cal eliminar-les.

Un cas real: l'empresa vol una agenda única amb totes les persones i entitats amb què tracta, assenyalant d'on surt cadascuna. Els tres orígens viuen en taules diferents i sense cap relació entre si: és l'escenari perfecte per a UNION ALL.

28 files = 15 clients + 8 empleats + 5 proveïdors.

Quatre decisions de disseny d'aquesta consulta mereixen comentari:

Fixa't també en l'ordre alfabètic: "Óscar" apareix entre "Marc" i "Rosa" perquè la base de dades fa servir la col·lació ca-ES-x-icu que vas configurar a 02-05. Amb la col·lació per defecte C, "Óscar" aniria al final del bloc, després de "Rosa".

INTERSECT retorna les files que apareixen a totes dues consultes.

A quines ciutats tenim alhora clients i empleats?

Dues ciutats. València, on hi ha la seu i viuen quatre clients, i Castelló, on treballa Laia Puig Sanchis i viu Marta Sanchis Gil. És una consulta amb lectura de negoci immediata: són les ciutats on es podria organitzar un lliurament en mà o un acte amb clients.

A quins països tenim alhora clients i proveïdors?

Tres països. Alemanya en queda fora perquè allà hi ha proveïdor (EcoNordic Supplies) però cap client.

Dues propietats d'INTERSECT que convé saber:

• Elimina duplicats per defecte, igual que UNION. Si una ciutat aparegués cinc vegades a clients i tres a empleats, el resultat la mostra una vegada.
• És commutatiu: A INTERSECT B i B INTERSECT A donen el mateix. És la propietat que EXCEPT no té.

EXCEPT retorna les files de la primera consulta que no apareixen a la segona. És la diferència de conjunts.

Quins productes no s'han venut mai? Els identificadors del catàleg, menys els identificadors que apareixen a les vendes:

Els tres de sempre. La consulta es llegeix gairebé com la pregunta: "tots els productes, llevant els que s'han venut".

A quins països tenim proveïdor però cap client?

Una fila. I ara la clau d'EXCEPT: no és commutatiu. Dona-hi la volta:

Zero files: no hi ha cap país amb clients en què no tinguem també proveïdor. Les dues consultes responen a preguntes diferents, i confondre-les és l'error més freqüent amb aquest operador.

Nota de dialecte: a Oracle aquest operador es diu MINUS, no EXCEPT. Des d'Oracle 21c també s'admet EXCEPT com a sinònim, però trobaràs MINUS en tot el codi anterior. El comportament és idèntic.

Tens ara dues formes de respondre a "quins productes no s'han venut mai?". Compara-les:

Totes dues identifiquen els productes 13, 19 i 20. Però no són intercanviables:

Quan triar cadascun: si necessites dades de les files resultants, fes servir l'anti-join. Si només necessites la llista d'identificadors —per a un recompte, una comprovació d'integritat, un informe de conciliació—, EXCEPT és més curt i es llegeix millor.

Al mòdul 7 apareixerà una tercera forma, NOT EXISTS amb una subconsulta correlacionada, que combina el millor de totes dues: retorna la fila sencera i es llegeix com la pregunta. I una quarta, NOT IN, que sembla la més natural i té un comportament traïdor amb els nuls. Les quatre es comparen a la lliçó 07-05.

Igual que UNION té la seva variant ALL, INTERSECT i EXCEPT també la tenen. La seva semàntica és multiconjunt: en lloc de treballar amb presència o absència, compten quantes vegades apareix cada fila.

Exemple sobre les ciutats: València apareix 4 vegades a clients i 7 vegades a empleats.

Retorna València 4 vegades —min(4, 7)— i Castelló 1 vegada —min(1, 1)—: 5 files en total. Amb INTERSECT a seques en serien 2.

Retorna València 3 vegades —max(7 − 4, 0)— i res més: max(1 − 1, 0) = 0 per a Castelló.

A la pràctica es fan servir molt poc. El seu terreny són les comprovacions de qualitat de dades del tipus "aquesta migració ha duplicat files?", on importa el nombre de repeticions i no només la seva presència. És útil saber que existeixen; no és habitual escriure-les.

Nota de dialecte: INTERSECT ALL i EXCEPT ALL són estàndard i són a PostgreSQL, però no a SQL Server ni a Oracle (on MINUS no té variant ALL).

Quan s'encadenen tres o més consultes, l'ordre d'avaluació importa. L'estàndard SQL estableix que:

INTERSECT té més precedència que UNION i EXCEPT, que s'avaluen d'esquerra a dreta entre si.

És a dir, A UNION B INTERSECT C significa A UNION (B INTERSECT C), igual que 2 + 3 * 4 significa 2 + (3 * 4).

Vegem-ho amb un cas que canvia radicalment segons com s'agrupi. La consulta:

PostgreSQL l'avalua com a productes EXCEPT (linies_comanda INTERSECT ressenyes):

1. linies_comanda INTERSECT ressenyes = els productes que s'han venut i tenen ressenya = 9 productes (1, 2, 5, 6, 10, 12, 15, 16, 18).
2. productes EXCEPT aquests 9 = 11 productes: 3, 4, 7, 8, 9, 11, 13, 14, 17, 19 i 20.

Són exactament els 11 productes sense cap ressenya. Ara forcem l'altra agrupació amb parèntesis:

Zero files, perquè (productes EXCEPT linies_comanda) són els tres productes mai venuts (13, 19, 20) i cap d'ells no té ressenya. La mateixa consulta, dues agrupacions, 11 files contra 0.

Regla del curs: tan bon punt hi hagi tres o més consultes encadenades amb operadors diferents, fes servir parèntesis sempre, encara que coincideixin amb la precedència per defecte. Costen dos caràcters i eliminen tota ambigüitat per a qui llegeixi el codi.

Nota de dialecte important: SQLite no implementa aquesta precedència. Avalua els operadors compostos estrictament d'esquerra a dreta, així que la primera consulta d'aquesta secció retornaria 0 files a SQLite i 11 a PostgreSQL, MySQL, SQL Server i Oracle. És un argument definitiu a favor dels parèntesis explícits: amb ells, la consulta significa el mateix a tots els motors.

ORDER BY i LIMIT no pertanyen a cap de les consultes: s'apliquen al resultat combinat i van al final de tot.

Les cinc primeres ciutats per ordre alfabètic del conjunt ja unificat. Detalls importants:

Si necessites ordenar o limitar una de les consultes per separat, cal tancar-la entre parèntesis:

És sintaxi vàlida a PostgreSQL, encara que poc freqüent: gairebé sempre el que es vol és ordenar el total.

A l'ordre lògic d'execució, el pas queda així: cada consulta es resol completament (amb el seu FROM, els seus JOIN, el seu WHERE i el seu SELECT), després s'aplica l'operador de conjunt, i només llavors s'ordena i es retalla.

UNION és l'únic dels tres que pots donar per segur en qualsevol motor i qualsevol versió. Si escrius SQL portable i necessites INTERSECT o EXCEPT sobre MySQL antic, l'alternativa és un JOIN (per a la intersecció) o un anti-join (per a la diferència), que és justament el que feia tothom abans del 2022.

• Diferent nombre de columnes. ERROR: each UNION query must have the same number of columns. Compta les columnes de cada branca abans d'executar.
• Tipus incompatibles a la mateixa posició. Les columnes s'emparellen per posició, no per nom. Un DATE a la posició 2 d'una branca i un VARCHAR a la 2 de l'altra dona error.
• Esperar que l'àlies de la segona consulta es faci servir. Els noms del resultat els posa sempre la primera.
• Fer servir UNION quan volies UNION ALL. Elimina files legítimament repetides i costa més. Per defecte, UNION ALL.
• Fer servir UNION ALL quan sí que hi havia solapament. A l'emulació del FULL OUTER JOIN (03-05) duplicaria totes les files coincidents.
• Invertir l'ordre d'un EXCEPT. No és commutatiu: proveidors EXCEPT clients dona Alemanya; a l'inrevés, zero files.
• Posar ORDER BY en una consulta intermèdia. ERROR: syntax error at or near "UNION". Va al final, una sola vegada, i afecta el total.
• Encadenar tres operadors sense parèntesis. INTERSECT s'avalua abans que UNION i EXCEPT a l'estàndard, però no a SQLite. La mateixa consulta retornava 11 files i 0 files segons l'agrupació.
• Oblidar la columna d'origen en un UNION de fonts diferents. Sense ella no saps si "Laia Puig Sanchis" és una empleada o una clienta.
• Consell: escriu la primera branca, executa-la, i només llavors afegeix les altres. Depurar un UNION de quatre consultes escrit d'una tirada és incòmode; els errors de tipus assenyalen la unió, no la columna culpable.
• Consell: alinea visualment les branques. Mateix ordre de columnes, mateixa indentació i l'operador en una línia pròpia. Un UNION ben formatat es revisa d'un cop d'ull.
• Consell: fes servir ORDER BY 1, 2 a les consultes de conjunts. Els noms poden ser enganyosos quan les branques vénen de taules diferents; els ordinals, no.

Construeix un directori de contactes espanyols: totes les persones i entitats de BotigaVerda el país de les quals sigui Espanya, amb una columna que indiqui el seu tipus (client, empleat o proveidor), el seu nom i la seva ciutat si n'hi ha.

Tingues en compte que empleats no té columna pais —tots treballen a Espanya— i que proveidors no té ciutat. Ordena per tipus i nom.

Respon amb operadors de conjunts:

1. A quines ciutats hi ha clients però cap empleat?
2. A quines ciutats hi ha empleats però cap client?
3. Explica per què els dos resultats són tan diferents en mida.

Un company vol la llista de productes que s'han venut però no tenen cap ressenya, i escriu això:

1. Com agrupa PostgreSQL aquesta consulta i què retorna realment?
2. Escriu la consulta correcta per al que ell volia.
3. Escriu la mateixa resposta fent servir un JOIN i un anti-join en lloc d'operadors de conjunts, retornant també el nom del producte. Quina de les dues prefereixes i per què?

Solució 1

21 files = 11 clients espanyols + 8 empleats + 2 proveïdors espanyols.

Tres detalls del raonament:

• empleats no porta WHERE perquè la taula no té columna pais: per disseny, tot l'equip treballa a Espanya. És una suposició del model, i convé deixar-la escrita en un comentari perquè ningú no la doni per oblidada.
• Els filtres van al WHERE de cada branca, no al final. Un WHERE després de l'últim UNION ALL pertanyeria només a la tercera consulta, no al conjunt.
• NULL::varchar manté el nombre de columnes a la branca de proveïdors.

Solució 2

1. Ciutats amb clients però sense empleats:

9 ciutats.

2. Ciutats amb empleats però sense clients:

Cap.

3. Per què són tan diferents. Perquè els dos conjunts tenen mides i naturaleses molt diferents:

Les ciutats dels empleats són un subconjunt de les dels clients: BotigaVerda només té oficines a València (seu) i Castelló (on treballa la Laia), i a totes dues hi ha clients. Per això empleats EXCEPT clients és buit, mentre que l'operació inversa retorna les altres nou ciutats on hi ha clients i cap presència física.

Comprovació creuada amb la secció 5: 11 ciutats en total (UNION), 2 en comú (INTERSECT), 9 només de clients (EXCEPT) i 0 només d'empleats. Els números quadren: 2 + 9 + 0 = 11.

Solució 3

1. Com agrupa PostgreSQL. No hi ha INTERSECT a la consulta, així que els dos operadors restants s'avaluen d'esquerra a dreta:

La primera part dona els 8 productes venuts sense ressenya; la segona afegeix els 20 productes del catàleg. La unió de tots dos conjunts són... els 20 productes. La consulta retorna el catàleg sencer, que no té absolutament res a veure amb la pregunta. L'UNION amb productes anul·la tota la feina de l'EXCEPT.

2. La consulta correcta. La tercera branca sobra completament:

8 productes venuts alguna vegada i mai ressenyats. Compara-ho amb els 11 productes sense ressenya de la secció 9: la diferència són els tres que mai no s'han venut (13, 19 i 20), que aquí no apareixen perquè no són a linies_comanda.

3. Amb JOIN i anti-join:

Els mateixos 8 productes, ara amb el seu nom i el seu preu. L'INNER JOIN amb linies_comanda exigeix que s'hagin venut; el LEFT JOIN amb ressenyes més el WHERE r.id IS NULL exigeix que no tinguin ressenya.

Quina preferir:

Per a aquest cas concret, el JOIN és la millor opció, perquè un informe amb vuit números sense nom no serveix a ningú. L'EXCEPT seria preferible si la consulta fos un pas intermedi d'una comprovació automàtica, on només importen els identificadors.

I fixa't en el DISTINCT: és exactament el símptoma del qual parlàvem a 02-04 i 03-02. Apareix perquè l'INNER JOIN amb linies_comanda genera una fila per venda, i la pregunta és sobre productes, no sobre vendes. Al mòdul 7 veuràs que WHERE EXISTS (...) resol això sense DISTINCT i sense multiplicar files.

Tanques l'última peça del mòdul:

• Els operadors de conjunts combinen en vertical: apilen files de consultes independents, mentre que els JOIN combinen en horitzontal afegint columnes. No necessiten cap relació entre les taules.
• Les regles de compatibilitat són tres: mateix nombre de columnes, tipus compatibles per posició i noms del resultat presos de la primera consulta. Els buits s'omplen amb literals com NULL::varchar.
• UNION ALL és l'opció per defecte: no dedupla, és més ràpid i no esborra files legítimament repetides. UNION només quan eliminar duplicats sigui l'objectiu, com a la llista d'11 ciutats enfront de les 23 d'UNION ALL.
• Has construït la llista unificada de contactes (28 files de tres taules sense relació entre si) amb una columna d'origen literal, que és el que fa llegible qualsevol UNION de fonts heterogènies.
• INTERSECT retorna el que és als dos costats i és commutatiu: dues ciutats amb clients i empleats, tres països amb clients i proveïdors.
• EXCEPT retorna el del primer que no és al segon i no és commutatiu: Alemanya en un sentit, zero files en l'altre. A Oracle es diu MINUS.
• Saps quan triar EXCEPT i quan l'anti-join de 03-03: EXCEPT per a llistes d'identificadors, anti-join quan necessites les dades de les files.
• INTERSECT té precedència sobre UNION i EXCEPT —llevat de SQLite, que avalua d'esquerra a dreta—, i la mateixa consulta pot retornar 11 files o 0 segons com s'agrupi. Fes servir parèntesis sempre que encadenis tres o més.
• ORDER BY i LIMIT van al final i s'apliquen al resultat combinat, fent servir els noms de columna de la primera consulta o els ordinals.

Les nou taules de BotigaVerda han deixat de ser nou illes. Saps:

• Que un JOIN és un producte cartesià filtrat, que la seva condició va a l'ON i que els JOIN es resolen dins del FROM, abans del WHERE.
• Fer servir l'INNER JOIN i predir quines files perd, amb la consulta canònica de quatre taules —linies_comanda + comandes + clients + productes— que t'acompanyarà fins al projecte final.
• Fer servir el LEFT JOIN per conservar el que no casa, el patró anti-join per trobar-ho, i per què una condició al WHERE sobre la taula dreta degrada silenciosament el LEFT a INNER.
• Que el RIGHT JOIN és el seu mirall i sempre es pot reescriure com a LEFT, i que barrejar-los en una cadena fa la consulta il·legible.
• Que el FULL OUTER JOIN serveix per conciliar dues fonts independents, i que la integritat referencial el fa innecessari dins d'un mateix esquema.
• Recórrer les relacions reflexives amb SELF JOIN —l'organigrama i la xarxa de referits— i generar combinacions completes amb CROSS JOIN.
• I apilar resultats sencers amb UNION, INTERSECT i EXCEPT.

Però fixa't en què retornen totes les consultes que has escrit en aquest mòdul: files de detall. Una fila per línia de comanda, una per client, una per parella de productes. I les preguntes que la direcció de BotigaVerda fa de debò no demanen detall, demanen totals: quant factura cada categoria, quantes comandes ha fet cada client, quina és la puntuació mitjana de cada producte, quin comercial tanca més vendes, quants clients hi ha per país. Per respondre-les cal resumir moltes files en una de sola, i això és una operació que encara no saps fer.

Al mòdul 4, Filtratge avançat i agregació, arribaran les dues meitats que falten. Primer afinaràs el filtratge: LIKE per buscar per patrons de text, IN i BETWEEN per a rangs i llistes, i el tractament seriós dels NULL amb IS NULL, IS NOT NULL i la seva lògica de tres valors —aquella que ja has vist treure el nas a l'ON, al WHERE i a cada LEFT JOIN d'aquest mòdul—. I després arribarà l'agregació: les funcions COUNT, SUM, AVG, MIN i MAX, la clàusula GROUP BY que parteix el resultat en grups i HAVING per filtrar aquests grups. Allà tornarà, aquesta vegada amb conseqüències reals, l'advertiment que has llegit tres vegades en aquest mòdul: compte amb sumar valors de capçalera després d'unir amb el detall.