SELECT multiple: JOIN, subquery e operatori insiemistici

Perché combinare più tabelle?

Il modello relazionale distribuisce i dati in più tabelle collegate tramite chiavi esterne. Per rispondere a domande reali — quale studente ha sostenuto quale esame in quale corso? — dobbiamo ricombinare quelle tabelle. SQL offre tre meccanismi principali: i JOIN, le subquery e gli operatori insiemistici.

JOIN: unire tabelle per righe corrispondenti

Un JOIN combina le colonne di due tabelle su una condizione di giunzione, tipicamente l’uguaglianza tra una FK e la PK della tabella referenziata.

INNER JOIN

Il più usato. Restituisce solo le righe che hanno una corrispondenza in entrambe le tabelle:

-- nome studente + nome corso + voto (solo esami esistenti)
SELECT
  s.nome,
  s.cognome,
  c.nome   AS corso,
  e.voto
FROM esame e
  INNER JOIN studente s ON e.studente_id = s.id
  INNER JOIN corso    c ON e.corso_id    = c.id
ORDER BY s.cognome, c.nome;

LEFT JOIN

Restituisce tutte le righe della tabella sinistra, anche quelle senza corrispondenza a destra (i campi della tabella destra diventano NULL):

-- tutti gli studenti, con il numero di esami sostenuti (anche 0)
SELECT
  s.nome,
  s.cognome,
  COUNT(e.id) AS num_esami
FROM studente s
  LEFT JOIN esame e ON e.studente_id = s.id
GROUP BY s.id, s.nome, s.cognome
ORDER BY num_esami DESC;

-- studenti che NON hanno mai sostenuto esami
SELECT s.nome, s.cognome
FROM studente s
  LEFT JOIN esame e ON e.studente_id = s.id
WHERE e.id IS NULL;

RIGHT JOIN

Speculare al LEFT JOIN: restituisce tutte le righe della tabella destra. In pratica si usa raramente — è sempre possibile riscriverlo come LEFT JOIN invertendo l’ordine delle tabelle:

-- equivalenti:
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;
SELECT * FROM b LEFT JOIN  a ON a.id = b.a_id;

FULL OUTER JOIN

Unione di LEFT + RIGHT: tutte le righe di entrambe le tabelle, con NULL dove non c’è corrispondenza. MySQL non lo supporta nativamentene — si simula con UNION:

-- simulazione FULL OUTER JOIN in MySQL
SELECT s.nome, e.voto FROM studente s LEFT  JOIN esame e ON s.id = e.studente_id
UNION
SELECT s.nome, e.voto FROM studente s RIGHT JOIN esame e ON s.id = e.studente_id;

SELF JOIN

Una tabella che si unisce a se stessa. Richiede alias obbligatori per distinguere i due “ruoli”:

-- es. tabella DIPENDENTE(id, nome, manager_id → FK su id)
-- trova ogni dipendente con il nome del suo manager
SELECT
  d.nome   AS dipendente,
  m.nome   AS manager
FROM dipendente d
  LEFT JOIN dipendente m ON d.manager_id = m.id
ORDER BY m.nome, d.nome;

Subquery: query annidate

Una subquery (o query annidata) è una SELECT dentro un’altra SELECT. Può comparire in vari punti: nella WHERE, nella SELECT, nella FROM (come tabella derivata).

Subquery scalare

Restituisce un unico valore e può essere usata ovunque ci si aspetti un valore:

-- studenti con voto superiore alla media globale
SELECT studente_id, voto
FROM esame
WHERE voto > (SELECT AVG(voto) FROM esame)
ORDER BY voto DESC;

-- differenza dal voto massimo
SELECT
  studente_id,
  corso_id,
  voto,
  (SELECT MAX(voto) FROM esame) - voto AS distanza_dal_max
FROM esame;

Subquery con IN

Verifica se il valore appartiene a un insieme restituito dalla subquery:

-- studenti che hanno sostenuto almeno un esame nel corso "Matematica"
SELECT nome, cognome
FROM studente
WHERE id IN (
  SELECT DISTINCT studente_id
  FROM esame
  WHERE corso_id = (SELECT id FROM corso WHERE nome = 'Matematica')
);

-- corsi in cui nessuno ha preso 30 o lode
SELECT nome FROM corso
WHERE id NOT IN (
  SELECT DISTINCT corso_id FROM esame WHERE voto >= 30
);

Subquery con EXISTS

EXISTS verifica solo se la subquery restituisce almeno una riga. Non ha bisogno di recuperare i dati, solo di confermare l’esistenza — spesso più efficiente di IN su dataset grandi:

-- studenti che hanno sostenuto almeno un esame
SELECT nome, cognome
FROM studente s
WHERE EXISTS (
  SELECT 1 FROM esame e WHERE e.studente_id = s.id
);

-- corsi senza nessun esame sostenuto
SELECT nome FROM corso c
WHERE NOT EXISTS (
  SELECT 1 FROM esame e WHERE e.corso_id = c.id
);

Subquery correlata

Una subquery correlata fa riferimento a colonne della query esterna. Viene rieseguita per ogni riga della query esterna — potente ma costosa:

-- per ogni studente, il suo voto massimo
SELECT
  s.nome,
  s.cognome,
  (SELECT MAX(e.voto) FROM esame e WHERE e.studente_id = s.id) AS voto_max
FROM studente s;

UNION e operatori insiemistici

Gli operatori insiemistici combinano i result set di due SELECT distinte. Le due SELECT devono avere lo stesso numero di colonne e tipi di dato compatibili.

UNION e UNION ALL

-- tutti gli ID degli studenti che hanno sostenuto esami nel 2023 O nel 2024
SELECT studente_id FROM esame WHERE YEAR(data_esame) = 2023
UNION
SELECT studente_id FROM esame WHERE YEAR(data_esame) = 2024;
-- UNION elimina i duplicati (lento: fa un sort+dedup)

-- con UNION ALL si mantengono i duplicati (più veloce)
SELECT studente_id FROM esame WHERE YEAR(data_esame) = 2023
UNION ALL
SELECT studente_id FROM esame WHERE YEAR(data_esame) = 2024;

INTERSECT e EXCEPT (supporto variabile)

-- INTERSECT: studenti iscritti a entrambi i corsi 1 e 2 (PostgreSQL / SQL Server)
SELECT studente_id FROM esame WHERE corso_id = 1
INTERSECT
SELECT studente_id FROM esame WHERE corso_id = 2;

-- EXCEPT: studenti che hanno fatto il corso 1 ma NON il corso 2
SELECT studente_id FROM esame WHERE corso_id = 1
EXCEPT
SELECT studente_id FROM esame WHERE corso_id = 2;

JOIN vs subquery: quando scegliere?

Esempio completo: query multitabella reale

-- Report: nome studente, corso, voto, e se il voto è sopra o sotto la media del corso
SELECT
  s.cognome,
  s.nome,
  c.nome           AS corso,
  e.voto,
  ROUND(medie.media, 1)  AS media_corso,
  CASE
    WHEN e.voto >= medie.media THEN 'sopra media'
    ELSE 'sotto media'
  END              AS confronto
FROM esame e
  INNER JOIN studente s ON e.studente_id = s.id
  INNER JOIN corso    c ON e.corso_id    = c.id
  INNER JOIN (
    SELECT corso_id, AVG(voto) AS media
    FROM esame
    GROUP BY corso_id
  ) AS medie ON medie.corso_id = e.corso_id
ORDER BY c.nome, e.voto DESC;

Nota: la subquery nella clausola FROM (detta tabella derivata) calcola le medie per corso una sola volta ed è poi trattata come una tabella normale nel JOIN.