Vai al contenuto
code-to-code

Apro l'indice…

↑ ↓ per scegliere, Invio per aprire, Esc per chiudere

SQL4. JOINp. 69

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.

SQL
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.

Python
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 fili

Una 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:

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:

SQL
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.

SQL
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:

SQL
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 NULL

Quale 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:

SQL
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:

  1. LEFT JOIN dalla tabella di cui cerchi le righe (quella di cui vuoi l'elenco);
  2. nell'ON, la chiave esterna che la lega all'altra;
  3. WHERE colonna chiave dell'altra tabella IS NULL.

Due errori classici:

  • JOIN al posto di LEFT JOIN: chi non ha coppie sparisce prima del WHERE, e non resta niente da trovare. Zero righe.
  • = NULL al posto di IS NULL: lo sai dal capitolo Leggere, non è mai vero. Zero righe, anche qui, e senza nessun errore:
SQL
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».

SQL
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.

SQL
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:

VuoiScrivi la condizione
Tenere tutte le righe di sinistra, e abbinare solo le righe di destra che rispettano la condizionenell'ON
Togliere dal risultato le righe che non la rispettanonel 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:

SQL
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.id
SQL
SELECT 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                taglia

Dopo 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 … ON fa le coppie di righe che rispettano l'ON. Una riga con tre coppie compare tre volte; una riga senza coppie sparisce.
  • L'ON mette 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 ambiguous vuol dire: scrivi di quale tabella parli.
  • LEFT JOIN tiene tutte le righe della tabella a sinistra; dove manca la coppia, le colonne dell'altra sono NULL.
  • «Chi non ha mai…»: LEFT JOIN + WHERE colonna_chiave_dell_altra IS NULL. Con JOIN semplice o con = NULL non esce niente.
  • Con la LEFT JOIN, una condizione sulla tabella di destra nel WHERE cancella le righe senza coppia: mettila nell'ON.
  • Più JOIN in fila: ognuna ha il suo ON. La stessa tabella due volte vuole due soprannomi. Il molti-a-molti passa dal ponte con due JOIN.