Vai al contenuto
code-to-code

Apro l'indice…

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

SQL4. JOINp. 78

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:

  1. Prevedi: leggi la domanda e la query, e scrivi su un foglio quante righe escono e quali. Prima di aprire la risposta.
  2. In italiano: cosa chiede la query, a parole.
  3. Passo per passo: cosa resta dopo ogni pezzo, nell'ordine di lettura (FROM, JOIN, WHERE, SELECT, ORDER BY).
  4. 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.

SQL
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_id vuoto (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.

PezzoCosa resta
FROM personale p16 righe, una per persona
JOIN stazioni s ON s.id = p.stazione_id12 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 stazioneLe tre colonne. AS stazione evita due colonne tutte e due chiamate nome
ORDER BY p.idLe 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).

SQL
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 FROM non cambia le coppie con una JOIN semplice: cambia solo da dove si parte a leggere. Cambierà quando useremo LEFT JOIN.

In italiano. «Per ogni stazione, le persone che ci lavorano; tieni solo quelle il cui ruolo comincia per capo.»

Passo per passo.

PezzoCosa resta
FROM stazioni s JOIN personale p ON p.stazione_id = s.id12 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.idLe 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'è.

SQL
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 stazione vuota: è un NULL.

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.

PezzoCosa resta
FROM personale p16 righe: la tabella di sinistra, che deve restare intera
LEFT JOIN stazioni s ON s.id = p.stazione_id16 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.idIn 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:

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

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

PezzoCosa resta
FROM personale p16 persone
LEFT JOIN corse c ON c.macchinista_id = p.idUna 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 NULLResta solo la riga con la corsa vuota: Paolo
SELECT p.nome, p.cognomeLe 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.

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

PezzoCosa resta
FROM personale p16 righe
JOIN personale_patenti pp ON pp.personale_id = p.id9 righe: le coppie persona-riga del ponte. Chi non ha patenti sparisce
JOIN patenti pa ON pa.id = pp.patente_id9 righe, ognuna con il nome della patente aggiunto
SELECT … ORDER BY p.id, pa.idLe 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.