JOIN in SQL spiegate con esempi: INNER, LEFT, RIGHT e FULL

In un database ben progettato i dati sono divisi in più tabelle: i clienti da una parte, gli ordini dall'altra. Per rispondere a domande come «quali clienti hanno ordinato a marzo?» bisogna ricomporre queste informazioni, e lo strumento per farlo è la JOIN. Capire come lavora, riga per riga, evita gran parte degli errori che si fanno scrivendo query.

Le tabelle di esempio

Useremo due tabelle molto piccole.

clienti

ordini

Notiamo due particolarità volute: Carla non ha ordini, e l'ordine 13 fa riferimento a un cliente 9 che non esiste. In un database con vincoli di chiave esterna l'ordine 13 non potrebbe esistere, ma nei dati importati da fonti esterne capita spesso.

INNER JOIN: solo le corrispondenze

SELECT c.nome, o.id, o.totale
FROM clienti c
INNER JOIN ordini o ON o.id_cliente = c.id;

Il risultato contiene solo le coppie in cui la condizione è vera:

Carla sparisce perché non ha ordini, e l'ordine 13 sparisce perché non ha un cliente. Anna compare due volte, una per ogni ordine. La parola INNER è facoltativa: scrivere solo JOIN produce lo stesso effetto.

LEFT JOIN: tutto a sinistra

SELECT c.nome, o.id, o.totale
FROM clienti c
LEFT JOIN ordini o ON o.id_cliente = c.id;

Tutti i clienti sono presenti. Dove non c'è un ordine corrispondente, le colonne dell'altra tabella valgono NULL. È la JOIN da usare quando la domanda è «tutti i clienti, con i loro eventuali ordini».

Trovare ciò che manca

Una variante molto utile serve a trovare le righe senza corrispondenza:

SELECT c.nome
FROM clienti c
LEFT JOIN ordini o ON o.id_cliente = c.id
WHERE o.id IS NULL;

Restituisce Carla: i clienti che non hanno mai ordinato. Lo stesso schema, invertito, trova gli ordini orfani come il 13, utile per controllare la qualità dei dati.

RIGHT JOIN e FULL OUTER JOIN

La RIGHT JOIN è lo specchio della LEFT: conserva tutte le righe della tabella a destra. Nella pratica si usa poco, perché basta scambiare l'ordine delle tabelle e scrivere una LEFT JOIN, che risulta più facile da leggere.

La FULL OUTER JOIN conserva tutte le righe di entrambe le tabelle:

È utile per confrontare due elenchi e vedere cosa c'è da una parte sola, per esempio durante una riconciliazione tra due sistemi. Non tutti i database la supportano: MySQL, per esempio, non ha la FULL OUTER JOIN e richiede di combinare due query con UNION.

CROSS JOIN: tutte le combinazioni

La CROSS JOIN abbina ogni riga della prima tabella a ogni riga della seconda, senza condizione. Con 3 clienti e 4 ordini produce 12 righe. Raramente serve sulle tabelle di dati, ma è comoda per generare combinazioni, come tutte le taglie per tutti i colori di un prodotto. Il rischio è ottenerla per sbaglio dimenticando la condizione di unione: su tabelle grandi il risultato esplode.

Gli errori più comuni

Filtrare nel posto sbagliato

Questa query sembra chiedere tutti i clienti con gli ordini superiori a 30:

SELECT c.nome, o.totale
FROM clienti c
LEFT JOIN ordini o ON o.id_cliente = c.id
WHERE o.totale > 30;

Ma la condizione nel WHERE scarta le righe in cui totale è NULL, quindi Carla scompare e la LEFT JOIN si comporta come una INNER. Se si vogliono tutti i clienti e solo gli ordini sopra 30, la condizione va nella clausola ON:

LEFT JOIN ordini o ON o.id_cliente = c.id AND o.totale > 30

Righe moltiplicate

Se si uniscono i clienti sia agli ordini sia ai ticket di assistenza, un cliente con 3 ordini e 2 ticket produce 6 righe. Sommare i totali in questa situazione dà un risultato gonfiato. La soluzione è aggregare ciascuna tabella separatamente, in sottoquery, e poi unire i risultati già riassunti.

Condizioni sulle colonne sbagliate

Unire tabelle su colonne che si somigliano ma non corrispondono, come un codice interno con un codice del fornitore, produce risultati plausibili ma errati. Conviene sempre verificare con un conteggio: se la JOIN restituisce più righe di quante ce ne si aspetta, c'è qualcosa da capire.

In sintesi

• INNER JOIN restituisce solo le righe con corrispondenza in entrambe le tabelle.
• LEFT JOIN conserva tutte le righe di sinistra, con NULL dove manca la corrispondenza.
• LEFT JOIN con WHERE ... IS NULL trova ciò che manca.
• Le condizioni sulla tabella di destra vanno nell'ON, non nel WHERE, se si vuole mantenere la LEFT JOIN.
• Più JOIN uno-a-molti nella stessa query moltiplicano le righe e falsano le somme.