Tenere tutto a sinistra: LEFT JOIN

L'INNER JOIN della lezione precedente ha un effetto collaterale importante: scarta le righe senza corrispondenza. Se un dipendente non ha un dipartimento (department_id è NULL), sparisce dal risultato.

A volte questo è quello che vuoi. Spesso però no: in un report HR è fondamentale vedere tutti i dipendenti, anche quelli senza dipartimento, e sapere quali sono. Per questo esiste LEFT JOIN (per esteso LEFT OUTER JOIN).

Come funziona

SELECT colonne
FROM   tabella_a
LEFT JOIN tabella_b ON tabella_a.x = tabella_b.y;

Letta in italiano: "tieni tutte le righe di tabella_a; per ognuna prova ad accostarle una riga di tabella_b; se non c'è corrispondenza, riempi le colonne di tabella_b con NULL".

La parola chiave è LEFT: significa "tabella di sinistra", cioè quella scritta dopo FROM. Tutte le sue righe sopravvivono, sempre.

-- Tutti i dipendenti, anche quelli senza dipartimento:
SELECT e.first_name,
       e.last_name,
       d.name AS dipartimento
FROM   employees   AS e
LEFT JOIN departments AS d ON e.department_id = d.id;

Nel nostro dataset questa query restituisce 10 righe (un dipendente ciascuno). Andrea Costa, che ha department_id = NULL, compare con dipartimento = NULL invece di sparire.

Trovare i "senza corrispondenza"

Una conseguenza utilissima: filtrando per IS NULL sulla colonna della tabella di destra trovi proprio le righe senza match:

-- Dipendenti orfani: nessun dipartimento assegnato
SELECT e.first_name, e.last_name
FROM   employees   AS e
LEFT JOIN departments AS d ON e.department_id = d.id
WHERE  d.id IS NULL;

Questo trucco si usa spessissimo nel mondo reale: "trovami gli ordini senza cliente", "i prodotti senza recensioni", "gli utenti che non hanno mai fatto login".

Prova tu

Esercizio di ripasso