JOIN, esercizi spiegati
Cinque esempi, una domanda alla volta
- 20 minuti di lettura
Sergio Colombo, il macchinista più anziano, insegna la linea a Paolo Riva, appena arrivato. Paolo ha la patente del tram ma non ha ancora guidato una corsa. Ogni domanda che Paolo fa a Sergio diventa una query. Cinque esempi, dal più semplice. Ognuno ha lo stesso ritmo:
- Prevedi: leggi la domanda e la query, e scrivi su un foglio quante righe escono e quali. Prima di aprire la risposta.
- In italiano: cosa chiede la query, a parole.
- Passo per passo: cosa resta dopo ogni pezzo, nell'ordine di lettura (
FROM,JOIN,WHERE,SELECT,ORDER BY). - Cosa portiamo a casa: la cosa da ricordare.
Per provarli: psql -d sql_join, poi incolli la query. Se non hai ancora il database, i comandi per crearlo sono nella pagina Palestra di questo capitolo. La teoria è nella pagina precedente: JOIN.
A · Chi lavora dove
Paolo non sa chi trova in ogni stazione. La tabella personale ha solo stazione_id, un numero. Vuole i nomi.
SELECT p.nome, p.cognome, s.nome AS stazione
FROM personale p
JOIN stazioni s ON s.id = p.stazione_id
ORDER BY p.id;Prima di aprire: nel personale ci sono 16 persone. Quante righe esce? Chi manca?
Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
nome | cognome | stazione
---------+----------+---------------
Lucia | Ferri | Porta Codice
Nadia | Russo | Vecchia Fiera
Sergio | Colombo | Porta Codice
Elena | Marchi | Via Variabile
Marta | Villa | Piazza Query
Tommaso | Greco | Largo Lista
Rita | Galbiati | Ponte Ciclo
Paolo | Riva | Piazza Query
Anna | Colombo | Via Variabile
Giulia | Bassi | Largo Lista
Bruno | Fontana | Porta Codice
Carla | Meroni | Corso Tupla
(12 rows)- Dodici righe, non sedici: mancano Ivo Brambati, Pietro Galli, Dario Sala ed Ernesto Pozzi.
- Sono le quattro persone con
stazione_idvuoto (il deposito, la manutenzione). Senza una stazione non hanno nessuna coppia.
In italiano. «Per ogni persona, trova la stazione il cui id è uguale al suo stazione_id, e dammi nome, cognome e nome della stazione.»
Passo per passo.
| Pezzo | Cosa resta |
|---|---|
FROM personale p | 16 righe, una per persona |
JOIN stazioni s ON s.id = p.stazione_id | 12 righe: le coppie persona-stazione. Le 4 persone con stazione_id vuoto non hanno coppie e spariscono |
SELECT p.nome, p.cognome, s.nome AS stazione | Le tre colonne. AS stazione evita due colonne tutte e due chiamate nome |
ORDER BY p.id | Le righe in ordine di badge |
Cosa portiamo a casa. Una JOIN semplice perde le righe che non hanno una coppia: 16 persone, 12 righe. Va bene se è quello che vuoi; se no, ti serve la LEFT JOIN, nell'esempio C.
B · Le persone che comandano
Paolo vuole sapere chi comanda in ogni stazione: i ruoli che cominciano per «capo» (capotreno, capostazione).
SELECT s.nome AS stazione, p.nome, p.ruolo
FROM stazioni s
JOIN personale p ON p.stazione_id = s.id
WHERE p.ruolo LIKE 'capo%'
ORDER BY s.id, p.id;Qui la tabella di partenza è stazioni: nell'esempio A era personale. Cambia qualcosa, per le righe che escono? Quante saranno?
Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
stazione | nome | ruolo
--------------+--------+--------------
Porta Codice | Lucia | capotreno
Porta Codice | Bruno | capostazione
Largo Lista | Giulia | capotreno
Piazza Query | Marta | capostazione
(4 rows)- Quattro righe, due a Porta Codice.
- Scambiare l'ordine delle due tabelle nel
FROMnon cambia le coppie con unaJOINsemplice: cambia solo da dove si parte a leggere. Cambierà quando useremoLEFT JOIN.
In italiano. «Per ogni stazione, le persone che ci lavorano; tieni solo quelle il cui ruolo comincia per capo.»
Passo per passo.
| Pezzo | Cosa resta |
|---|---|
FROM stazioni s JOIN personale p ON p.stazione_id = s.id | 12 righe: le stesse coppie dell'esempio A, lette dalla parte delle stazioni |
WHERE p.ruolo LIKE 'capo%' | 4 righe: capotreno e capostazione |
SELECT … ORDER BY s.id, p.id | Le tre colonne, in ordine di stazione e poi di badge |
Qui i soprannomi fanno il loro lavoro. Prova a scrivere nome senza s. e p.: sia stazioni sia personale hanno una colonna nome, e PostgreSQL risponde
ERROR: column reference "nome" is ambiguous
LINE 1: SELECT nome, cognome, nome FROM personale JOIN stazioni ON s...
^Cosa portiamo a casa. Il WHERE si scrive come sempre, ma ora può guardare le colonne di tutte e due le tabelle. E ogni colonna che esiste in tutte e due ha bisogno del soprannome davanti.
C · Tutti, anche senza stazione
Sergio chiede a Paolo: «E gli altri quattro? Anche loro lavorano.» Paolo vuole l'elenco completo: il nome della stazione dove c'è, vuoto dove non c'è.
SELECT p.nome, p.cognome, s.nome AS stazione
FROM personale p
LEFT JOIN stazioni s ON s.id = p.stazione_id
ORDER BY p.id;È l'esempio A con una parola in più. Quante righe? Come si riconoscono quelle senza stazione?
Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
nome | cognome | stazione
---------+----------+---------------
Lucia | Ferri | Porta Codice
Ivo | Brambati |
Nadia | Russo | Vecchia Fiera
Sergio | Colombo | Porta Codice
Elena | Marchi | Via Variabile
Pietro | Galli |
Marta | Villa | Piazza Query
Dario | Sala |
Ernesto | Pozzi |
Tommaso | Greco | Largo Lista
Rita | Galbiati | Ponte Ciclo
Paolo | Riva | Piazza Query
Anna | Colombo | Via Variabile
Giulia | Bassi | Largo Lista
Bruno | Fontana | Porta Codice
Carla | Meroni | Corso Tupla
(16 rows)- Sedici righe: tutto il personale.
- Ivo, Pietro, Dario ed Ernesto hanno la casella
stazionevuota: è unNULL.
In italiano. «Per ogni persona, anche se non c'è una stazione abbinata, dammi i suoi dati e il nome della stazione (o niente, se non ce l'ha).»
Passo per passo.
| Pezzo | Cosa resta |
|---|---|
FROM personale p | 16 righe: la tabella di sinistra, che deve restare intera |
LEFT JOIN stazioni s ON s.id = p.stazione_id | 16 righe: 12 con la loro stazione, 4 con tutte le colonne di s a NULL |
SELECT … | I nomi, con la stazione vuota dove manca |
ORDER BY p.id | In ordine di badge |
Cosa portiamo a casa. LEFT JOIN non perde nessuna riga della tabella a sinistra. La tabella a sinistra è quella scritta nel FROM: se scrivessi FROM stazioni s LEFT JOIN personale p … salverebbe le stazioni, non le persone.
D · Chi non ha mai guidato
Sergio fa una prova a Paolo: «Tra i macchinisti, chi non ha mai guidato una corsa?». Paolo lo sa: lui. Ma deve farlo dire al database.
Prima la domanda piccola. Guarda tutte le corse di Tommaso Greco (id 10) e di Paolo (id 12), con LEFT JOIN:
SELECT p.nome, p.cognome, c.id AS corsa
FROM personale p
LEFT JOIN corse c ON c.macchinista_id = p.id
WHERE p.id IN (10, 12)
ORDER BY p.id, c.id;Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
nome | cognome | corsa
---------+---------+-------
Tommaso | Greco | 4
Tommaso | Greco | 5
Tommaso | Greco | 12
Tommaso | Greco | 13
Tommaso | Greco | 15
Tommaso | Greco | 16
Paolo | Riva |
(7 rows)- Tommaso ha sei corse, e compare sei volte.
- Paolo ha una riga sola, con la colonna
corsavuota: nessuna corsa lo ha come macchinista.
Quella casella vuota è la chiave. Un id di una corsa vera non è mai NULL: se lo è, la persona non ha nessuna coppia. Tieni solo quelle righe, e solo i macchinisti:
SELECT p.nome, p.cognome
FROM personale p
LEFT JOIN corse c ON c.macchinista_id = p.id
WHERE p.ruolo = 'macchinista' AND c.id IS NULL; nome | cognome
-------+---------
Paolo | Riva
(1 row)In italiano. «Prendi ogni macchinista con le sue corse; tieni quelli per cui la corsa manca.»
Passo per passo.
| Pezzo | Cosa resta |
|---|---|
FROM personale p | 16 persone |
LEFT JOIN corse c ON c.macchinista_id = p.id | Una riga per ogni corsa guidata, e una riga con c.id a NULL per chi non ne ha mai guidate. Chi ha 7 corse compare 7 volte |
WHERE p.ruolo = 'macchinista' | I 4 macchinisti: Sergio (7 corse), Tommaso (6), Rita (6), Paolo (nessuna): 20 righe |
AND c.id IS NULL | Resta solo la riga con la corsa vuota: Paolo |
SELECT p.nome, p.cognome | Le due colonne |
Cosa portiamo a casa. Il disegno è sempre questo: LEFT JOIN + WHERE chiave_dell_altra IS NULL. Il test sul ruolo sta nel WHERE perché riguarda la tabella di sinistra (personale): lì non fa danni.
E · Chi ha quale patente
Paolo vuole il registro delle patenti: nome, cognome e patente di ognuno. La tabella personale non ha le patenti: stanno in patenti, e il legame sta in una terza tabella, il ponte personale_patenti.
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
ORDER BY p.id, pa.id;Prima di aprire: le righe del ponte sono nove. Quante righe esce? Sergio ha due patenti: quante volte compare?
Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
nome | cognome | patente
---------+----------+---------
Ivo | Brambati | tram
Sergio | Colombo | metro
Sergio | Colombo | tram
Ernesto | Pozzi | metro
Ernesto | Pozzi | tram
Tommaso | Greco | metro
Tommaso | Greco | tram
Rita | Galbiati | metro
Paolo | Riva | tram
(9 rows)- Nove righe: una per ogni riga del ponte.
- Sergio compare due volte, una per patente. La funicolare non c'è, perché non ha nessuno.
In italiano. «Per ogni persona prendi le sue righe nel ponte; per ogni riga del ponte prendi la patente a cui punta.»
Passo per passo.
| Pezzo | Cosa resta |
|---|---|
FROM personale p | 16 righe |
JOIN personale_patenti pp ON pp.personale_id = p.id | 9 righe: le coppie persona-riga del ponte. Chi non ha patenti sparisce |
JOIN patenti pa ON pa.id = pp.patente_id | 9 righe, ognuna con il nome della patente aggiunto |
SELECT … ORDER BY p.id, pa.id | Le colonne scelte, in ordine di badge e poi di patente |
Sono due ON, uno per freccia: dalla persona al ponte, dal ponte alla patente. Se scrivessi pa.id = pp.personale_id (la persona al posto della patente) il database non ti darebbe nessun errore: abbinerebbe le patenti ai numeri dei badge, e le righe uscirebbero sbagliate o mancherebbero, senza una parola.
Cosa portiamo a casa. Il molti-a-molti si attraversa con due JOIN. Per ogni JOIN scrivi una riga di ON e controlla che colleghi una chiave esterna alla sua chiave primaria. Se invece vuoi tenere anche le patenti che nessuno ha, servono due LEFT JOIN partendo dalle patenti: lo fai nella palestra.