Guida illustrata alle Join multiple

Finora, i nostri articoli della serie "Una guida illustrata" hanno spiegato diversi tipi di join: INNER JOINs, OUTER JOINs (LEFT JOIN, RIGHT JOIN, FULL JOIN), CROSS JOIN, self-join e non-equi join. In questo articolo conclusivo della serie, vi mostriamo come creare query SQL che abbinano i dati di più tabelle utilizzando uno o più tipi di join.

Tipi di join nelle query SQL

Prima di iniziare a discutere di esempi di query SQL che utilizzano più tipi di join, facciamo un breve riepilogo dei tipi di join che abbiamo trattato finora, per essere sicuri di capire le differenze. A tal fine, ecco un breve riepilogo sotto forma di tabella. Date un'occhiata:

Type of JOIN	Matching records from tables	Explanation
INNER JOIN(JOIN) SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t JOIN color c ON t.color_id = c.id ;	Result: id size color_id id color 2 M 1 1 yellow 3 NULL 3 3 blue	An INNER JOIN returns records that match in both tables.
LEFT JOIN SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t LEFT JOIN color c ON t.color_id = c.id ;	Result: id size color_id id color 2 M 1 1 yellow 3 NULL 3 3 blue 1 S NULL NULL NULL	A LEFT JOIN returns all records from the left table, even when they do not match in the right table. Missing values become NULLs.
RIGHT JOIN SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t RIGHT JOIN color c ON t.color_id = c.id ;	Result: id size color_id id color 2 M 1 1 yellow 3 NULL 3 3 blue NULL NULL NULL 2 NULL	A RIGHT JOIN returns all records from the right table, even when they do not match those in the left table. Missing values become NULLs.
FULL JOIN SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t FULL JOIN color c ON t.color_id = c.id ;	Result: id size color_id id color 2 M 1 1 yellow 3 NULL 3 3 blue 1 S NULL NULL NULL NULL NULL NULL 2 NULL	A FULL JOIN returns all records from both tables: left and right, even when rows do not match. Missing values become NULLs.
CROSS JOIN SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t CROSS JOIN color c ;	Result: id size color_id id color 2 M 1 1 yellow 2 M 1 2 NULL 2 M 1 3 blue 3 NULL 3 1 yellow 3 NULL 3 2 NULL 3 NULL 3 3 blue 1 S NULL 1 yellow 1 S NULL 2 NULL 1 S NULL 3 blue	A CROSS JOIN returns the Cartesian product of the records from both tables. This means that each record from the left table is joined with each record from the right table. Missing values become NULLs.

Si noti che il risultato di ogni tipo di join contiene dati provenienti dalle tabelle tshirt e color. Il tipo di join specifico determina il contenuto della tabella da restituire.

Che cos'è un join multiplo in SQL?

Ogni query può comprendere zero, una o più join. Una join multipla è l'uso di più di una join in una singola query. Le join utilizzate possono essere tutte dello stesso tipo, oppure possono essere di tipo diverso. Inizieremo la nostra discussione mostrando un esempio di query che utilizza due join dello stesso tipo. Guardate la query qui sotto.

SELECT v.name, c.name,  p.lastname
FROM vehicle v
INNER JOIN color c ON  v.color_id = c.id
INNER JOIN person p ON v.person_id = p.id ;

La query invoca due INNER JOINs per unire tre tabelle: vehicle, person e color. Verranno restituiti solo i record che hanno una corrispondenza in ciascuna tabella. Per prima cosa, si possono osservare gli insiemi di dati che sono stati uniti.

Ci sono tre insiemi di dati che corrispondono a tre tabelle del database: vehicle, color e person, rappresentati di seguito.

person

color

vehicle

Si può notare che ogni vehicle nella tabella dei veicoli ha un colore, tranne motorcycle. Ogni veicolo ha un proprietario assegnato, tranne bicycle, che non ha un proprietario. Uno dei colori (blue) della tabella color tabella non è assegnato a nessun veicolo. Inoltre, la moto non ha un colore disponibile nel database. D'altra parte, bicycle ha un colore assegnato, ma non ha un proprietario. Infine, alla persona denominata Miller non è assegnato alcun veicolo.

Nella query precedente è stato utilizzato un join multiplo per recuperare solo i veicoli a cui sono stati assegnati un colore e un proprietario. La tabella vehicle ha la colonna color_id che identifica la colonna color nella tabella dei colori e la colonna person_id che identifica il proprietario person nella tabella delle persone.

Risultato della query:

Risulta che solo due record corrispondono ai criteri definiti dalle due giunzioni interne.

L'immagine seguente presenta la sequenza con cui sono stati uniti i record delle rispettive tabelle.

Si noti che tutte le operazioni di JOIN vengono eseguite da sinistra a destra. Nel primo passo, vengono abbinate le tabelle della prima JOIN (tabelle vehicle e color). Di conseguenza, viene creata una tabella intermedia. Nel secondo passo, questa tabella intermedia (trattata come tabella di sinistra) viene unita a un'altra tabella (tabella person) utilizzando il secondo JOIN.

Ricordate che una singola JOIN di qualsiasi tipo produce una singola tabella intermedia (comunemente chiamata tabella derivata) durante una query multi-join.

Join misto sinistro e destro con join interno

È anche possibile combinare diversi tipi di join in una query multi-join. Facciamo un esempio con INNER JOIN e LEFT JOIN. Supponiamo di voler interrogare il nostro database per trovare tutte le persone che possiedono un veicolo colorato o che non ne possiedono affatto.

Intuitivamente, si partirebbe dalla tabella person e unirla alla tabella vehicle tabella utilizzando un LEFT JOIN. In questo caso, il LEFT JOIN farebbe corrispondere ogni record della tabella con un record della tabella. person con un record della tabella vehicle e per tutte le persone per le quali non è stato trovato un record corrispondente, riempirebbe i valori mancanti con NULLs. Questa unione produrrà un elenco di tutte le persone nel database con i dati relativi ai veicoli associati, anche se non ne possiedono uno. Ma a noi interessa vedere solo i veicoli con i colori assegnati. Questo significa che dobbiamo usare un INNER JOIN sulle tabelle vehicle e color. Ecco una query che soddisfa questo particolare requisito, ma fa il suo dovere?

SELECT v.name vehicle_name, c.name color_name,  p.lastname
FROM person p
LEFT JOIN vehicle v ON  v.person_id = p.id
INNER JOIN color c ON v.color_id = c.id ;

No! Questa query restituisce lo stesso risultato ottenuto con la nostra query precedente (che utilizzava solo INNER JOINs). Il nostro elenco non include coloro che non possiedono un veicolo.

Risultato:

Ma cosa è successo? INNER JOIN ha saltato i risultati che non corrispondevano in entrambe le tabelle, cioè nella tabella derivata (creata unendo le tabelle person e vehicle) e nella tabella color e nella tabella. Come si può risolvere questo problema?

La seguente query presenta una delle possibili soluzioni. In questo caso, la tabella derivata restituisce solo i veicoli con i colori, e viene poi RIGHT JOINcon la tabella person per ottenere tutte le persone.

SELECT p.lastname, v.name, c.name
FROM vehicle v
INNER JOIN color c ON  v.color_id = c.id
RIGHT JOIN person p ON v.person_id = p.id ;

Risultato:

Ora abbiamo un elenco di tutte le persone: quelle con veicoli colorati e quelle senza veicoli. Abbiamo iniziato con un INNER JOIN di tabelle vehicle e color. Ogni veicolo incluso nella tabella derivata deve avere un colore assegnato, motivo per cui questo tipo di join è appropriato. Avendo selezionato i veicoli colorati, possiamo ora utilizzare una RIGHT JOIN sulla tabella derivata con la tabella person e in questo modo si ottengono le persone che non sono proprietarie di veicoli insieme a quelle (della tabella derivata) che possiedono un veicolo colorato.

Un altro metodo per risolvere questo problema consiste nell'utilizzare una LEFT JOIN sulla tabella persone e una sottoquery in cui si utilizza una INNER JOIN sulle tabelle vehicle e color.

Guardate la query qui sotto.

SELECT p.lastname, o.vehicle_name, o.color_name
FROM person p LEFT JOIN
(  SELECT v.name vehicle_name, c.name color_name, v.person_id
    FROM vehicle v
    INNER JOIN color c ON v.color_id=c.id
) o ON  o.person_id = p.id;

JOIN miste con Full JOIN

Un altro tipo di join multiplo utilizza i join completi. Per prima cosa, diamo un'occhiata a una join multipla con solo join completi.

SELECT p.lastname, v.name, c.name
FROM vehicle v
FULL JOIN color c ON  v.color_id = c.id
FULL JOIN person p ON v.person_id = p.id ;

La query di cui sopra confronta i record di tre tabelle: person, vehicle e color in modo tale che anche i record senza corrispondenza nelle altre due tabelle appaiano nella tabella dei risultati. Le colonne vuote saranno riempite con i valori di NULL. Per questo motivo, la query restituisce tutte le persone, indipendentemente dal fatto che abbiano o meno un veicolo, tutti i veicoli, indipendentemente dal fatto che abbiano o meno un colore assegnato, e tutti i colori, indipendentemente dal fatto che siano assegnati a un veicolo.

Risultato:

Abbiamo usato le join complete per unire tutti i record, anche quelli che non corrispondono. Ricordiamo che le join complete restituiscono tutti i record, mentre le join interne restituiscono solo quelli che corrispondono.

L'immagine seguente spiega la sequenza con cui sono state unite le tabelle.

FULL JOIN può anche comparire in una query con un altro tipo di join, creando così un join multiplo con tipi misti. La query seguente fa uso di una FULL JOIN con una INNER JOIN.

SELECT p.lastname, v.name, c.name
FROM vehicle v
INNER  JOIN color c ON  v.color_id = c.id
FULL  JOIN person p ON v.person_id = p.id ;

Questa query ci permette di recuperare un elenco di tutte le persone, che siano o meno proprietari di veicoli, e di tutti i veicoli a cui è stato assegnato un colore.

Ecco come funzionano i due join:

Innanzitutto, le tabelle vehicle e color vengono combinate con un INNER JOIN. Quindi la tabella derivata viene combinata con la tabella person utilizzando un FULL JOIN. Ecco il risultato:

Riepilogo

Una singola query SQL può unire due o più tabelle. Quando sono coinvolte tre o più tabelle, le query possono utilizzare un singolo tipo di join più di una volta, oppure possono utilizzare più tipi di join. Quando si utilizzano più tipi di join è necessario considerare attentamente la sequenza di join per ottenere il risultato desiderato. Gli esempi presentati in questo articolo dimostrano chiaramente come un piccolo cambiamento nel tipo di join (o, nel caso di join multipli, nell'ordine in cui compaiono nella query) possa cambiare completamente il risultato della query, determinandone o meno il successo.

A quali combinazioni di join dobbiamo prestare particolare attenzione? INNER JOINs con OUTER JOINs e OUTER JOINs con OUTER JOINs. Ognuna di queste combinazioni può produrre risultati di query errati se usata in modo inappropriato.

Per saperne di più su SQL

La conoscenza di base delle unioni SQL è indispensabile, ma la maggior parte dei principianti di SQL si sente intimidita dalle istruzioni JOIN. La verità è che non c'è assolutamente nulla da temere!

In questo articolo abbiamo discusso come utilizzare join multipli in un'unica query, sia di tipo LIKE che di tipo JOIN misto. Ulteriori informazioni sui join sono disponibili nel materiale completo su SQL di Vertabelo Academy. Durante i corsi, aumenterete notevolmente le vostre competenze, testando e affinando le vostre nuove abilità attraverso gli esercizi pratici interattivi forniti. Iniziate dal corso SQL Basics se non avete conoscenze pregresse di SQL. Il corso SQL JOINs vi permetterà di fare pratica con l'interrogazione di più tabelle grazie a numerosi esercizi interattivi sulle istruzioni di JOIN. Provatelo subito gratuitamente!