JOIN
Mettere insieme due tabelle, e vedere chi manca
- 18 minuti di lettura
Nadia Russo lavora all'ufficio oggetti smarriti. La tabella oggetti_smarriti ha una colonna stazione_id: dove è stato trovato l'oggetto. Ma stazione_id è un numero.
SELECT id, descrizione, stazione_id
FROM oggetti_smarriti
WHERE id IN (2, 3, 15)
ORDER BY id; id | descrizione | stazione_id
----+-------------------+-------------
2 | Ombrello nero | 1
3 | Mazzo di chiavi | 2
15 | Cuffie senza fili | 1
(3 rows)A un passeggero non puoi dire «le cuffie sono nella stazione 1». Serve il nome. Nel capitolo Le relazioni lo facevi a mano: prima cercavi l'id nella tabella stazioni, poi lo ricopiavi in una seconda query. Funziona, ma è lungo e si sbaglia. La JOIN lo fa il database, in una query sola.
È l'indicizzare, fatto dal database
Lo hai già fatto in Python, nel capitolo I dizionari: per trovare una cosa dal suo id si costruisce un dizionario con l'id come chiave.
stazioni = [
{"id": 1, "nome": "Porta Codice"},
{"id": 2, "nome": "Via Variabile"},
{"id": 6, "nome": "Vecchia Fiera"},
]
oggetti = [
{"descrizione": "Ombrello nero", "stazione_id": 1},
{"descrizione": "Mazzo di chiavi", "stazione_id": 2},
{"descrizione": "Cuffie senza fili", "stazione_id": 1},
]
nome_della_stazione = {}
for stazione in stazioni:
nome_della_stazione[stazione["id"]] = stazione["nome"]
for oggetto in oggetti:
print(nome_della_stazione[oggetto["stazione_id"]], "|", oggetto["descrizione"])Porta Codice | Ombrello nero
Via Variabile | Mazzo di chiavi
Porta Codice | Cuffie senza filiUna tabella ha l'id, l'altra ha un numero che punta a quell'id: la chiave esterna. Il database fa lo stesso lavoro del dizionario nome_della_stazione, ma con migliaia di righe e senza che tu scriva il ciclo.
JOIN … ON: le coppie che tornano
Ecco la stessa domanda in SQL:
SELECT s.nome, o.descrizione
FROM stazioni s
JOIN oggetti_smarriti o ON o.stazione_id = s.id
WHERE s.id IN (1, 2, 6, 8)
ORDER BY s.id, o.id; nome | descrizione
---------------+--------------------------
Porta Codice | Ombrello nero
Porta Codice | Telefono con cover rossa
Porta Codice | Cuffie senza fili
Via Variabile | Mazzo di chiavi
Via Variabile | Libro d'avventura
(5 rows)(Il WHERE s.id IN (1, 2, 6, 8) serve solo a tenere il risultato corto. Lo spieghiamo tra poco.)
Come si legge: «per ogni stazione, prendi tutti gli oggetti il cui stazione_id è uguale all'id della stazione». La condizione dopo ON decide quali coppie (una riga della prima tabella, una riga della seconda) stanno insieme.
Il disegno
Guarda cosa succede alle quattro stazioni del WHERE. A sinistra la tabella stazioni, a destra oggetti_smarriti: le frecce sono le coppie che rispettano ON.
stazioni oggetti_smarriti (stazione_id)
┌────┬────────────────┐ ┌────┬──────────────────────────┬─────────────┐
│ 1 │ Porta Codice │ ◀──── │ 2 │ Ombrello nero │ 1 │
│ │ │ ◀──── │ 5 │ Telefono con cover rossa │ 1 │
│ │ │ ◀──── │ 15 │ Cuffie senza fili │ 1 │
├────┼────────────────┤ ├────┼──────────────────────────┼─────────────┤
│ 2 │ Via Variabile │ ◀──── │ 3 │ Mazzo di chiavi │ 2 │
│ │ │ ◀──── │ 13 │ Libro d'avventura │ 2 │
├────┼────────────────┤ └────┴──────────────────────────┴─────────────┘
│ 6 │ Vecchia Fiera │ (nessun oggetto punta a 6)
│ 8 │ Corso Funzione │ (nessun oggetto punta a 8)
└────┴────────────────┘Tre cose da vedere:
- Porta Codice compare tre volte: una coppia per ogni oggetto. Il risultato ha una riga per coppia, non per stazione.
- Via Variabile compare due volte, perché ha due oggetti.
- Vecchia Fiera e Corso Funzione non compaiono: non hanno nessuna coppia, quindi nessuna riga.
La JOIN semplice (si chiama anche INNER JOIN, ma la parola INNER si può omettere) tiene solo le coppie che tornano. Chi non ha coppie sparisce.
I soprannomi
Nelle query sopra c'è stazioni s e oggetti_smarriti o. La s e la o sono soprannomi (in SQL si dice alias): un nome corto che dai a una tabella, subito dopo il suo nome. Da lì in poi la tabella si chiama con il soprannome: s.nome, o.descrizione.
Servono per due motivi.
1 · Scrivere meno. o.stazione_id è più corto di oggetti_smarriti.stazione_id.
2 · Dire di quale tabella parli. stazioni e personale hanno tutte e due una colonna id e una colonna nome. Se scrivi solo nome, PostgreSQL non sa quale:
SELECT id, nome
FROM personale
JOIN stazioni ON stazioni.id = personale.stazione_id;ERROR: column reference "id" is ambiguous
LINE 1: SELECT id, nome FROM personale JOIN stazioni ON stazioni.id ...
^«Ambiguous» vuol dire ambigua: la colonna c'è in più tabelle. La cura è scrivere p.id o s.id.
La regola dei soprannomi, in una frase: appena dai un soprannome a una tabella, usa solo quello. Se scrivi FROM stazioni s e poi stazioni.id, PostgreSQL risponde:
ERROR: invalid reference to FROM-clause entry for table "stazioni"
LINE 1: ...ioni s JOIN oggetti_smarriti o ON o.stazione_id = stazioni.i...
^
HINT: Perhaps you meant to reference the table alias "s".E se lo dimentichi dall'altra parte (oggetti_smarriti senza soprannome, poi o.descrizione):
ERROR: missing FROM-clause entry for table "o"
LINE 1: SELECT s.nome, o.descrizione FROM stazioni s JOIN oggetti_sm...
^Le due righe vogliono dire la stessa cosa: il nome che hai usato non è quello che hai dato.
In questo libro i soprannomi sono di una lettera (c per corse, t per treni, p per personale, s per stazioni). Quando la stessa tabella entra due volte ne servono due diversi: lo vedi più sotto.
JOIN con WHERE e ORDER BY
La JOIN fa parte del FROM. Tutto il resto lo conosci già e si scrive dopo. Il WHERE filtra le coppie, ORDER BY le mette in fila, e nel SELECT scegli colonne di tutte e due le tabelle.
SELECT s.nome, o.descrizione
FROM stazioni s
JOIN oggetti_smarriti o ON o.stazione_id = s.id
WHERE s.id = 1 AND o.ritirato_il IS NULL; nome | descrizione
--------------+-------------------
Porta Codice | Cuffie senza fili
(1 row)Gli oggetti di Porta Codice non ancora ritirati. Una sola riga: dei tre oggetti, due sono già stati ritirati.
LEFT JOIN: tieni tutta la tabella di sinistra
E se Nadia vuole tutte le stazioni, anche quelle senza oggetti? Con la JOIN semplice Vecchia Fiera sparisce. Con LEFT JOIN no:
SELECT s.nome, o.descrizione
FROM stazioni s
LEFT JOIN oggetti_smarriti o ON o.stazione_id = s.id
WHERE s.id IN (1, 2, 6, 8)
ORDER BY s.id, o.id; nome | descrizione
----------------+--------------------------
Porta Codice | Ombrello nero
Porta Codice | Telefono con cover rossa
Porta Codice | Cuffie senza fili
Via Variabile | Mazzo di chiavi
Via Variabile | Libro d'avventura
Vecchia Fiera |
Corso Funzione |
(7 rows)La regola: LEFT JOIN tiene tutte le righe della tabella a sinistra (quella scritta prima, nel FROM). Quelle che hanno delle coppie escono con le loro coppie, come prima. Quelle senza coppie escono una volta sola, con NULL al posto delle colonne dell'altra tabella. psql mostra il NULL come una casella vuota, come hai visto nel capitolo Tabelle, tipi e psql.
stazioni oggetti_smarriti
│ 1 │ Porta Codice │ ◀──── tre oggetti → tre righe
│ 2 │ Via Variabile │ ◀──── due oggetti → due righe
│ 6 │ Vecchia Fiera │ ◀──── (niente) → una riga, oggetto NULL
│ 8 │ Corso Funzione │ ◀──── (niente) → una riga, oggetto NULLQuale tabella sta «a sinistra»? Quella del FROM, scritta prima della parola LEFT JOIN. Se la scambi, cambia il risultato.
Chi non ha mai…
Ecco il trucco più utile della LEFT JOIN. Guarda le righe di Vecchia Fiera e Corso Funzione: la colonna dell'altra tabella è NULL. Un oggetto vero non ha mai l'id vuoto: la chiave primaria non è mai NULL. Quindi un id vuoto, dopo una LEFT JOIN, può voler dire una sola cosa: la coppia non c'è.
Aggiungi un WHERE che tiene solo quelle righe e ottieni chi non ha mai qualcosa:
SELECT s.nome
FROM stazioni s
LEFT JOIN oggetti_smarriti o ON o.stazione_id = s.id
WHERE o.id IS NULL
ORDER BY s.id; nome
----------------
Vecchia Fiera
Corso Funzione
(2 rows)Le stazioni dove nessuno ha mai dimenticato niente. Sono tre pezzi, sempre gli stessi:
LEFT JOINdalla tabella di cui cerchi le righe (quella di cui vuoi l'elenco);- nell'
ON, la chiave esterna che la lega all'altra; WHEREcolonna chiave dell'altra tabellaIS NULL.
Due errori classici:
JOINal posto diLEFT JOIN: chi non ha coppie sparisce prima delWHERE, e non resta niente da trovare. Zero righe.= NULLal posto diIS NULL: lo sai dal capitolo Leggere, non è mai vero. Zero righe, anche qui, e senza nessun errore:
SELECT s.nome
FROM stazioni s
LEFT JOIN oggetti_smarriti o ON o.stazione_id = s.id
WHERE o.id = NULL; nome
------
(0 rows)La condizione sull'altra tabella: ON o WHERE?
C'è una cosa delicata. Nadia vuole tutte le stazioni, con gli oggetti trovati dal 10 novembre in poi. Dove va la condizione sulla data? Si può scrivere in due posti, e il risultato cambia.
Nell'ON: la condizione fa parte di «quali coppie contano».
SELECT s.nome, o.descrizione
FROM stazioni s
LEFT JOIN oggetti_smarriti o ON o.stazione_id = s.id AND o.trovato_il >= '2026-11-10'
WHERE s.id IN (1, 2, 6, 8)
ORDER BY s.id, o.id; nome | descrizione
----------------+--------------------------
Porta Codice | Telefono con cover rossa
Porta Codice | Cuffie senza fili
Via Variabile | Mazzo di chiavi
Vecchia Fiera |
Corso Funzione |
(5 rows)Nel WHERE: la condizione filtra le righe dopo la JOIN.
SELECT s.nome, o.descrizione
FROM stazioni s
LEFT JOIN oggetti_smarriti o ON o.stazione_id = s.id
WHERE o.trovato_il >= '2026-11-10' AND s.id IN (1, 2, 6, 8)
ORDER BY s.id, o.id; nome | descrizione
---------------+--------------------------
Porta Codice | Telefono con cover rossa
Porta Codice | Cuffie senza fili
Via Variabile | Mazzo di chiavi
(3 rows)Vecchia Fiera e Corso Funzione sono sparite. Perché? Dopo la LEFT JOIN hanno trovato_il uguale a NULL; il WHERE chiede NULL >= '2026-11-10', che non è vero, e la riga viene buttata. Il WHERE ha disfatto quello che la LEFT JOIN aveva salvato.
La regola:
| Vuoi | Scrivi la condizione |
|---|---|
| Tenere tutte le righe di sinistra, e abbinare solo le righe di destra che rispettano la condizione | nell'ON |
| Togliere dal risultato le righe che non la rispettano | nel WHERE |
Con la JOIN semplice invece è lo stesso: non ci sono righe da salvare, ON e WHERE danno lo stesso risultato. Il problema nasce solo con la LEFT JOIN, e solo per le condizioni sulla tabella di destra. Quelle sulla tabella di sinistra (s.id IN (…)) puoi metterle nel WHERE senza paura.
Più JOIN in fila
Una JOIN può essere seguita da un'altra. Ognuna aggiunge una tabella e il suo ON. Le corse hanno tre chiavi esterne: il treno, e due stazioni (da_stazione_id e a_stazione_id). Per vedere codici e nomi insieme servono tre JOIN:
SELECT t.codice, da.nome AS da, a.nome AS a, c.passeggeri
FROM corse c
JOIN treni t ON t.id = c.treno_id
JOIN stazioni da ON da.id = c.da_stazione_id
JOIN stazioni a ON a.id = c.a_stazione_id
WHERE c.passeggeri > 200
ORDER BY c.passeggeri DESC; codice | da | a | passeggeri
--------+--------------+--------------+------------
M12 | Piazza Query | Porta Codice | 225
M12 | Porta Codice | Ponte Ciclo | 210
M12 | Piazza Query | Porta Codice | 201
(3 rows)Si legge dall'alto: parti dalle corse (c), a ogni corsa attacca il suo treno (t), poi la stazione di partenza, poi la stazione di arrivo.
La stessa tabella due volte vuole due soprannomi diversi: stazioni da e stazioni a. È come avere due copie della tabella delle stazioni sul tavolo: una la consulti per la partenza, l'altra per l'arrivo. Con un soprannome solo, PostgreSQL non saprebbe a quale copia ti riferisci. E ogni copia ha il suo ON: la prima con da_stazione_id, la seconda con a_stazione_id. Se scambi le due colonne, partenza e arrivo escono scambiati, e nessun errore te lo dice.
AS da e AS a danno un nome alle colonne del risultato, che altrimenti si chiamerebbero tutte e due nome.
Attraverso la tabella ponte
Nel capitolo Le relazioni hai visto che un molti-a-molti usa una tabella ponte. Per andare da una persona alla sua patente si passa dal ponte: due JOIN, una per lato.
personale ──▶ personale_patenti ◀── patenti
p.id = pp.personale_id pp.patente_id = pa.idSELECT p.nome, p.cognome, pa.nome AS patente
FROM personale p
JOIN personale_patenti pp ON pp.personale_id = p.id
JOIN patenti pa ON pa.id = pp.patente_id
WHERE pa.nome = 'tram'
ORDER BY p.id; nome | cognome | patente
---------+----------+---------
Ivo | Brambati | tram
Sergio | Colombo | tram
Ernesto | Pozzi | tram
Tommaso | Greco | tram
Paolo | Riva | tram
(5 rows)Chi ha la patente del tram: cinque persone. Il ponte non compare nel SELECT, ma deve stare nel FROM: è il passaggio.
Come si legge una query con JOIN
Come nel capitolo Leggere: si scrive dal SELECT, ma si legge dal FROM. Ora l'ordine ha un pezzo in più:
1. FROM … JOIN … ON … quali tabelle, e quali coppie
2. WHERE filtra le coppie
3. SELECT sceglie le colonne
4. ORDER BY mette in fila
5. LIMIT tagliaDopo il punto 1 la «tabella» è già diventata una sola, larga, con le colonne di tutte quelle messe insieme. Da lì in poi la query lavora come se fosse una tabella sola.
Cosa viene dopo
Ora puoi fare domande che attraversano le tabelle, e sai trovare chi non ha mai fatto qualcosa. Contare e sommare, per esempio «quanti oggetti per stazione», è il lavoro del capitolo Riassumere. Il database con cui lavori, in Python, nell'API: Perché un database.
Ricorda
- La
JOIN … ONfa le coppie di righe che rispettano l'ON. Una riga con tre coppie compare tre volte; una riga senza coppie sparisce. - L'
ONmette a confronto la chiave esterna di una tabella con la chiave primaria dell'altra:o.stazione_id = s.id. - I soprannomi (
stazioni s) accorciano e tolgono l'ambiguità: appena ne dai uno, usa solo quello.column reference … is ambiguousvuol dire: scrivi di quale tabella parli. LEFT JOINtiene tutte le righe della tabella a sinistra; dove manca la coppia, le colonne dell'altra sonoNULL.- «Chi non ha mai…»:
LEFT JOIN+WHERE colonna_chiave_dell_altra IS NULL. ConJOINsemplice o con= NULLnon esce niente. - Con la
LEFT JOIN, una condizione sulla tabella di destra nelWHEREcancella le righe senza coppia: mettila nell'ON. - Più
JOINin fila: ognuna ha il suoON. La stessa tabella due volte vuole due soprannomi. Il molti-a-molti passa dal ponte con dueJOIN.