Query avanzate: JOIN, raggruppamenti e sottoquery

Nella lezione precedente la query sui prestiti aperti restituiva solo numeri: libro_id = 9, studente_id = 5. Le informazioni sono divise in più tabelle, ed è giusto così (lezione 3). Ora impariamo a rimetterle insieme e a fare statistiche: sono le query che compaiono in ogni traccia dell'Esame di Stato.

INNER JOIN: unire due tabelle

SELECT libri.titolo, autori.cognome
FROM libri
JOIN autori ON libri.autore_id = autori.id;

• JOIN (che è lo stesso di INNER JOIN) affianca a ogni libro la riga dell'autore corrispondente.
• La condizione dopo ON dice come si collegano: la chiave esterna del libro è uguale alla chiave primaria dell'autore. È quasi sempre questa la forma: FK = PK.
• Le colonne si scrivono tabella.colonna, perché due tabelle possono avere colonne con lo stesso nome (qui tutte e due hanno id).

Per scrivere meno si usano gli alias di tabella, una lettera dopo il nome:

SELECT l.titolo, a.cognome AS autore
FROM libri l
JOIN autori a ON l.autore_id = a.id
WHERE a.nazionalita = 'italiana'
ORDER BY a.cognome, l.titolo;

+----------------------+----------+
| titolo               | autore   |
+----------------------+----------+
| Il barone rampante   | Calvino  |
| Le città invisibili  | Calvino  |
| Lessico famigliare   | Ginzburg |
| Il sistema periodico | Levi     |
| La chiave a stella   | Levi     |
| Se questo è un uomo  | Levi     |
| L'isola di Arturo    | Morante  |
+----------------------+----------+

Dopo la JOIN si possono usare in WHERE e ORDER BY le colonne di entrambe le tabelle: qui filtriamo sulla nazionalità, che sta negli autori.

Come lavora una JOIN. Immagina che MySQL prenda ogni riga di libri e la affianchi a ogni riga di autori: 14 × 8 = 112 combinazioni. Poi tiene solo quelle in cui l.autore_id = a.id, cioè le 14 giuste. Se dimentichi l'ON, ottieni tutte le 112 combinazioni, quasi tutte senza senso (il prodotto cartesiano): se una JOIN restituisce troppe righe, controlla l'ON.

JOIN tra tre tabelle

La tabella prestiti è collegata sia ai libri sia agli studenti: per vederla "in chiaro" servono due JOIN.

SELECT s.nome, s.cognome, l.titolo, p.data_prestito
FROM prestiti p
JOIN studenti s ON p.studente_id = s.id
JOIN libri l    ON p.libro_id = l.id
WHERE p.data_restituzione IS NULL
ORDER BY p.data_prestito;

+--------+---------+----------------------+---------------+
| nome   | cognome | titolo               | data_prestito |
+--------+---------+----------------------+---------------+
| Aisha  | Benali  | Frankenstein         | 2026-09-02    |
| Marco  | Bianchi | Io, robot            | 2026-09-20    |
| Luca   | Romano  | Se questo è un uomo  | 2026-09-25    |
| Chiara | Greco   | 1984                 | 2026-09-28    |
| Giulia | Rossi   | 1984                 | 2026-10-01    |
| Matteo | Ricci   | Se questo è un uomo  | 2026-10-02    |
+--------+---------+----------------------+---------------+

Ora sì che si capisce chi ha cosa. Il metodo per le JOIN lunghe: parti dalla tabella "centrale" (qui prestiti) e aggiungi una JOIN per ogni chiave esterna che vuoi "aprire". Si può continuare: con una terza JOIN su autori (partendo da l.autore_id) avresti anche l'autore di ogni libro prestato.

LEFT JOIN: tenere anche chi non ha corrispondenze

La JOIN normale tiene solo le righe che hanno un "compagno" nell'altra tabella. Un libro mai prestato non ha righe in prestiti, quindi in una JOIN con prestiti sparisce.

La LEFT JOIN tiene tutte le righe della tabella a sinistra (quella scritta prima); quando manca il compagno, le colonne dell'altra tabella valgono NULL.

SELECT l.titolo, p.id AS prestito
FROM libri l
LEFT JOIN prestiti p ON p.libro_id = l.id
WHERE l.autore_id IN (1, 3)      -- solo i libri di Levi e Morante, per avere poche righe
ORDER BY l.titolo;

+----------------------+----------+
| titolo               | prestito |
+----------------------+----------+
| Il sistema periodico |     NULL |
| L'isola di Arturo    |     NULL |
| La chiave a stella   |     NULL |
| Se questo è un uomo  |        1 |
| Se questo è un uomo  |        5 |
| Se questo è un uomo  |       10 |
+----------------------+----------+

Se questo è un uomo compare tre volte (una per prestito), gli altri tre libri una volta, con NULL. Con una JOIN normale vedresti solo le tre righe di Se questo è un uomo.

Da qui viene un trucco classico: trovare ciò che non ha corrispondenze.

-- i libri che non sono mai stati prestati
SELECT l.titolo
FROM libri l
LEFT JOIN prestiti p ON p.libro_id = l.id
WHERE p.id IS NULL
ORDER BY l.titolo;

Il risultato sono 7 libri, da Il giro del mondo in 80 giorni a Lessico famigliare.

Funzioni di aggregazione

Le funzioni di aggregazione riassumono molte righe in un solo valore:

SELECT COUNT(*) AS libri,
       SUM(copie) AS copie,
       MIN(anno) AS piu_vecchio,
       MAX(anno) AS piu_recente,
       ROUND(AVG(copie), 2) AS media_copie
FROM libri;

+-------+-------+-------------+-------------+-------------+
| libri | copie | piu_vecchio | piu_recente | media_copie |
+-------+-------+-------------+-------------+-------------+
|    14 |    35 |        1818 |        1978 |        2.50 |
+-------+-------+-------------+-------------+-------------+

La biblioteca ha 14 titoli e 35 copie in tutto. COUNT(*) conta i titoli (le righe), SUM(copie) i volumi sugli scaffali.

GROUP BY: un risultato per gruppo

Spesso non vogliamo un totale unico ma un totale per categoria: quanti libri per genere, quanti prestiti per classe. GROUP BY divide le righe in gruppi con lo stesso valore e calcola le funzioni di aggregazione dentro ogni gruppo.

SELECT genere, COUNT(*) AS titoli, SUM(copie) AS copie
FROM libri
GROUP BY genere
ORDER BY titoli DESC, genere;

+----------------+--------+-------+
| genere         | titoli | copie |
+----------------+--------+-------+
| Romanzo        |      6 |    12 |
| Fantascienza   |      4 |    12 |
| Avventura      |      2 |     5 |
| Memorialistica |      1 |     4 |
| Racconti       |      1 |     2 |
+----------------+--------+-------+

La regola d'oro del GROUP BY. Nella SELECT possono comparire solo le colonne scritte nel GROUP BY e le funzioni di aggregazione. SELECT genere, titolo, COUNT(*) … GROUP BY genere è un errore: quale dei 6 titoli dei romanzi dovrebbe mostrare? MySQL in alcune configurazioni lo accetta e ne sceglie uno a caso: un motivo in più per rispettare la regola.

GROUP BY con le JOIN

Le statistiche più interessanti uniscono tabelle e poi raggruppano. Quanti prestiti ha fatto ogni classe?

SELECT s.classe, COUNT(*) AS prestiti
FROM prestiti p
JOIN studenti s ON p.studente_id = s.id
GROUP BY s.classe
ORDER BY s.classe;

+--------+----------+
| classe | prestiti |
+--------+----------+
| 3B     |        3 |
| 4A     |        2 |
| 5A     |        4 |
| 5B     |        1 |
+--------+----------+

Per contare includendo gli zeri serve la LEFT JOIN e COUNT su una colonna della tabella di destra. Quante volte sono stati prestati i libri di ogni autore?

SELECT a.cognome, COUNT(p.id) AS prestiti
FROM autori a
JOIN libri l ON l.autore_id = a.id
LEFT JOIN prestiti p ON p.libro_id = l.id
GROUP BY a.id, a.cognome
ORDER BY prestiti DESC, a.cognome;

Ginzburg e Morante compaiono con 0. Con COUNT(*) avrebbero 1, perché la riga con i NULL esiste comunque: COUNT(p.id) invece non conta i NULL. Il GROUP BY a.id raggruppa per autore anche se due autori avessero lo stesso cognome.

HAVING: filtrare i gruppi

WHERE filtra le righe prima di raggrupparle; HAVING filtra i gruppi dopo, e può usare le funzioni di aggregazione.

-- gli autori con almeno 2 libri in catalogo
SELECT a.nome, a.cognome, COUNT(*) AS libri
FROM autori a
JOIN libri l ON l.autore_id = a.id
GROUP BY a.id, a.nome, a.cognome
HAVING COUNT(*) >= 2
ORDER BY libri DESC, a.cognome;

+--------+---------+-------+
| nome   | cognome | libri |
+--------+---------+-------+
| Primo  | Levi    |     3 |
| Isaac  | Asimov  |     2 |
| Italo  | Calvino |     2 |
| George | Orwell  |     2 |
| Jules  | Verne   |     2 |
+--------+---------+-------+

WHERE COUNT(*) >= 2 darebbe errore: quando si valuta il WHERE, i gruppi non esistono ancora. L'ordine in cui MySQL esegue una query aiuta a ricordarlo:

Sottoquery

Una sottoquery è una SELECT dentro un'altra, tra parentesi. Si usa quando per rispondere serve prima calcolare qualcosa.

-- i libri con più copie della media (2,5)
SELECT titolo, copie
FROM libri
WHERE copie > (SELECT AVG(copie) FROM libri)
ORDER BY copie DESC;

La sottoquery restituisce un solo valore (2.50) e la query esterna lo usa come se fosse scritto lì. Risultato: 6 libri, da 1984 (5 copie) ai quattro con 3 copie.

Con IN la sottoquery restituisce un elenco:

-- gli studenti che hanno almeno un libro da restituire
SELECT nome, cognome, classe
FROM studenti
WHERE id IN (SELECT studente_id FROM prestiti WHERE data_restituzione IS NULL)
ORDER BY cognome;

Con NOT IN si trova chi non compare nell'elenco: gli studenti che non hanno mai preso un libro (nel nostro database nessuno, prova ad aggiungerne uno). Molte sottoquery si possono riscrivere con una JOIN e viceversa: scegli la forma che ti sembra più chiara.

⚠️ NOT IN e NULL. Se la sottoquery può restituire anche un NULL, NOT IN non trova nulla. Con le chiavi esterne NOT NULL, come le nostre, il problema non si pone; altrimenti aggiungi WHERE colonna IS NOT NULL dentro la sottoquery, o usa la LEFT JOIN … IS NULL.

La query della disponibilità

Mettiamo insieme tutto: per ogni libro, quante copie sono fuori in prestito e quante sono disponibili. È la query che userà il catalogo della nostra applicazione PHP.

SELECT l.titolo,
       l.copie,
       COUNT(p.id) AS fuori,
       l.copie - COUNT(p.id) AS disponibili
FROM libri l
LEFT JOIN prestiti p
       ON p.libro_id = l.id AND p.data_restituzione IS NULL
GROUP BY l.id, l.titolo, l.copie
ORDER BY l.titolo;

Guarda bene la condizione AND p.data_restituzione IS NULL: è nell'ON, non nel WHERE. Così la LEFT JOIN aggancia solo i prestiti aperti, ma tiene comunque tutti i libri. Se la spostassi nel WHERE, sparirebbero i libri che non hanno prestiti aperti, cioè proprio quelli tutti disponibili.

+--------------------------------+-------+-------+-------------+
| titolo                         | copie | fuori | disponibili |
+--------------------------------+-------+-------+-------------+
| 1984                           |     5 |     2 |           3 |
| Fondazione                     |     2 |     0 |           2 |
| Frankenstein                   |     2 |     1 |           1 |
| ...                            |       |       |             |
| Se questo è un uomo            |     4 |     2 |           2 |
| Ventimila leghe sotto i mari   |     2 |     0 |           2 |
+--------------------------------+-------+-------+-------------+

Esercizi

-- 1. 1984, La fattoria degli animali (Orwell), Frankenstein (Shelley)
SELECT l.titolo, l.anno, a.nome, a.cognome
FROM libri l
JOIN autori a ON l.autore_id = a.id
WHERE a.nazionalita = 'britannica';

-- 2. Se questo è un uomo (restituito il 29/09) e 1984 (ancora fuori)
SELECT l.titolo, p.data_prestito, p.data_restituzione
FROM prestiti p
JOIN libri l    ON p.libro_id = l.id
JOIN studenti s ON p.studente_id = s.id
WHERE s.nome = 'Giulia' AND s.cognome = 'Rossi'
ORDER BY p.data_prestito;

-- 3. Frankenstein, di Mary Shelley (1818)
SELECT l.titolo, l.anno, a.nome, a.cognome
FROM libri l
JOIN autori a ON l.autore_id = a.id
WHERE l.anno = (SELECT MIN(anno) FROM libri);

-- 1. italiana 7, britannica 3, statunitense 2, francese 2
SELECT a.nazionalita, COUNT(*) AS libri
FROM libri l
JOIN autori a ON l.autore_id = a.id
GROUP BY a.nazionalita
ORDER BY libri DESC;

-- 2. Se questo è un uomo (3), 1984 (2)
SELECT l.titolo, COUNT(*) AS volte
FROM prestiti p
JOIN libri l ON p.libro_id = l.id
GROUP BY l.id, l.titolo
HAVING COUNT(*) >= 2
ORDER BY volte DESC;

-- 3. Bianchi, Ricci e Rossi con 2; gli altri con 1
SELECT s.nome, s.cognome, COUNT(p.id) AS prestiti
FROM studenti s
LEFT JOIN prestiti p ON p.studente_id = s.id
GROUP BY s.id, s.nome, s.cognome
ORDER BY prestiti DESC, s.cognome;

SELECT s.nome, s.cognome, s.classe, s.email, l.titolo,
       DATEDIFF('2026-10-15', p.data_prestito) AS giorni
FROM prestiti p
JOIN studenti s ON p.studente_id = s.id
JOIN libri l    ON p.libro_id = l.id
WHERE p.data_restituzione IS NULL
  AND DATEDIFF('2026-10-15', p.data_prestito) > 14
ORDER BY giorni DESC;

SELECT l.genere, COUNT(*) AS prestiti
FROM prestiti p
JOIN libri l ON p.libro_id = l.id
GROUP BY l.genere
HAVING COUNT(*) >= 2
ORDER BY prestiti DESC;

Per allenarti su casi nuovi, prova le esercitazioni SQL: otto piccoli database da creare e interrogare, con i risultati attesi.