Tipi di JOIN in SQL Spiegati con Esempi

Una JOIN scritta male non genera un errore. Viene eseguita, restituisce un risultato, e le righe sono sbagliate: clienti che mancano, totali gonfiati, record orfani invisibili. Nella maggior parte dei casi la query ha chiesto esattamente quello che ha ottenuto, e chi l’ha scritta ha scelto il tipo di join sbagliato, oppure ha filtrato nel punto sbagliato.

Una join combina le righe di due tabelle in base a una condizione che scrivi tu. Quali righe sopravvivono dipende dal tipo di join, e i sei tipi descritti sotto danno sei risposte diverse partendo dalle stesse due tabelle.

customers contiene cinque persone. Tre sono state segnalate da un altro cliente, registrato in referred_by:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        TEXT NOT NULL,
  referred_by INT REFERENCES customers(customer_id)
);

INSERT INTO customers (customer_id, name, referred_by) VALUES
  (1, 'Alice', NULL),
  (2, 'Bob',   NULL),
  (3, 'Carol', 1),
  (4, 'Dave',  1),
  (5, 'Erin',  2);

orders contiene sei ordini. Cinque puntano a un cliente reale. Il sesto, 106, punta al cliente 99, che è stato eliminato dopo aver effettuato l’ordine, quindi l’ordine non corrisponde più a nessuno:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL,
  amount      NUMERIC(10,2) NOT NULL
);

INSERT INTO orders (order_id, customer_id, amount) VALUES
  (101, 1, 50.00),
  (102, 1, 20.00),
  (103, 2, 75.00),
  (104, 4, 30.00),
  (105, 4, 15.00),
  (106, 99, 10.00);

Il modo più rapido per seguire gli esempi è un container Postgres, configurato come in Cos’è Docker Compose e Come si Usa. Le join sono SQL standard. I due punti in cui MySQL si comporta diversamente sono segnalati più sotto.

INNER JOIN: solo le righe che corrispondono su entrambi i lati

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name, o.order_id;

Cinque righe. Carol ed Erin non hanno ordini, quindi non compaiono. L’ordine 106 non ha un cliente, quindi non compare nemmeno lui. JOIN e INNER JOIN significano la stessa cosa. INNER è opzionale e la maggior parte delle persone lo omette.

Con cinque righe non noti nessuna lentezza. Su una tabella orders reale, orders.customer_id ha bisogno di un indice, altrimenti ogni join contro quella tabella la scansiona per intero. Come Funzionano gli Indici Database e Quando Usarli spiega quando quell’indice ripaga.

LEFT JOIN: ogni riga a sinistra, corrispondenza trovata o no

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name, o.order_id;

Ogni cliente è presente. Carol ed Erin ottengono NULL nelle colonne dell’ordine. Questa è la join che scriverai più spesso: “tutto X, con qualsiasi Y abbia”.

RIGHT JOIN: ogni riga a destra, corrispondenza trovata o no

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

L’ordine 106 è di nuovo presente, con NULL al posto del nome. Questa è la riga orfana che l’inner join teneva nascosta, e una right join (o l’equivalente left join) è il modo per trovarla intenzionalmente.

Raramente vedrai una RIGHT JOIN in codice reale. a RIGHT JOIN b restituisce le stesse righe di b LEFT JOIN a, quindi la maggior parte dei team usa LEFT JOIN ovunque e cambia l’ordine delle tabelle. Riscrivere una RIGHT JOIN in questo modo è una questione di coerenza. L’originale non è sbagliato.

FULL OUTER JOIN: tutto da entrambi i lati

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name NULLS LAST, o.order_id;

Otto righe: ogni corrispondenza, più i clienti senza corrispondenza, più l’ordine senza corrispondenza. Questo la rende la join giusta per la riconciliazione, quando vuoi tutto ciò che, su un lato o sull’altro, non si allinea. Fuori da quel caso, non ti servirà molto.

MySQL non ha FULL OUTER JOIN, e non accetta nemmeno NULLS LAST. Per ottenere le stesse righe lì, parti dalla LEFT JOIN, poi fai una UNION ALL con una RIGHT JOIN filtrata con WHERE c.customer_id IS NULL, così le righe già corrisposte non vengono ripetute. Funziona anche una UNION semplice delle due join, ma paga il costo di una deduplica e unisce anche righe che sono davvero identiche.

CROSS JOIN: ogni riga accoppiata con ogni riga

1
2
3

SELECT c.name, o.order_id
FROM customers c
CROSS JOIN orders o;

Cinque clienti per sei ordini: trenta righe, senza nessuna condizione di join. La maggior parte delle cross join in produzione sono incidenti. Qualcuno ha scritto FROM customers, orders e si è scordato il WHERE che doveva collegarle. Ne vorresti una intenzionalmente per generare combinazioni, come ogni taglia con ogni colore, o ogni giorno di un intervallo di date con ogni negozio, prima di riempire i numeri reali.

Come funziona una self-join?

customers.referred_by punta a un’altra riga della stessa tabella. Per mostrare chi ha segnalato chi per nome, unisci la tabella a se stessa con un secondo alias:

1
2
3
4

SELECT c.name AS customer, r.name AS referred_by
FROM customers c
LEFT JOIN customers r ON c.referred_by = r.customer_id
ORDER BY c.name;

La join funziona esattamente come prima. customers c e customers r sono due alias, e il planner li tratta come due tabelle separate che per caso contengono le stesse righe. È una LEFT JOIN apposta, così Alice e Bob, che non sono stati segnalati da nessuno, restano nel risultato. Qualsiasi tabella in cui una riga punta a un’altra riga della stessa tabella funziona così: dipendenti e manager, categorie e categorie padre, commenti e risposte.

Perché una clausola WHERE trasforma una LEFT JOIN in una INNER JOIN?

Vuoi ogni cliente, con i suoi ordini sopra i 25 $ dove ne ha almeno uno:

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 25;

Carol ed Erin sono scomparse. WHERE viene eseguito dopo la join, e per loro o.amount è NULL. NULL > 25 non è né vero né falso. È indefinito, e WHERE tiene solo le righe che sono vere. La LEFT JOIN le aveva mantenute, e il filtro le ha scartate.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- Mantieni ogni cliente; filtra solo quali dei suoi ordini contano
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.amount > 25;

-- Mantiene i clienti senza nessun ordine, ma non i clienti i cui ordini sono tutti <= 25
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 25 OR o.amount IS NULL;

Usa la prima. Metterla nell’ON decide quali ordini possono corrispondere, e ogni cliente ottiene comunque almeno una riga. La seconda versione lascia passare solo le righe in cui la join non ha trovato nulla. Un cliente il cui unico ordine è di 20 $ ottiene una riga con amount = 20.00, che non supera nessuna delle due condizioni, e quel cliente scompare di nuovo. Su questi dati entrambe le query mantengono per caso tutti e cinque i clienti, e questo è come la seconda versione passa inosservata nei test.

Perché SUM restituisce il totale sbagliato dopo aver fatto join tra due tabelle?

Aggiungi una terza tabella, payments, dove un ordine può essere pagato in più rate:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE payments (
  payment_id INT PRIMARY KEY,
  order_id   INT NOT NULL,
  amount     NUMERIC(10,2) NOT NULL
);

INSERT INTO payments (payment_id, order_id, amount) VALUES
  (1, 101, 25.00),
  (2, 101, 25.00),
  (3, 103, 75.00);

L’ordine 101 è di 50 $, pagato in due rate da 25 $. Ora fai join tra tutte e tre le tabelle e totalizza quanto ha ordinato ogni cliente:

1
2
3
4
5

SELECT c.name, SUM(o.amount) AS total_ordered
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN payments p ON p.order_id = o.order_id
GROUP BY c.name;

Il totale reale di Alice è 70 $ (50.00 + 20.00). Sono andate storte due cose. L’ordine 101 corrisponde a due pagamenti, quindi la join restituisce quella riga d’ordine due volte e SUM(o.amount) somma i suoi 50 $ due volte. L’ordine 102 non ha pagamenti, quindi la inner join contro payments lo elimina. Per Alice questo dà 50 + 50 + 0 = 100. Dave non ha nessun pagamento, quindi non compare nel risultato.

La regola: quando aggreghi una colonna, non fare join con tabelle di cui l’aggregazione non ha bisogno. Ogni join uno-a-molti può moltiplicare le righe o rimuoverle. Se vuoi solo i totali degli ordini, lascia fuori payments:

1
2
3
4

SELECT c.name, SUM(o.amount) AS total_ordered
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.name;

Se nello stesso report ti servono anche i totali dei pagamenti, prima fai un GROUP BY order_id su payments in una subquery o una CTE. Questo dà una riga per ordine, e puoi fare join con gli ordini in sicurezza.

NOT IN contro NOT EXISTS: come trovi le righe senza corrispondenza?

“Clienti senza ordini” sembra un lavoro per NOT IN:

1
2

SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

Funziona qui solo perché orders.customer_id è NOT NULL. Togli quel vincolo e inserisci un singolo ordine con un customer_id NULL, e la query restituisce zero righe, senza nessun errore. NOT IN si espande in una catena di confronti <> uniti da AND. x <> NULL è indefinito, quindi un solo NULL rende indefinita la condizione intera per ogni cliente.

Due pattern non hanno questo problema:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- LEFT JOIN + IS NULL
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

-- NOT EXISTS
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Entrambe restituiscono Carol ed Erin, e un NULL in orders.customer_id non influenza nessuna delle due. Nessuna delle due costruisce una lista con cui confrontare. Entrambe controllano, cliente per cliente, se esiste un ordine corrispondente. Usa NOT EXISTS come prima scelta. Usa LEFT JOIN ... IS NULL quando la query fa già join tra le due tabelle per prendere altre colonne.

Quando una join restituisce più righe di quante te ne aspetti, cerca una tabella uno-a-molti tra la join e il GROUP BY. Quando ne restituisce meno, cerca una condizione WHERE sulla tabella che può essere NULL. In entrambi i casi, correggi la query attorno alla join prima di cambiare il tipo di join.

Articoli correlati

• Come Funzionano gli Indici Database e Quando Usarli
• Cos’è Docker Compose e Come si Usa
• Come Funziona il Caching con Redis e Come Usarlo