Guida SQLite interattiva: LEFT JOIN

Q: Cosa fa LEFT JOIN in SQLite?

LEFT JOIN restituisce tutte le righe della tabella di sinistra, più le righe corrispondenti della tabella di destra quando esistono. Se la tabella di destra non ha corrispondenze, ottieni comunque la riga di sinistra, e le colonne del lato destro tornano come NULL. LEFT OUTER JOIN è la stessa cosa: in SQLite OUTER è facoltativo.

Q: Che differenza c'è tra LEFT JOIN e INNER JOIN in SQLite?

INNER JOIN restituisce solo le righe in cui la condizione di join è soddisfatta in entrambe le tabelle. LEFT JOIN restituisce comunque tutte le righe della tabella di sinistra, riempiendo con NULL le colonne del lato destro senza corrispondenza. Usa LEFT JOIN quando 'nessuna corrispondenza' è già di per sé una risposta significativa, come gli utenti con zero ordini.

Q: Perché la mia LEFT JOIN in SQLite si comporta come una INNER JOIN?

Quasi sempre per colpa di una clausola WHERE che filtra su una colonna del lato destro senza tenere conto dei NULL. Le condizioni sulla tabella di destra vanno nella clausola ON, non in WHERE, oppure devi scrivere WHERE right.col IS NULL per trovare le righe senza corrispondenza. WHERE right.col = 'x' scarta in silenzio tutte le righe senza corrispondenza.

LEFT JOIN tiene tutto ciò che sta a sinistra

INNER JOIN restituisce solo le righe in cui entrambi i lati corrispondono. Spesso è quello che vuoi, ma non sempre. A volte "nessuna corrispondenza" è proprio la risposta che cerchi: utenti che non hanno fatto ordini, prodotti mai venduti, post con zero commenti. Per questi casi ti serve LEFT JOIN.

Una LEFT JOIN restituisce tutte le righe della tabella di sinistra. Se la tabella di destra ha una riga corrispondente, ottieni le colonne corrispondenti. Se no, ottieni comunque la riga di sinistra, e le colonne del lato destro tornano come NULL.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total REAL
);

INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders (user_id, total) VALUES (1, 49.99), (1, 12.50), (2, 99.00);

SELECT u.name, o.total
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;

Cleo non ha ordini, ma compare lo stesso, con NULL nella colonna total. Sostituisci LEFT JOIN con INNER JOIN e Cleo sparisce del tutto.

Il modello mentale

Leggi la query dall'alto in basso e considera la tabella di sinistra come l'ancora. Ogni riga di users comparirà nell'output, qualunque cosa succeda. Poi la LEFT JOIN chiede, per ogni utente: "c'è una riga corrispondente in orders?"

Corrispondenza trovata → aggancia le colonne corrispondenti alla riga dell'utente.
Più corrispondenze → produce una riga di output per ogni corrispondenza (Ada ha due ordini, quindi compare due volte).
Nessuna corrispondenza → produce una riga con NULL in ogni colonna della tabella di destra.

Quest'ultimo caso è l'intera ragion d'essere di LEFT JOIN. Qui NULL non significa "non lo sappiamo": significa "a destra non c'è niente da agganciare".

LEFT OUTER JOIN è la stessa operazione. In SQLite la parola chiave OUTER è facoltativa, e quasi tutti la omettono.

Trovare le righe senza corrispondenza

Il caso d'uso classico di LEFT JOIN: trovare le righe della tabella di sinistra che non hanno corrispondenza a destra. Il trucco è filtrare su una colonna della tabella di destra che nei dati reali è NOT NULL (di solito la sua chiave primaria) e verificare che sia NULL dopo la join:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 49.99), (2, 2, 99.00);

SELECT u.name
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.id IS NULL;

Torna solo Cleo. La join aggancia i dati degli ordini dove esistono; poi WHERE o.id IS NULL tiene solo le righe in cui l'aggancio non è riuscito. A volte si chiama "anti-join".

ON e WHERE: la trappola sottile

È il bug più comune con LEFT JOIN, e vale la pena soffermarsi. Le condizioni vanno nella clausola ON oppure nella clausola WHERE, ma con le outer join si comportano in modo molto diverso.

ON viene applicata mentre la join avviene. Le condizioni lì decidono quali righe del lato destro contano come corrispondenza.
WHERE viene applicata dopo che la join ha prodotto le sue righe. Filtra il risultato combinato.

Guarda cosa succede se metti una condizione sulla tabella di destra in WHERE:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, status TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 'shipped'), (2, 2, 'pending');

-- Sbagliato: trasforma in silenzio la LEFT JOIN in una INNER JOIN
SELECT u.name, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.status = 'shipped';

Cleo non ha ordini, quindi nella sua riga o.status è NULL, e NULL = 'shipped' non è vero: viene scartata. Lo stato di Boris è 'pending', scartato anche lui. La LEFT JOIN si è comportata di nascosto come una INNER JOIN.

La soluzione: sposta la condizione in ON, così filtra le corrispondenze invece delle righe di output:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, status TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 'shipped'), (2, 2, 'pending');

SELECT u.name, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id AND o.status = 'shipped';

Ora compaiono tutti gli utenti. Ada ottiene il suo ordine spedito; Boris ottiene NULL (il suo ordine in attesa non contava come corrispondenza); Cleo ottiene NULL (nessun ordine). È la risposta giusta quando la domanda è "mostrami tutti gli utenti, più i loro ordini spediti se ce ne sono".

Regola pratica: le condizioni sulla tabella di sinistra possono stare in WHERE. Le condizioni sulla tabella di destra vanno quasi sempre in ON, a meno che tu non voglia proprio trovare le righe senza corrispondenza con IS NULL.

Contare con LEFT JOIN

Un lavoro comune: contare le righe collegate per ogni genitore, compresi i genitori con zero. INNER JOIN scarterebbe gli zeri. LEFT JOIN più un COUNT su una colonna del lato destro dà la risposta giusta:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 49.99), (2, 1, 12.50), (3, 2, 99.00);

SELECT u.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.total), 0) AS revenue
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY u.name;

Due cose da notare:

COUNT(o.id) conta le righe del lato destro non nulle. Cleo ottiene 0, non 1, perché COUNT ignora i NULL. Se scrivessi COUNT(*), Cleo otterrebbe 1 (la riga esiste, ha solo dei NULL dentro). Quasi sempre COUNT(right.id) è quello che ti serve.
COALESCE(SUM(o.total), 0) trasforma la somma NULL di Cleo in 0. Senza, mostrerebbe un ricavo NULL, tecnicamente corretto ma brutto da vedere.

Unire più tabelle

Le LEFT JOIN si concatenano. Ogni join prende il risultato parziale e ci unisce un'altra tabella. Una volta che hai reso una colonna annullabile con una LEFT JOIN, continua a usare LEFT JOIN per tutte le tabelle che dipendono da essa, altrimenti la INNER JOIN successiva scarterà in silenzio righe che volevi tenere.

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);
CREATE TABLE shipments (order_id INTEGER PRIMARY KEY, carrier TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (10, 1, 49.99), (11, 2, 99.00);
INSERT INTO shipments VALUES (10, 'UPS');

SELECT u.name, o.total, s.carrier
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
LEFT JOIN shipments AS s ON s.order_id = o.id;

Tornano tre utenti. Ada ha un ordine e una spedizione. Boris ha un ordine ma nessuna spedizione (carrier è NULL). Cleo non ha ordini, quindi sia o.total sia s.carrier sono NULL. La catena di LEFT JOIN conserva ogni utente, indipendentemente da dove si interrompono i dati lungo la catena di relazioni.

Quando LEFT JOIN è la scelta giusta

Usa LEFT JOIN quando la domanda riguarda essenzialmente la tabella di sinistra e la tabella di destra è un'informazione aggiuntiva. Formulazioni come "tutti gli utenti, con i loro ordini se ce ne sono" o "tutti i prodotti e la loro ultima recensione" corrispondono direttamente a LEFT JOIN.

Usa INNER JOIN quando entrambi i lati sono necessari allo stesso modo: "gli ordini con i dati del loro utente" non ha senso per un ordine senza utente, quindi il filtro della inner join è proprio quello che vuoi.

Se ti ritrovi a scrivere LEFT JOIN ... WHERE right.col IS NOT NULL, volevi una INNER JOIN. Se ti ritrovi a scrivere LEFT JOIN ... WHERE right.col IS NULL, volevi un anti-join, e l'hai scritto bene.

Prossimo passo: le self-join

A volte la tabella a cui vuoi unire i dati è la stessa che stai già interrogando: dipendenti e i loro responsabili, categorie e le loro categorie madri, coppie di utenti della stessa città. È una self-join, ed è il tema della prossima pagina.

Domande frequenti

Cosa fa LEFT JOIN in SQLite?

LEFT JOIN restituisce tutte le righe della tabella di sinistra, più le righe corrispondenti della tabella di destra quando esistono. Se la tabella di destra non ha corrispondenze, ottieni comunque la riga di sinistra, e le colonne del lato destro tornano come NULL. LEFT OUTER JOIN è la stessa cosa: in SQLite OUTER è facoltativo.

Che differenza c'è tra LEFT JOIN e INNER JOIN in SQLite?

INNER JOIN restituisce solo le righe in cui la condizione di join è soddisfatta in entrambe le tabelle. LEFT JOIN restituisce comunque tutte le righe della tabella di sinistra, riempiendo con NULL le colonne del lato destro senza corrispondenza. Usa LEFT JOIN quando 'nessuna corrispondenza' è già di per sé una risposta significativa, come gli utenti con zero ordini.

Perché la mia LEFT JOIN in SQLite si comporta come una INNER JOIN?

Quasi sempre per colpa di una clausola WHERE che filtra su una colonna del lato destro senza tenere conto dei NULL. Le condizioni sulla tabella di destra vanno nella clausola ON, non in WHERE, oppure devi scrivere WHERE right.col IS NULL per trovare le righe senza corrispondenza. WHERE right.col = 'x' scarta in silenzio tutte le righe senza corrispondenza.

Concetti correlati