El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència

HOME

A la lliçó anterior vam aprendre a interrogar una taula. Però les preguntes que de veritat interessen a BiblioRed no caben en una sola taula: "qui té ara mateix l'exemplar EJ-3081?", "quins socis no han agafat mai res en préstec?", "de quin autor és el llibre que més es retarda?". Les respostes estan repartides entre socis, prestecs, exemplars, llibres i autors.

Aquest repartiment no és un defecte: és exactament el que vam decidir a la lliçó 02-01 en separar l'obra (llibres) de l'objecte físic (exemplars), i en no repetir el nom del soci a cada préstec. La informació es guarda una sola vegada, al lloc que li correspon, i es recompon quan cal. L'eina que la recompon és el JOIN, la reunió ⋈ de l'àlgebra relacional.

Aquesta és la lliçó on SQL deixa de semblar un cercador de taules i comença a semblar un llenguatge de consulta de debò. És llarga i densa; agafa-t'ho amb calma i executa cada exemple sobre el teu biblioredb.

Contingut

Per què cal recompondre les dades
Producte cartesià i CROSS JOIN
INNER JOIN: la reunió bàsica
LEFT JOIN i les preguntes negatives
RIGHT JOIN i FULL OUTER JOIN
Resum visual dels tipus de JOIN
SELF JOIN: unir una taula amb ella mateixa
Encadenar tres o més taules
Filtrar a ON o filtrar a WHERE
Subconsultes escalars
Subconsultes amb IN, EXISTS i NOT EXISTS
Subconsultes correlacionades
Taules derivades: subconsultes al FROM
Expressions de taula comuna: WITH
Operadors de conjunt: UNION, INTERSECT, EXCEPT
Errors habituals i consells
Exercicis
Conclusió

Per què cal recompondre les dades

Mira la taula prestecs en cru:

SELECT prestec_id, soci_id, exemplar_id, data_prestec FROM prestecs LIMIT 3;

prestec_id	soci_id	exemplar_id	data_prestec
1	14	2	2026-03-02
2	15	1	2026-03-05
3	16	5	2026-03-11

Per a un ésser humà això no diu res: 14, 2, 15, 1… són referències. El nom del soci és a socis, el codi de l'exemplar a exemplars i el títol a llibres. L'alternativa —guardar el nom i el títol dins de cada préstec— és justament el que feia el full de càlcul, i ja en vam veure el resultat: redundància, inconsistència i errors de teclat.

El tracte del model relacional és aquest: es guarda sense repetir, i es paga un JOIN en consultar. És un tracte excel·lent, perquè escriure bé passa una vegada i llegir malament passa per sempre.

Producte cartesià i `CROSS JOIN`

Abans d'aparellar files correctament, cal entendre què passa si no les aparelles. El producte cartesià × combina cada fila d'una taula amb cada fila de l'altra.

SELECT su.nom AS sucursal, a.cognoms AS autor
FROM sucursals su
CROSS JOIN autors a
ORDER BY su.sucursal_id, a.autor_id;

Primeres files:

sucursal	autor
Centre	Palma
Centre	Follett
Centre	Valcárcel
Centre	Barreda
…	…

SELECT COUNT(*) FROM sucursals CROSS JOIN autors;

 count
-------
    32

4 sucursals × 8 autors = 32 files. Amb socis (10) i llibres (9) en serien 90; amb les taules reals de BiblioRed, 12.000 × 8.000 = 96 milions.

Serveix per a alguna cosa? Sí, en un cas concret: generar totes les combinacions possibles de dos conjunts, per exemple per construir una graella de "cada sucursal × cada estat possible" que després s'omple amb dades. Fora d'això, un producte cartesià en producció gairebé sempre és un accident.

La forma antiga, i l'accident clàssic

Abans de SQL-92 no existia la paraula JOIN: les taules es llistaven al FROM separades per comes i la condició d'aparellament s'escrivia al WHERE.

-- Sintaxi antiga: funciona, però és perillosa
SELECT p.prestec_id, s.cognoms
FROM prestecs p, socis s
WHERE s.soci_id = p.soci_id;

El perill és evident: si oblides la condició del WHERE, obtens un producte cartesià en silenci. Dotze préstecs per deu socis són 120 files que semblen dades legítimes. Amb taules grans, la consulta es penja.

-- L'oblit!
SELECT p.prestec_id, s.cognoms FROM prestecs p, socis s;   -- 120 files

Amb la sintaxi moderna, JOIN ... ON, la condició està enganxada a la unió i no es pot perdre de vista. Fes servir sempre JOIN explícit.

`INNER JOIN`: la reunió bàsica

SELECT columnes
FROM taula_a
INNER JOIN taula_b ON condició_d_aparellament;

INNER JOIN és un producte cartesià seguit d'un filtre: retorna només les parelles de files que compleixen la condició. La paraula INNER és opcional (JOIN a seques significa INNER JOIN), però escriure-la deixa clar que no és un LEFT.

SELECT p.prestec_id,
       s.nom || ' ' || s.cognoms AS soci,
       p.data_prestec
FROM prestecs p
INNER JOIN socis s ON s.soci_id = p.soci_id
ORDER BY p.prestec_id;

prestec_id	soci	data_prestec
1	Marta Alsina	2026-03-02
2	Ivan Pereda	2026-03-05
3	Núria Bastos	2026-03-11
4	Marta Alsina	2026-04-06
5	Lucía Vendrell	2026-04-12
6	Núria Bastos	2026-05-04
7	Álvaro Ferran	2026-05-08
8	Sonia Quiroga	2026-05-19
9	Marta Alsina	2026-07-14
10	Ivan Pereda	2026-07-18
11	Diego Salom	2026-07-21
12	Pau Miralles	2026-07-25

Dotze files: una per préstec. La Marta Alsina hi apareix tres vegades perquè té tres préstecs; això no és duplicació, és la realitat.

Àlies de taula

p i s són àlies de taula. No són obligatoris, però sí molt recomanables:

Escurcen les referències: s.cognoms en lloc de socis.cognoms.
Són imprescindibles quan dues taules tenen columnes amb el mateix nom. Si escrius SELECT soci_id FROM prestecs JOIN socis ON ..., el gestor no sap de quina de les dues parles:

ERROR:  column reference "soci_id" is ambiguous

Són obligatoris en un SELF JOIN (apartat 7).

Consell d'estil: fes servir inicials reconeixibles (s socis, p prestecs, e exemplars, l llibres, a autors, su sucursals) i sigues coherent en tot el projecte. En aquest curs sempre farem servir aquestes mateixes.

Combinar `JOIN` amb `WHERE`

SELECT s.soci_id,
       s.nom || ' ' || s.cognoms AS soci,
       p.data_devolucio_prevista
FROM prestecs p
INNER JOIN socis s ON s.soci_id = p.soci_id
WHERE p.data_devolucio IS NULL
ORDER BY p.data_devolucio_prevista;

soci_id	soci	data_devolucio_prevista
14	Marta Alsina	2026-08-04
15	Ivan Pereda	2026-08-08
17	Diego Salom	2026-08-11
19	Pau Miralles	2026-08-15

Els quatre socis que tenen alguna cosa pendent de retornar, amb el termini de cadascun.

`USING` i `NATURAL JOIN`

Quan les columnes de totes dues taules es diuen exactament igual, existeix una abreviatura:

SELECT p.prestec_id, s.cognoms
FROM prestecs p
INNER JOIN socis s USING (soci_id);

USING (soci_id) equival a ON s.soci_id = p.soci_id, i a més fusiona les dues columnes en una de sola al resultat. És còmode i el nostre esquema ho permet, perquè hem anomenat les claus foranes igual que les primàries.

També existeix NATURAL JOIN, que aparella automàticament per totes les columnes de nom coincident:

-- NO el facis servir
SELECT * FROM exemplars NATURAL JOIN llibres;

Aquí exemplars i llibres comparteixen llibre_id… però si algun dia algú afegeix a totes dues una columna observacions, el NATURAL JOIN començarà a aparellar també per ella i la consulta canviarà de significat sense que ningú l'hagi tocada. És màgia implícita: evita-la. ON explícit o, com a molt, USING.

`LEFT JOIN` i les preguntes negatives

INNER JOIN descarta el que no aparella. De vegades això és justament el que no vols.

-- Quants préstecs té cada soci? Amb INNER JOIN, els socis
-- sense préstecs desapareixen del llistat.
SELECT s.soci_id, s.cognoms, p.prestec_id
FROM socis s
INNER JOIN prestecs p ON p.soci_id = s.soci_id;

Retorna 12 files i només hi apareixen 8 socis: Ramón Etxebarri (13) i Elena Roig (20) s'han esfumat.

LEFT JOIN (abreviatura de LEFT OUTER JOIN) conserva totes les files de la taula esquerra; quan no hi ha parella a la dreta, omple aquestes columnes amb NULL.

SELECT s.soci_id, s.cognoms, p.prestec_id, p.data_prestec
FROM socis s
LEFT JOIN prestecs p ON p.soci_id = s.soci_id
ORDER BY s.soci_id, p.prestec_id;

soci_id	cognoms	prestec_id	data_prestec
11	Ferran	7	2026-05-08
12	Quiroga	8	2026-05-19
13	Etxebarri	(NULL)	(NULL)
14	Alsina	1	2026-03-02
14	Alsina	4	2026-04-06
14	Alsina	9	2026-07-14
15	Pereda	2	2026-03-05
15	Pereda	10	2026-07-18
16	Bastos	3	2026-03-11
16	Bastos	6	2026-05-04
17	Salom	11	2026-07-21
18	Vendrell	5	2026-04-12
19	Miralles	12	2026-07-25
20	Roig	(NULL)	(NULL)

14 files: els 12 préstecs més les dues files "buides" d'Etxebarri i Roig.

El patró anti-join: trobar el que NO existeix

I aquí arriba un dels patrons més útils de tot SQL. Si les files sense parella són les que tenen NULL a les columnes de la dreta, n'hi ha prou de filtrar-les:

SELECT s.soci_id, s.nom, s.cognoms, s.data_alta
FROM socis s
LEFT JOIN prestecs p ON p.soci_id = s.soci_id
WHERE p.prestec_id IS NULL
ORDER BY s.soci_id;

soci_id	nom	cognoms	data_alta
13	Ramón	Etxebarri	2020-11-14
20	Elena	Roig	2024-10-01

Els dos socis que no han agafat mai res en préstec. És la diferència − de l'àlgebra relacional, implementada amb un LEFT JOIN.

Detall crucial: la columna que comproves amb IS NULL ha de ser una que mai no pugui ser nul·la a la taula dreta —la clau primària és l'elecció segura—. Si comprovessis WHERE p.data_devolucio IS NULL obtindries una altra cosa completament diferent (els préstecs oberts, més els socis sense préstecs).

Un altre cas real de BiblioRed: els exemplars que no s'han prestat mai, candidats a expurgació.

SELECT e.codi, l.titol, e.estat, e.data_adquisicio
FROM exemplars e
JOIN llibres l      ON l.llibre_id = e.llibre_id
LEFT JOIN prestecs p ON p.exemplar_id = e.exemplar_id
WHERE p.prestec_id IS NULL
ORDER BY e.codi;

codi	titol	estat	data_adquisicio
EJ-3083	El mapa del temps	disponible	2021-06-01
EJ-3088	Àlgebra per a impacients	reparacio	2020-01-15
EJ-3091	L'hivern dels ocells	disponible	2022-04-27
EJ-3093	Manual de jardineria urbana	disponible	2023-09-11
EJ-3094	Manual de jardineria urbana	baixa	2023-09-11
EJ-3095	Memòria de l'Eixample (1904)	disponible	2003-01-15

Sis exemplars que fa temps que són al prestatge sense sortir mai. Just el tipus d'informe que el full de càlcul no podia produir.

`RIGHT JOIN` i `FULL OUTER JOIN`

RIGHT JOIN és la imatge especular de LEFT JOIN: conserva totes les files de la taula dreta.

SELECT a.cognoms AS autor, l.titol
FROM llibres l
RIGHT JOIN autors a ON a.autor_id = l.autor_id
ORDER BY a.cognoms, l.titol;

autor	titol
Barreda	Àlgebra per a impacients
Barreda	Manual de jardineria urbana
Escolà	(NULL)
Follett	Els pilars de la Terra
Lemos	L'hivern dels ocells
Ordóñez	Rutes del delta
Palma	El mapa del temps
Sorrentino	Quaderns de Ravenna
Valcárcel	La casa de les marees

Nou files: els vuit autors, amb Óscar Barreda repetit per les seves dues obres i Marina Escolà amb NULL perquè encara no en té cap. Observa que "Memòria de l'Eixample (1904)" no hi apareix: és el llibre sense autor catalogat, i RIGHT JOIN conserva els autors, no els llibres.

Tota consulta RIGHT JOIN es pot reescriure com a LEFT JOIN invertint les taules, i aquesta és la forma que veuràs al 95 % del codi professional:

SELECT a.cognoms AS autor, l.titol
FROM autors a
LEFT JOIN llibres l ON l.autor_id = a.autor_id;

Consell: queda't amb LEFT JOIN. Llegir una consulta llarga en què uns JOIN van cap a l'esquerra i d'altres cap a la dreta és innecessàriament difícil.

`FULL OUTER JOIN`

Conserva les files sense parella de tots dos costats:

SELECT a.cognoms AS autor, l.titol
FROM autors a
FULL OUTER JOIN llibres l ON l.autor_id = a.autor_id
ORDER BY a.cognoms NULLS LAST, l.titol;

autor	titol
Barreda	Àlgebra per a impacients
Barreda	Manual de jardineria urbana
Escolà	(NULL)
Follett	Els pilars de la Terra
Lemos	L'hivern dels ocells
Ordóñez	Rutes del delta
Palma	El mapa del temps
Sorrentino	Quaderns de Ravenna
Valcárcel	La casa de les marees
(NULL)	Memòria de l'Eixample (1904)

Deu files: hi apareix tant l'autora sense llibres com el llibre sense autor. És la consulta d'auditoria per excel·lència: ensenya els dos costats descosits de cop.

Avís sobre SQLite

RIGHT JOIN i FULL OUTER JOIN no van existir a SQLite fins a la versió 3.39 (juny de 2022). Si el teu sqlite3 és anterior, aquestes dues consultes donaran error de sintaxi. Comprova la teva versió amb SELECT sqlite_version();. Solucions:

Reescriure el RIGHT JOIN com a LEFT JOIN amb les taules invertides (sempre possible).
Simular el FULL OUTER JOIN amb dos LEFT JOIN units per UNION (apartat 15).

LEFT JOIN i INNER JOIN, en canvi, funcionen a SQLite des de sempre.

Resum visual dels tipus de `JOIN`

Imagina dues taules mínimes aparellades per una clau:

ESQ amb claus 1, 2, 3
DRE amb claus 2, 3, 4

Tipus de `JOIN`	Files que retorna	Claus del resultat	Nre. de files
`INNER JOIN`	Només les que aparellen	2, 3	2
`LEFT JOIN`	Totes les de l'esquerra	1 (amb `NULL`), 2, 3	3
`RIGHT JOIN`	Totes les de la dreta	2, 3, 4 (amb `NULL`)	3
`FULL OUTER JOIN`	Totes les de tots dos costats	1, 2, 3, 4	4
`CROSS JOIN`	Totes les combinacions	3 × 3 parelles	9
`LEFT JOIN` + `IS NULL`	Només les de l'esquerra sense parella	1	1

I l'arbre de decisió que convé tenir al cap en escriure una consulta:

flowchart TD
    A["Quines files vull al resultat?"] --> B{"Necessito files<br/>sense correspondència?"}
    B -->|No: només les aparellades| C["INNER JOIN"]
    B -->|Sí| D{"De quin costat?"}
    D -->|"Només de la taula principal<br/>(la del FROM)"| E["LEFT JOIN"]
    D -->|Només de la secundària| F["RIGHT JOIN<br/><i>millor: gira-ho<br/>i fes servir LEFT JOIN</i>"]
    D -->|Dels dos costats| G["FULL OUTER JOIN"]
    E --> H{"Vull EXCLUSIVAMENT<br/>les que no aparellen?"}
    H -->|Sí| I["LEFT JOIN + WHERE clau_dreta IS NULL<br/><i>(anti-join)</i>"]
    H -->|No| J["LEFT JOIN a seques"]
    B -->|"Vull totes les combinacions<br/>possibles, sense aparellar"| K["CROSS JOIN"]

`SELF JOIN`: unir una taula amb ella mateixa

No és un tipus diferent de JOIN: és un INNER o LEFT JOIN normal en què les dues taules són la mateixa. Serveix per comparar files d'una taula entre si, i per això els àlies són obligatoris: cal poder distingir les dues "còpies".

Pregunta: quines parelles de socis estan donats d'alta a la mateixa sucursal?

SELECT a.cognoms AS soci_a,
       b.cognoms AS soci_b,
       a.sucursal_id
FROM socis a
INNER JOIN socis b
        ON b.sucursal_id = a.sucursal_id
       AND b.soci_id > a.soci_id
ORDER BY a.sucursal_id, a.cognoms, b.cognoms;

soci_a	soci_b	sucursal_id
Bastos	Roig	1
Etxebarri	Bastos	1
Etxebarri	Roig	1
Ferran	Bastos	1
Ferran	Etxebarri	1
Ferran	Roig	1
Alsina	Miralles	2
Alsina	Pereda	2
Pereda	Miralles	2
Quiroga	Salom	3

Deu parelles. La condició b.soci_id > a.soci_id fa dues coses alhora i és el truc que cal memoritzar:

Evita aparellar cada soci amb ell mateix (que seria a.soci_id = b.soci_id).
Evita el duplicat simètric: si surt (Ferran, Roig), no surt també (Roig, Ferran).

Sense aquesta condició obtindries 4×4 + 3×3 + 2×2 + 1×1 = 30 files en lloc de 10.

Un altre SELF JOIN útil a BiblioRed: altres obres del mateix autor.

SELECT l1.titol AS llibre, l2.titol AS altra_obra_del_mateix_autor
FROM llibres l1
INNER JOIN llibres l2 ON l2.autor_id = l1.autor_id
                     AND l2.llibre_id <> l1.llibre_id
ORDER BY l1.titol;

llibre	altra_obra_del_mateix_autor
Àlgebra per a impacients	Manual de jardineria urbana
Manual de jardineria urbana	Àlgebra per a impacients

Aquí sí que volem les dues direccions (per poder recomanar des de qualsevol dels dos llibres), per això fem servir <> en lloc de >. Óscar Barreda és l'únic autor amb dues obres al fons.

L'ús canònic del SELF JOIN al món real són les jerarquies: una taula empleats amb una columna cap_id que apunta a la mateixa taula. BiblioRed no en té cap, però el mecanisme és idèntic.

Encadenar tres o més taules

Els JOIN s'encadenen de dalt a baix: el resultat del primer s'uneix amb la taula següent, i així successivament. La consulta estrella de BiblioRed recorre cinc taules.

flowchart LR
    S["socis"] --> P["prestecs"]
    P --> E["exemplars"]
    E --> L["llibres"]
    L --> A["autors"]

SELECT s.nom || ' ' || s.cognoms AS soci,
       e.codi                    AS exemplar,
       l.titol,
       a.cognoms                 AS autor,
       p.data_prestec,
       p.data_devolucio_prevista
FROM prestecs p
INNER JOIN socis s     ON s.soci_id     = p.soci_id
INNER JOIN exemplars e ON e.exemplar_id = p.exemplar_id
INNER JOIN llibres l   ON l.llibre_id   = e.llibre_id
LEFT  JOIN autors a    ON a.autor_id    = l.autor_id
WHERE p.data_devolucio IS NULL
ORDER BY p.data_prestec;

soci	exemplar	titol	autor	data_prestec	data_devolucio_prevista
Marta Alsina	EJ-3081	El mapa del temps	Palma	2026-07-14	2026-08-04
Ivan Pereda	EJ-3084	Els pilars de la Terra	Follett	2026-07-18	2026-08-08
Diego Salom	EJ-3086	La casa de les marees	Valcárcel	2026-07-21	2026-08-11
Pau Miralles	EJ-3090	L'hivern dels ocells	Lemos	2026-07-25	2026-08-15

Aquest és el llistat que el bibliotecari vol veure cada matí. I ja podem respondre la pregunta del principi: qui té ara mateix EJ-3081 és la Marta Alsina, i l'ha de retornar el 4 d'agost de 2026.

Tres decisions d'aquesta consulta mereixen comentari:

Comencem per prestecs, no per socis. Quan encadenes diverses taules, arrencar per la taula "central" —la que té les claus foranes cap a les altres— fa la consulta molt més natural de llegir.
autors s'uneix amb LEFT JOIN. Per què? Perquè llibres.autor_id admet NULL (recorda "Memòria de l'Eixample"). Amb un INNER JOIN, si algú prestés aquest exemplar, el préstec desapareixeria de l'informe matinal sense que ningú se n'assabentés. És l'error silenciós més freqüent en encadenar taules: un INNER JOIN sobre una clau forana opcional perd files.
L'ordre en què escrius els JOIN no determina l'ordre d'execució. L'optimitzador (lliçó 01-04) decideix pel seu compte. Tu escrius perquè es llegeixi bé; ell executa perquè corri de pressa.

Filtrar a `ON` o filtrar a `WHERE`

Aquesta distinció és subtil, es pregunta a totes les entrevistes i provoca resultats incorrectes cada dia. Amb INNER JOIN tant se val on posis la condició. Amb LEFT JOIN, canvia el resultat completament.

La raó és a l'ordre d'avaluació:

ON s'aplica mentre s'aparellen les files: decideix què és parella i què no.
WHERE s'aplica després de construïda la unió: descarta files del resultat ja muntat, incloses les files plenes de NULL que el LEFT JOIN acabava de conservar.

Pregunta: "llista de tots els socis, indicant quins préstecs han fet a partir del juliol de 2026".

Amb la condició a `ON`

SELECT s.soci_id, s.cognoms, p.prestec_id, p.data_prestec
FROM socis s
LEFT JOIN prestecs p
       ON p.soci_id = s.soci_id
      AND p.data_prestec >= '2026-07-01'
ORDER BY s.soci_id;

soci_id	cognoms	prestec_id	data_prestec
11	Ferran	(NULL)	(NULL)
12	Quiroga	(NULL)	(NULL)
13	Etxebarri	(NULL)	(NULL)
14	Alsina	9	2026-07-14
15	Pereda	10	2026-07-18
16	Bastos	(NULL)	(NULL)
17	Salom	11	2026-07-21
18	Vendrell	(NULL)	(NULL)
19	Miralles	12	2026-07-25
20	Roig	(NULL)	(NULL)

Deu files: hi són tots els socis, que és el que demanava la pregunta. Els que no tenen préstecs al juliol surten amb NULL.

Amb la condició a `WHERE`

SELECT s.soci_id, s.cognoms, p.prestec_id, p.data_prestec
FROM socis s
LEFT JOIN prestecs p ON p.soci_id = s.soci_id
WHERE p.data_prestec >= '2026-07-01'
ORDER BY s.soci_id;

soci_id	cognoms	prestec_id	data_prestec
14	Alsina	9	2026-07-14
15	Pereda	10	2026-07-18
17	Salom	11	2026-07-21
19	Miralles	12	2026-07-25

Quatre files. El WHERE ha eliminat totes les files amb p.data_prestec a NULL, perquè NULL >= '2026-07-01' és DESCONEGUT. El LEFT JOIN s'ha convertit, de fet, en un INNER JOIN.

On va la condició	Efecte a `INNER JOIN`	Efecte a `LEFT JOIN`
A `ON`	Idèntic	Filtra què s'aparella; conserva totes les files de l'esquerra
A `WHERE`	Idèntic	Filtra el resultat; anul·la l'efecte del `LEFT`

Regla pràctica: en un LEFT JOIN, les condicions sobre la taula dreta van al ON; les condicions sobre la taula esquerra van al WHERE. L'única excepció és el patró anti-join WHERE clau_dreta IS NULL, on vols deliberadament aquest efecte.

Subconsultes escalars

Una subconsulta és un SELECT dins d'un altre SELECT, entre parèntesis. La varietat més simple és l'escalar: retorna exactament una fila i una columna, és a dir, un valor solt, i per tant es pot fer servir a qualsevol lloc on encaixi un valor.

Al `WHERE`

-- Llibres publicats després de "El mapa del temps"
SELECT titol, any_publicacio
FROM llibres
WHERE any_publicacio > (SELECT any_publicacio FROM llibres WHERE llibre_id = 331)
ORDER BY any_publicacio;

titol	any_publicacio
Quaderns de Ravenna	2012
La casa de les marees	2015
Rutes del delta	2017
Àlgebra per a impacients	2019
L'hivern dels ocells	2021
Manual de jardineria urbana	2023

La subconsulta s'executa una vegada, retorna 2008, i la consulta externa l'utilitza com si l'haguessis escrit a mà. L'avantatge és que no cal saber el valor per endavant: si demà es corregeix l'any d'aquest llibre, la consulta continua sent correcta.

Si una subconsulta escalar retorna més d'una fila, el gestor dóna error:

SELECT titol FROM llibres
WHERE any_publicacio > (SELECT any_publicacio FROM llibres WHERE editorial = 'Editorial Andana');

ERROR:  more than one row returned by a subquery used as an expression

Si en retorna zero, en canvi, no hi ha error: el resultat és NULL, i la comparació sencera passa a DESCONEGUT, així que la consulta externa retorna zero files… en silenci. Una altra aplicació de la lògica de tres valors.

A la llista del `SELECT`

SELECT e.codi,
       e.estat,
       (SELECT l.titol FROM llibres l WHERE l.llibre_id = e.llibre_id) AS titol
FROM exemplars e
WHERE e.sucursal_id = 4
ORDER BY e.codi;

codi	estat	titol
EJ-3088	reparacio	Àlgebra per a impacients
EJ-3092	disponible	Rutes del delta

Això és equivalent a un LEFT JOIN amb llibres. Com a norma general, prefereix el JOIN: és més llegible, més flexible (pots portar diverses columnes de la taula unida) i l'optimitzador sol tractar-lo millor. La subconsulta al SELECT es reserva per a quan necessites una única dada calculada i el JOIN complicaria la consulta.

Subconsultes amb `IN`, `EXISTS` i `NOT EXISTS`

Quan la subconsulta retorna diverses files, no es pot comparar amb =, però sí preguntar per pertinença o per existència.

`IN`

-- Socis que tenen algun préstec obert
SELECT soci_id, nom, cognoms
FROM socis
WHERE soci_id IN (SELECT soci_id FROM prestecs WHERE data_devolucio IS NULL)
ORDER BY soci_id;

soci_id	nom	cognoms
14	Marta	Alsina
15	Ivan	Pereda
17	Diego	Salom
19	Pau	Miralles

Compara-ho amb la versió JOIN. Amb JOIN caldria afegir-hi DISTINCT per no repetir els socis amb diversos préstecs oberts; amb IN no cal, perquè la pertinença a un conjunt és sí o no. Aquest és el seu avantatge principal.

`NOT IN` i el parany del `NULL`

-- Llibres que ningú no ha reservat mai
SELECT llibre_id, titol
FROM llibres
WHERE llibre_id NOT IN (SELECT llibre_id FROM reserves)
ORDER BY llibre_id;

llibre_id	titol
334	Àlgebra per a impacients
335	Quaderns de Ravenna
337	Rutes del delta
338	Manual de jardineria urbana
339	Memòria de l'Eixample (1904)

Funciona perquè reserves.llibre_id és NOT NULL. Ara la mateixa idea sobre una columna que sí que admet nuls:

-- Autors sense cap obra al fons
SELECT autor_id, cognoms
FROM autors
WHERE autor_id NOT IN (SELECT autor_id FROM llibres);

(0 rows)

Zero files, quan la resposta correcta és "Marina Escolà". El motiu el vam veure a 02-01: la subconsulta retorna {1, 2, 3, 4, 5, 6, 7, NULL} (el NULL és el de "Memòria de l'Eixample"), i 8 NOT IN (..., NULL) es tradueix en 8 <> 1 AND ... AND 8 <> NULL, l'últim terme del qual és DESCONEGUT. I CERT AND DESCONEGUT és DESCONEGUT, que no passa el filtre. Amb un sol NULL a la llista, NOT IN no retorna mai cap fila.

Les tres solucions, de pitjor a millor:

-- 1) Filtrar els NULL a mà: funciona, però cal recordar-se'n sempre
SELECT autor_id, cognoms FROM autors
WHERE autor_id NOT IN (SELECT autor_id FROM llibres WHERE autor_id IS NOT NULL);

-- 2) Anti-join amb LEFT JOIN
SELECT a.autor_id, a.cognoms FROM autors a
LEFT JOIN llibres l ON l.autor_id = a.autor_id
WHERE l.llibre_id IS NULL;

-- 3) NOT EXISTS: immune al problema per construcció
SELECT a.autor_id, a.cognoms FROM autors a
WHERE NOT EXISTS (SELECT 1 FROM llibres l WHERE l.autor_id = a.autor_id);

Les tres retornen ara:

autor_id	cognoms
8	Escolà

`EXISTS` i `NOT EXISTS`

EXISTS no compara valors: pregunta si la subconsulta retorna almenys una fila. Retorna TRUE o FALSE, mai DESCONEGUT, i per això és immune al parany anterior.

-- Socis amb algun préstec obert (la mateixa pregunta que amb IN)
SELECT s.soci_id, s.nom, s.cognoms
FROM socis s
WHERE EXISTS (SELECT 1
              FROM prestecs p
              WHERE p.soci_id = s.soci_id
                AND p.data_devolucio IS NULL)
ORDER BY s.soci_id;

soci_id	nom	cognoms
14	Marta	Alsina
15	Ivan	Pereda
17	Diego	Salom
19	Pau	Miralles

El SELECT 1 és una convenció: com que només importa si hi ha files, no quines files, s'hi posa una constant. SELECT * funcionaria igual i el gestor l'optimitza idènticament.

-- Socis que no han agafat mai res en préstec
SELECT s.soci_id, s.nom, s.cognoms
FROM socis s
WHERE NOT EXISTS (SELECT 1 FROM prestecs p WHERE p.soci_id = s.soci_id)
ORDER BY s.soci_id;

soci_id	nom	cognoms
13	Ramón	Etxebarri
20	Elena	Roig

Mateix resultat que l'anti-join de l'apartat 4. Tres maneres d'expressar la mateixa pregunta:

Forma	Llegibilitat	Risc amb `NULL`	Quan fer-la servir
`LEFT JOIN ... IS NULL`	Mitjana	Cap (si comproves la clau primària)	Quan a més necessites columnes de la taula dreta
`NOT IN (subconsulta)`	Alta	Alt	Només si la columna és `NOT NULL`
`NOT EXISTS`	Alta	Cap	L'opció per defecte

Subconsultes correlacionades

Les subconsultes d'EXISTS que acabes de veure tenen una particularitat: esmenten una columna de la consulta externa (s.soci_id). Això les converteix en correlacionades: no es poden executar per si soles, perquè depenen de la fila que s'estigui avaluant en cada moment.

	Subconsulta independent	Subconsulta correlacionada
Es pot executar a part?	Sí	No
Quantes vegades s'avalua?	Una	Conceptualment, una per fila externa
Exemple	`WHERE llibre_id IN (SELECT llibre_id FROM reserves)`	`WHERE EXISTS (SELECT 1 FROM reserves r WHERE r.llibre_id = l.llibre_id)`

Un exemple de BiblioRed que respon una pregunta genuïnament difícil d'una altra manera: quins exemplars pertanyen a un llibre que té alguna reserva activa? (són els que cal apartar tan bon punt tornin al taulell).

SELECT e.codi, e.estat, e.sucursal_id, l.titol
FROM exemplars e
INNER JOIN llibres l ON l.llibre_id = e.llibre_id
WHERE EXISTS (SELECT 1
              FROM reserves r
              WHERE r.llibre_id = e.llibre_id
                AND r.estat     = 'activa')
ORDER BY e.codi;

codi	estat	sucursal_id	titol
EJ-3081	prestat	2	El mapa del temps
EJ-3082	disponible	1	El mapa del temps
EJ-3083	disponible	3	El mapa del temps
EJ-3084	prestat	1	Els pilars de la Terra
EJ-3085	disponible	2	Els pilars de la Terra

Cinc exemplars en alerta: els tres de "El mapa del temps" (dues reserves actives) i els dos de "Els pilars de la Terra" (una).

Sobre el rendiment: la descripció "s'avalua una vegada per fila externa" és el model mental, no el que passa necessàriament. Els optimitzadors moderns solen transformar una subconsulta correlacionada en un JOIN internament. Tot i així, sobre taules grans convé mesurar-ho; a la lliçó 06-03 aprendràs a comprovar-ho amb EXPLAIN.

Taules derivades: subconsultes al `FROM`

Una subconsulta també pot ocupar el lloc d'una taula al FROM. S'anomena taula derivada i, com qualsevol taula, necessita un àlies.

SELECT su.nom AS sucursal,
       ob.codi,
       ob.data_prestec
FROM (SELECT p.prestec_id, p.data_prestec, e.codi, e.sucursal_id
      FROM prestecs p
      INNER JOIN exemplars e ON e.exemplar_id = p.exemplar_id
      WHERE p.data_devolucio IS NULL) AS ob
INNER JOIN sucursals su ON su.sucursal_id = ob.sucursal_id
ORDER BY su.nom, ob.data_prestec;

sucursal	codi	data_prestec
Centre	EJ-3084	2026-07-18
Centre	EJ-3086	2026-07-21
Nord	EJ-3081	2026-07-14
Nord	EJ-3090	2026-07-25

Els quatre préstecs oberts, repartits entre les sucursals on viu cada exemplar. La taula derivada ob (d'"oberts") existeix només durant la consulta.

Les taules derivades resolen casos en què necessites treballar sobre un resultat intermedi, sobretot quan aquest intermedi inclou agregats —cosa que veurem a la lliçó 02-05—. El seu inconvenient és la llegibilitat: si en nies dues o tres, la consulta es converteix en un laberint de parèntesis que cal llegir de dins cap enfora.

Expressions de taula comuna: `WITH`

Una CTE (Common Table Expression, expressió de taula comuna) és una taula derivada a la qual es dóna nom abans de fer-la servir. Mateixa potència, llegibilitat incomparablement millor.

WITH oberts AS (
    SELECT p.prestec_id, p.data_prestec, e.codi, e.sucursal_id
    FROM prestecs p
    INNER JOIN exemplars e ON e.exemplar_id = p.exemplar_id
    WHERE p.data_devolucio IS NULL
)
SELECT su.nom AS sucursal,
       ob.codi,
       ob.data_prestec
FROM oberts ob
INNER JOIN sucursals su ON su.sucursal_id = ob.sucursal_id
ORDER BY su.nom, ob.data_prestec;

Retorna exactament el mateix que l'apartat anterior, però ara la consulta es llegeix de dalt a baix, com un procediment: "primer calculo els oberts, després els creuo amb les sucursals".

Diverses CTE encadenades

Se separen per comes, i cadascuna pot fer servir les anteriors:

WITH oberts AS (
    SELECT p.prestec_id, p.soci_id, p.data_prestec, e.codi
    FROM prestecs p
    INNER JOIN exemplars e ON e.exemplar_id = p.exemplar_id
    WHERE p.data_devolucio IS NULL
),
socis_nord AS (
    SELECT soci_id, nom, cognoms
    FROM socis
    WHERE sucursal_id = 2
)
SELECT sn.nom || ' ' || sn.cognoms AS soci,
       ob.codi,
       ob.data_prestec
FROM socis_nord sn
INNER JOIN oberts ob ON ob.soci_id = sn.soci_id
ORDER BY ob.data_prestec;

soci	codi	data_prestec
Marta Alsina	EJ-3081	2026-07-14
Ivan Pereda	EJ-3084	2026-07-18
Pau Miralles	EJ-3090	2026-07-25

Tres dels quatre préstecs oberts corresponen a socis de la sucursal Nord; el quart és de Diego Salom, donat d'alta a Sud.

Avantatges de les CTE:

Llegibilitat: cada bloc té nom i propòsit.
Reutilització: una CTE es pot referenciar diverses vegades a la consulta principal, mentre que una taula derivada s'hauria d'escriure dues vegades.
Depuració: pots executar el contingut de la CTE per separat per veure què produeix.

Notes de dialecte:

WITH és estàndard i funciona a PostgreSQL (des de 8.4) i SQLite (des de 3.8.3).
Existeix WITH RECURSIVE per recórrer jerarquies i grafs (arbres de categories, llistes de materials). És potent i queda fora de l'abast d'aquest curs introductori.
Històricament PostgreSQL tractava cada CTE com una barrera d'optimització; des de la versió 12 les integra a la consulta principal llevat que escriguis MATERIALIZED. Si treballes amb una versió anterior i notes lentitud, aquesta pot ser la causa.

Operadors de conjunt: `UNION`, `INTERSECT`, `EXCEPT`

Els JOIN combinen taules d'ample (afegeixen columnes). Els operadors de conjunt les combinen d'alt (apilen files). Són la unió ∪, la intersecció ∩ i la diferència − de l'àlgebra relacional.

Per fer-los servir, les dues consultes han de ser compatibles: mateix nombre de columnes i tipus compatibles, en el mateix ordre. Els noms de columna els posa la primera consulta.

`UNION` i `UNION ALL`

-- Socis "en moviment": amb préstec obert o amb reserva activa
SELECT soci_id FROM prestecs WHERE data_devolucio IS NULL
UNION
SELECT soci_id FROM reserves WHERE estat = 'activa'
ORDER BY soci_id;

soci_id
14
15
16
17
18
19

Sis socis. La primera consulta retorna {14, 15, 17, 19} i la segona {16, 18, 15}; Ivan Pereda (15) és a totes dues i hi apareix una sola vegada, perquè UNION elimina duplicats.

SELECT soci_id FROM prestecs WHERE data_devolucio IS NULL
UNION ALL
SELECT soci_id FROM reserves WHERE estat = 'activa'
ORDER BY soci_id;

soci_id
14
15
15
16
17
18
19

Set files: UNION ALL no elimina duplicats. I precisament per això és més ràpid: no ha d'ordenar ni comparar res. Regla pràctica: fes servir UNION ALL llevat que necessitis la deduplicació. Molta gent escriu UNION per costum i en paga el cost sense motiu.

`INTERSECT`

-- Socis que tenen préstec obert I A MÉS reserva activa
SELECT soci_id FROM prestecs WHERE data_devolucio IS NULL
INTERSECT
SELECT soci_id FROM reserves WHERE estat = 'activa';

soci_id
15

Ivan Pereda: té "Els pilars de la Terra" en préstec i "El mapa del temps" reservat.

`EXCEPT`

-- Socis amb préstec obert però SENSE cap reserva activa
SELECT soci_id FROM prestecs WHERE data_devolucio IS NULL
EXCEPT
SELECT soci_id FROM reserves WHERE estat = 'activa'
ORDER BY soci_id;

soci_id
14
17
19

EXCEPT no és simètric: A EXCEPT B no és el mateix que B EXCEPT A. Si invertissis l'ordre obtindries {16, 18}, els socis amb reserva activa i sense préstecs oberts.

Detalls a tenir en compte

INTERSECT i EXCEPT també eliminen duplicats per defecte; existeixen INTERSECT ALL i EXCEPT ALL.
L'ORDER BY va al final, una sola vegada, i ordena el resultat combinat. No en pots posar un a cada branca (llevat que sigui entre parèntesis amb LIMIT).
A Oracle, EXCEPT es diu MINUS.
SQLite admet UNION, UNION ALL, INTERSECT i EXCEPT des de sempre; és el seu punt fort davant dels JOIN externs.

Un ús molt pràctic a SQLite antic: simular un FULL OUTER JOIN.

SELECT a.cognoms, l.titol FROM autors a LEFT JOIN llibres l ON l.autor_id = a.autor_id
UNION
SELECT a.cognoms, l.titol FROM llibres l LEFT JOIN autors a ON a.autor_id = l.autor_id;

Les deu files de l'apartat 5, sense necessitar FULL OUTER JOIN.

Errors Habituals i Consells

Oblidar la condició d'unió. Amb la sintaxi antiga de comes produeix un producte cartesià silenciós. Fes servir sempre JOIN ... ON.
Fer servir INNER JOIN sobre una clau forana que admet NULL. Perds files sense previ avís. Si la columna pot ser nul·la, LEFT JOIN.
Posar la condició de la taula dreta al WHERE d'un LEFT JOIN. El converteix en INNER JOIN i et quedes sense les files que volies conservar. Va al ON.
Comprovar IS NULL sobre la columna equivocada en un anti-join. Fes servir sempre la clau primària de la taula dreta, que mai no pot ser nul·la per si mateixa.
NOT IN amb una subconsulta que pot retornar NULL. Zero files, sempre, en silenci. Fes servir NOT EXISTS.
Confondre "més files de les esperades" amb un error del JOIN. Si un soci té tres préstecs, hi apareixerà tres vegades: això és correcte. El problema sorgeix en comptar (COUNT) sobre aquest resultat, i ho veurem a la lliçó 02-05.
Encadenar JOIN sense àlies. Amb cinc taules i columnes homònimes, una consulta sense àlies és il·legible i fins i tot ambigua per al gestor.
Fer servir NATURAL JOIN. Aparella per noms coincidents i canvia de significat quan algú afegeix una columna.
Niar taules derivades de tres nivells. Converteix-les en CTE amb WITH: mateix resultat, la meitat de temps per entendre-la.
Consell: quan una consulta multitaula retorni alguna cosa estranya, treu-li clàusules fins que funcioni. Executa primer el JOIN pelat amb SELECT *, mira quantes files en surten i ves afegint-hi condicions d'una en una.
Consell: escriu sempre la condició d'unió en l'ordre ON taula_nova.columna = taula_ja_present.columna. És una convenció menor, però en llegir una consulta de cinc taules s'agraeix moltíssim.

Exercicis

Exercici 1: Reunions bàsiques

Llista els exemplars amb el títol del llibre al qual pertanyen i el nom de la seva sucursal. Ordena per sucursal i codi.
Mostra les reserves actives amb el nom del soci i el títol del llibre reservat.
Llista tots els llibres amb el cognom del seu autor, inclosos els que no tenen autor catalogat.
Mostra els préstecs retornats amb retard, indicant soci, títol i les dues dates.

Exercici 2: Preguntes negatives

Quins llibres no tenen cap exemplar a la sucursal Centre (sucursal_id = 1)? Resol-ho amb NOT EXISTS.
Quins socis no han fet cap reserva mai? Resol-ho de dues maneres: amb anti-join i amb NOT EXISTS.
Quines sucursals no tenen cap exemplar en estat prestat?
Quins autors tenen obra al fons però cap de les seves obres no s'ha prestat mai?

Exercici 3: Consultes compostes

Fent servir una CTE, obtén els exemplars disponibles de llibres que tenen alguna reserva activa, amb el seu codi, títol i sucursal. És la llista d'"apartar per a reserves".
Amb operadors de conjunt, obtén els identificadors dels socis que han fet alguna reserva però mai un préstec.
Llista, per a cada sucursal, els socis donats d'alta en ella i els exemplars que custodia… i explica per què no s'ha de fer amb un sol JOIN de tres taules.

Solucions

Solució 1

-- 1
SELECT su.nom AS sucursal, e.codi, l.titol, e.estat
FROM exemplars e
INNER JOIN llibres l    ON l.llibre_id   = e.llibre_id
INNER JOIN sucursals su ON su.sucursal_id = e.sucursal_id
ORDER BY su.nom, e.codi;

Quinze files. Les dues primeres i les dues últimes:

sucursal	codi	titol	estat
Centre	EJ-3082	El mapa del temps	disponible
Centre	EJ-3084	Els pilars de la Terra	prestat
…	…	…	…
Sud	EJ-3089	Quaderns de Ravenna	disponible
Sud	EJ-3094	Manual de jardineria urbana	baixa

-- 2
SELECT s.nom || ' ' || s.cognoms AS soci, l.titol, r.data_reserva
FROM reserves r
INNER JOIN socis s   ON s.soci_id   = r.soci_id
INNER JOIN llibres l ON l.llibre_id = r.llibre_id
WHERE r.estat = 'activa'
ORDER BY r.data_reserva;

soci	titol	data_reserva
Núria Bastos	El mapa del temps	2026-07-20
Lucía Vendrell	Els pilars de la Terra	2026-07-22
Ivan Pereda	El mapa del temps	2026-07-28

-- 3  LEFT JOIN, perquè llibres.autor_id admet NULL
SELECT l.titol, a.cognoms AS autor
FROM llibres l
LEFT JOIN autors a ON a.autor_id = l.autor_id
ORDER BY l.titol;

titol	autor
Àlgebra per a impacients	Barreda
El mapa del temps	Palma
Els pilars de la Terra	Follett
La casa de les marees	Valcárcel
L'hivern dels ocells	Lemos
Manual de jardineria urbana	Barreda
Memòria de l'Eixample (1904)	(NULL)
Quaderns de Ravenna	Sorrentino
Rutes del delta	Ordóñez

Amb INNER JOIN hauries perdut "Memòria de l'Eixample (1904)".

-- 4
SELECT s.nom || ' ' || s.cognoms AS soci,
       l.titol,
       p.data_devolucio_prevista,
       p.data_devolucio,
       p.recarrec
FROM prestecs p
INNER JOIN socis s     ON s.soci_id     = p.soci_id
INNER JOIN exemplars e ON e.exemplar_id = p.exemplar_id
INNER JOIN llibres l   ON l.llibre_id   = e.llibre_id
WHERE p.data_devolucio > p.data_devolucio_prevista
ORDER BY p.data_prestec;

soci	titol	data_devolucio_prevista	data_devolucio	recarrec
Ivan Pereda	El mapa del temps	2026-03-26	2026-04-02	1.40
Lucía Vendrell	Quaderns de Ravenna	2026-05-03	2026-05-10	1.40
Sonia Quiroga	Rutes del delta	2026-06-09	2026-06-30	4.20

Solució 2

-- 1
SELECT l.llibre_id, l.titol
FROM llibres l
WHERE NOT EXISTS (SELECT 1 FROM exemplars e
                  WHERE e.llibre_id = l.llibre_id AND e.sucursal_id = 1)
ORDER BY l.llibre_id;

llibre_id	titol
334	Àlgebra per a impacients
335	Quaderns de Ravenna
337	Rutes del delta

-- 2a  Anti-join
SELECT s.soci_id, s.cognoms
FROM socis s
LEFT JOIN reserves r ON r.soci_id = s.soci_id
WHERE r.reserva_id IS NULL
ORDER BY s.soci_id;

-- 2b  NOT EXISTS
SELECT s.soci_id, s.cognoms
FROM socis s
WHERE NOT EXISTS (SELECT 1 FROM reserves r WHERE r.soci_id = s.soci_id)
ORDER BY s.soci_id;

soci_id	cognoms
12	Quiroga
13	Etxebarri
17	Salom
19	Miralles
20	Roig

Han reservat alguna vegada els socis 11, 14, 15, 16 i 18; els altres cinc, mai.

-- 3
SELECT su.sucursal_id, su.nom
FROM sucursals su
WHERE NOT EXISTS (SELECT 1 FROM exemplars e
                  WHERE e.sucursal_id = su.sucursal_id AND e.estat = 'prestat')
ORDER BY su.sucursal_id;

sucursal_id	nom
3	Sud
4	Est

-- 4  Doble negació: autors AMB llibres però SENSE préstecs d'aquests llibres
SELECT a.autor_id, a.cognoms
FROM autors a
WHERE EXISTS (SELECT 1 FROM llibres l WHERE l.autor_id = a.autor_id)
  AND NOT EXISTS (SELECT 1
                  FROM llibres l
                  INNER JOIN exemplars e ON e.llibre_id   = l.llibre_id
                  INNER JOIN prestecs  p ON p.exemplar_id = e.exemplar_id
                  WHERE l.autor_id = a.autor_id)
ORDER BY a.autor_id;

(0 rows)

Cap autor no compleix les dues condicions. Óscar Barreda (4) semblava candidat, perquè "Manual de jardineria urbana" no s'ha prestat mai, però la seva altra obra, "Àlgebra per a impacients", sí (préstec 4). És un bon recordatori que en una condició "cap de les seves obres" cal examinar totes les obres de l'autor, no d'una en una.

Solució 3

-- 1
WITH reservats AS (
    SELECT DISTINCT llibre_id FROM reserves WHERE estat = 'activa'
)
SELECT e.codi, l.titol, su.nom AS sucursal
FROM exemplars e
INNER JOIN reservats rv ON rv.llibre_id   = e.llibre_id
INNER JOIN llibres l    ON l.llibre_id    = e.llibre_id
INNER JOIN sucursals su ON su.sucursal_id = e.sucursal_id
WHERE e.estat = 'disponible'
ORDER BY e.codi;

codi	titol	sucursal
EJ-3082	El mapa del temps	Centre
EJ-3083	El mapa del temps	Sud
EJ-3085	Els pilars de la Terra	Nord

Tres exemplars que el personal ha d'apartar. El DISTINCT de la CTE és important: "El mapa del temps" té dues reserves actives, i sense ell cada exemplar seu hi apareixeria duplicat.

-- 2
SELECT soci_id FROM reserves
EXCEPT
SELECT soci_id FROM prestecs
ORDER BY soci_id;

(0 rows)

Els cinc socis que han reservat alguna vegada (11, 14, 15, 16, 18) han fet també algun préstec. Prova l'operació inversa per veure la diferència:

SELECT soci_id FROM prestecs
EXCEPT
SELECT soci_id FROM reserves
ORDER BY soci_id;

soci_id
12
17
19

-- 3  La consulta "ingènua"
SELECT su.nom, s.cognoms, e.codi
FROM sucursals su
LEFT JOIN socis s     ON s.sucursal_id = su.sucursal_id
LEFT JOIN exemplars e ON e.sucursal_id = su.sucursal_id;

Per què no s'ha de fer així: socis i exemplars no estan relacionades entre si; totes dues pengen de sucursals de manera independent. En unir-les a la mateixa consulta es produeix un producte cartesià dins de cada sucursal: la sucursal Centre té 4 socis i 6 exemplars, així que genera 24 files. El total és 4×6 + 3×4 + 2×3 + 1×2 = 24 + 12 + 6 + 2 = 44 files, cap de les quals no significa res: aparellen la Núria Bastos amb un exemplar que no ha tocat.

Aquest fenomen té nom —explosió de files o fan trap— i és una de les causes més freqüents de recomptes inflats. Les dues solucions correctes:

-- a) Dues consultes separades, que és el que demanava la pregunta de veritat
SELECT su.nom, s.cognoms FROM sucursals su
LEFT JOIN socis s ON s.sucursal_id = su.sucursal_id ORDER BY su.nom;

SELECT su.nom, e.codi FROM sucursals su
LEFT JOIN exemplars e ON e.sucursal_id = su.sucursal_id ORDER BY su.nom;

-- b) Una sola consulta, resumint cada branca per separat abans d'unir-les.
--    Això necessita funcions d'agregat: és exactament el tema
--    de la lliçó següent, 02-05.

Conclusió

Aquesta lliçó ha convertit les set taules aïllades de BiblioRed en un sistema consultable:

Les dades estan repartides a propòsit —cada fet en un sol lloc— i es recomponen en consultar amb la reunió ⋈ de l'àlgebra relacional.
El producte cartesià (CROSS JOIN) és el punt de partida conceptual i l'accident clàssic de la sintaxi antiga de comes.
INNER JOIN ... ON retorna només el que aparella; els àlies de taula són gairebé obligatoris i NATURAL JOIN és màgia que convé evitar.
LEFT JOIN conserva la taula esquerra, i la seva combinació amb WHERE clau_dreta IS NULL —l'anti-join— és el patró per respondre tota pregunta que comenci per "els que mai no…": socis sense préstecs, exemplars mai prestats.
RIGHT JOIN i FULL OUTER JOIN completen el quadre (i van arribar tard a SQLite, a la versió 3.39).
El SELF JOIN compara files d'una taula amb ella mateixa, amb la condició > per no duplicar parelles simètriques.
Encadenar cinc taules —soci → préstec → exemplar → llibre → autor— respon les preguntes reals del taulell, sempre que facis servir LEFT JOIN on la clau forana admeti nuls.
Filtrar a ON no és el mateix que filtrar a WHERE en un LEFT JOIN: el WHERE el degrada a INNER JOIN.
Les subconsultes en les seves cinc formes: escalar, amb IN, amb EXISTS/NOT EXISTS, correlacionades i com a taula derivada al FROM. Amb una regla gravada a foc: NOT EXISTS en comptes de NOT IN quan hi pugui haver NULL.
Les CTE amb WITH, que converteixen una consulta laberíntica en un procediment llegible de dalt a baix.
Els operadors de conjunt: UNION (dedupla), UNION ALL (més ràpid), INTERSECT i EXCEPT (que no és simètric).

Fixa't que hem fregat repetidament un límit: podem llistar els préstecs de cada soci, però no comptar-los; podem veure els exemplars de cada sucursal, però no quants n'hi ha; l'exercici 3.3 s'ha quedat a mitges perquè per resumir dues branques calia alguna cosa que encara no tenim.

Aquesta cosa arriba a la lliçó 02-05, Agregació i Agrupació de Dades: COUNT, SUM, AVG, MIN i MAX, la clàusula GROUP BY, la diferència entre HAVING i WHERE, l'ordre lògic en què s'executa realment una consulta i —molt important després del que acabem de veure— el problema de comptar files inflades per un JOIN. Amb ella, BiblioRed passarà de respondre "què hi ha" a respondre "quant n'hi ha".

Consultes Multitaula: JOIN i Subconsultes

Contingut

Per què cal recompondre les dades

Producte cartesià i CROSS JOIN

La forma antiga, i l'accident clàssic

INNER JOIN: la reunió bàsica

Àlies de taula

Combinar JOIN amb WHERE

USING i NATURAL JOIN

LEFT JOIN i les preguntes negatives

El patró anti-join: trobar el que NO existeix

RIGHT JOIN i FULL OUTER JOIN

FULL OUTER JOIN

Avís sobre SQLite

Resum visual dels tipus de JOIN

SELF JOIN: unir una taula amb ella mateixa

Encadenar tres o més taules

Filtrar a ON o filtrar a WHERE

Amb la condició a ON

Amb la condició a WHERE

Subconsultes escalars

Al WHERE

A la llista del SELECT

Subconsultes amb IN, EXISTS i NOT EXISTS

IN

NOT IN i el parany del NULL

EXISTS i NOT EXISTS

Subconsultes correlacionades

Taules derivades: subconsultes al FROM

Expressions de taula comuna: WITH