Che cos'è il FULL JOIN in SQL? Spiegazione con 4 esempi

Che cos'è la FULL JOIN, uno dei tipi di join SQL spesso trascurati? In questo articolo vi mostreremo quanto può essere utile FULL JOIN e vi insegneremo come applicarlo a diversi scenari. I quattro esempi coprono alcuni degli usi tipici.

Una join SQL è una costruzione per combinare i dati di due o più tabelle. FULL JOIN è uno dei tipi di join. Questo articolo è utile se si ha già familiarità con le unioni SQL e il loro funzionamento. In caso contrario, o se le vostre conoscenze devono essere rinfrescate, vi consigliamo di seguire il nostro corso interattivo per colmare le lacune. SQL JOINs interattivo per colmare le lacune. Vi aspettano novantatré esercizi in cui potrete riepilogare i tipi di join ed esercitarvi a filtrare i dati utilizzando join singoli e multipli, self-join e join non equi.

Che cos'è una giunzione completa?

FULL JOIN o FULL OUTER JOIN (SQL li accetta entrambi) è un join esterno. In SQL, una join esterna è un tipo di join che include le righe non corrispondenti di una o entrambe le tabelle unite; anche LEFT JOIN e RIGHT JOIN sono join esterne. FULL JOIN è un'unione di LEFT JOIN e RIGHT JOIN: mostra le righe corrispondenti e quelle non corrispondenti di entrambe le tabelle. Quando i valori di una tabella non trovano corrispondenza nell'altra tabella, FULL JOIN restituisce NULLs. Visivamente, può essere rappresentata come segue:

Sintassi della giunzione completa

La sintassi di FULL JOIN è la seguente:

SELECT …
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

Come di consueto per le giunzioni SQL, le parti principali della sintassi FULL JOIN sono le clausole JOIN e ON. La prima tabella nella clausola FROM è seguita dalla parola chiave FULL JOIN, che unisce la seconda tabella. La condizione per cui le tabelle vengono unite è specificata nella clausola ON. Di solito, le tabelle vengono unite su una colonna condivisa tra loro, ad esempio la colonna id nella tabella writer e la colonna writer_ID della tabella book della tabella. Queste due colonne avranno gli stessi valori grazie alla relazione chiave primaria-chiave esterna.

In un semplice esempio, possiamo scrivere questo codice e ottenere il seguente risultato:

Altri tipi di join in SQL

Abbiamo già detto che FULL JOIN è solo uno dei diversi tipi di join in SQL. Ecco l'elenco completo:

• (INNER) JOIN
• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• FULL (OUTER) JOIN
• CROSS JOIN

JOIN (o INNER JOIN) è un tipo di join interno che restituisce solo le righe corrispondenti delle tabelle unite. Per saperne di più sulle INNER JOIN, consultare il sito.

LEFT JOIN (o LEFT OUTER JOIN) elenca tutte le righe della prima tabella (sinistra) e solo le righe corrispondenti della seconda tabella (destra). Se non ci sono righe corrispondenti nella tabella di destra, i valori vengono mostrati come NULL. Per saperne di più, consultare il nostro articolo sul LEFT JOIN.

RIGHT JOIN (o RIGHT OUTER JOIN) è l'immagine speculare di LEFT JOIN. Restituisce tutte le righe della seconda tabella (destra) e solo le righe corrispondenti della prima tabella (sinistra). Se nella tabella di sinistra ci sono righe non corrispondenti, i valori sono NULL.

Ora si può capire meglio perché abbiamo detto che FULL JOIN è l'unione di LEFT JOIN e RIGHT JOIN. Poiché mostra tutti i dati delle tabelle di destra e di sinistra e anche le righe non corrispondenti, è come se si usassero LEFT JOIN e RIGHT JOIN allo stesso tempo.

CROSS JOIN restituisce un prodotto cartesiano. In altre parole, restituisce tutte le combinazioni di tutte le righe di entrambe le tabelle unite.

Esempi di JOIN COMPLETA

Passiamo ora agli esempi che illustrano l'uso tipico di FULL JOIN.

Esempio 1: Mostrare tutti i dipendenti e i reparti dell'azienda

Compito: È necessario mostrare tutti i dipendenti di un'azienda, anche se non hanno un reparto assegnato. È inoltre necessario mostrare tutti i reparti, anche se non vi sono dipendenti assegnati.

Set di dati: Per risolvere il problema utilizzeremo due tabelle. La prima tabella è quella dei dipartimenti (lo script è qui), che è un elenco dei dipartimenti dell'azienda:

id	department_name
1	IT
2	Accounting
3	Sales

La seconda tabella è dipendenti, con i dati mostrati di seguito. Lo script è qui:

id	first_name	last_name	department_id
1	Sarah	Zimmerman	2
2	Thomas	Tyson	1
3	Daniel	Richardson	1
4	Sofia	Tardelli	2
5	Mark	Fitzpatrick	4

Soluzione:

SELECT 
  first_name,
  last_name,
  department_name
FROM employees e
FULL JOIN department d
  ON e.department_id = d.id;

Spiegazione: Il codice seleziona il nome e il cognome dei dipendenti e il nome del reparto.

È possibile farlo perché FULL JOIN le due tabelle. Facciamo riferimento alla tabella employees in FROM e le diamo un alias. Poi, facciamo riferimento alla seconda tabella, departmentin FULL JOIN. Le tabelle vengono unite in base agli ID dei reparti presenti in entrambe le tabelle. Questa condizione di unione è scritta nella clausola ON.

Output: L'output restituisce tutti i dati di entrambe le tabelle. Ci sono alcune righe interessanti che devono essere elaborate.

Mark Fitzpatrick non ha un reparto - il suo valore department_id valore è 5, ma non c'è nessun reparto con ID 5 nella tabella department. Pertanto, sotto il nome del reparto c'è un valore NULL. Questo potrebbe essere dovuto al fatto che si tratta di un nuovo dipendente e che la tabella non è stata aggiornata con il employees tabella non è stata aggiornata con i dati del reparto.

Inoltre, l'ultima riga mostra il reparto Vendite, ma non il nome del dipendente. Ciò indica che il reparto Vendite non ha dipendenti. Non c'è nulla di strano, perché abbiamo saputo che tutti e tre i dipendenti del reparto Vendite hanno recentemente lasciato l'azienda.

first_name	last_name	department_name
Sarah	Zimmerman	Accounting
Thomas	Tyson	IT
Daniel	Richardson	IT
Sofia	Tardelli	Accounting
Mark	Fitzpatrick	NULL
NULL	NULL	Sales

Esempio 2: Mostrare tutti i clienti con il numero di prodotti acquistati e il numero di prodotti senza vendite

Compito: C'è un negozio di dischi sul web che vende vinili (cioè dischi musicali). Il compito è elencare tutti i clienti e trovare il numero di vinili acquistati da ciascun cliente. L'output deve mostrare anche il numero di vinili che nessuno ha ancora acquistato.

Set di dati: Lavoreremo con tre tabelle. La prima è customercon lo script qui:

id	first_name	last_name
1	Marvin	Simmons
2	Marianne	Dickens
3	Susan	Strozzi

Lo script per la seconda tabella, vinyl, si trova qui. La tabella mostra l'elenco dei vinili, come si può vedere qui sotto:

id	artist	album_name	price
1	Callier, Terry	What Color is Love	24.99
2	Guy, Buddy	Sweet Tea	32.99
3	Little Simz	A Curious Tale of Trials	32.99
4	LaVette, Bettye	Scene of the Crime	36.99

La terza tabella è una tabella di giunzione chiamata purchase. Lo script è disponibile qui:

id	customer_id	vinyl_id	purchase_date
1	1	2	2023-01-03
2	1	3	2023-01-12
3	1	2	2023-02-18
4	1	3	2023-03-01
5	2	3	2023-03-01
6	2	2	2023-04-01
7	2	4	2023-05-01

Soluzione:

SELECT 
  first_name,
  last_name,
  COUNT (v.id) AS vinyl_count	   
FROM customer c
FULL JOIN purchase p
  ON c.id = p.customer_id
FULL JOIN vinyl v
  ON p.vinyl_id = v.id
GROUP BY first_name, last_name;

Spiegazione: Questo è un esempio di utilizzo di FULL JOIN e di unione di più di due tabelle.

La query seleziona i nomi dei clienti. Quindi, utilizza la funzione aggregata COUNT() per trovare il numero di vinili acquistati. FULL JOIN la tabella customer con la tabella purchase tabella. Questa operazione viene eseguita sugli ID dei clienti in entrambe le tabelle.

Ora dobbiamo unire la terza tabella. È semplice: scrivere di nuovo FULL JOIN e fare riferimento alla tabella vinyl. Ora è possibile unirla alla tabella purchase sugli ID dei vinili.

Risultato: L'output mostra che c'è un vinile che nessuno ha ancora ordinato. È possibile riconoscerlo dai NULL.

Mostra anche un elenco di tutti i clienti e il numero di vinili che hanno acquistato. Susan Strozzi non ha comprato nulla. Marianne Dickens e Marvin Simmons hanno acquistato rispettivamente tre e quattro vinili.

first_name	last_name	vinyl_count
NULL	NULL	1
Susan	Strozzi	0
Marianne	Dickens	3
Marvin	Simmons	4

Esempio 3: elencare tutte le coppie studente-certificazione e i voti degli studenti

Compito: Lavorate per una piattaforma di certificazione SQL online. Offre diverse certificazioni; ogni anno c'è una nuova edizione di ogni certificazione. Trovare tutte le possibili coppie studente-certificazione e il voto ottenuto da ogni studente in tutte le edizioni della certificazione.

Set di dati: Il set di dati è in aumento; ora comprende quattro tabelle. La prima tabella è student; qui c'è lo script. È un elenco di studenti della piattaforma:

i nostri vinili, rispettivamente.

id	first_name	last_name
1	Tom	Frank
2	Mary	Maddison
3	Pavel	Kuba
4	Amanda	Wilson

La seconda tabella è la tabella dei certificati, che contiene l'elenco dei certificati. Ecco lo script:

id	certificate_name
1	Microsoft Certified: Azure Data Fundamentals
2	Oracle Database SQL Certified Associate Certification
3	IBM Certified Database Associate
4	MySQL 5.7 Database Administrator Certification
5	EDB PostgreSQL 12 Associate Certification

La tabella successiva è una tabella di giunzione chiamata certificate_enrollment

id	student_id	edition_id	grade	pass
1	2	1	620	FALSE
2	2	6	850	TRUE
3	2	10	900	TRUE
4	1	2	100	FALSE
5	1	7	500	FALSE
6	1	7	800	TRUE
7	4	8	800	TRUE

. Lo script è disponibile qui. La tabella mostra quali studenti si sono iscritti a quale certificato, insieme ai loro voti e al loro superamento:

L'ultima tabella è una tabella di raccordo denominata certificate_edition. Essa mostra l'elenco delle edizioni dei certificati, collegandosi alla tabella certificate. Lo script è qui:

id	certificate_id	edition
1	1	2022
2	2	2022
3	3	2022
4	4	2022
5	5	2022
6	1	2023
7	2	2023
8	3	2023
9	4	2023
10	5	2023

Soluzione:

SELECT 
  first_name,
  last_name,
  certificate_name,
  edition,
  grade
FROM student s
FULL JOIN certificate_enrollment cen
  ON s.id = cen.student_id
FULL JOIN certificate_edition ced
  ON cen.edition_id = ced.id
FULL JOIN certificate c
  ON ced.certificate_id = c.id;

Spiegazione: Selezioniamo innanzitutto tutte le colonne rilevanti da tutte e quattro le tabelle: first_name e last_name da student, certificate_name da certificate, edition da certificato_edizione e grade da certificate_enrollment.

Quindi, uniamo le tabelle come negli esempi precedenti. Per prima cosa, la tabella studentFULL JOIN con certificate_enrollment sugli ID degli studenti.

Il secondo FULL JOIN aggiunge la tabella certificate_edition per unirla alla tabella certificate_enrollment. Le tabelle sono unite in base all'ID dell'edizione del certificato.

Ora che abbiamo unito tre tabelle, possiamo aggiungere la quarta. Facciamo riferimento alla tabella certificate in FULL JOIN. La uniamo a certificate_edition in base all'ID del certificato.

Output: L'output mostra esattamente ciò che vogliamo.

C'è un elenco di tutti gli studenti che hanno ricevuto un certificato, la sua edizione e il voto ottenuto da ogni studente. Se il voto è inferiore a 700, lo studente è stato bocciato e ha dovuto ripetere l'esame per il certificato.

Uno studente non si è ancora iscritto a un programma di certificazione: si tratta di Pavel Kuba. Inoltre, ci sono quattro edizioni del certificato senza studenti iscritti.

first_name	last_name	certificate_name	edition	grade
Mary	Maddison	Microsoft Certified: Azure Data Fundamentals	2022	620
Mary	Maddison	Microsoft Certified: Azure Data Fundamentals	2023	850
Mary	Maddison	EDB PostgreSQL 12 Associate Certification	2023	900
Tom	Frank	Oracle Database SQL Certified Associate Certification	2022	100
Tom	Frank	Oracle Database SQL Certified Associate Certification	2023	500
Tom	Frank	Oracle Database SQL Certified Associate Certification	2023	800
Amanda	Wilson	IBM Certified Database Associate	2023	800
Pavel	Kuba	NULL	NULL	NULL
NULL	NULL	EDB PostgreSQL 12 Associate Certification	2022	NULL
NULL	NULL	MySQL 5.7 Database Administrator Certification	2022	NULL
NULL	NULL	IBM Certified Database Associate	2022	NULL
NULL	NULL	MySQL 5.7 Database Administrator Certification	2023	NULL

Esempio 4: trovare tutte le coppie autore-argomento e mostrare gli articoli e il loro stato

Compito: Lavorate per un'azienda che assume scrittori di articoli per i propri clienti. Esiste un elenco di scrittori e di argomenti offerti. Inoltre, ogni argomento ha attualmente diversi articoli disponibili. Gli scrittori sono assegnati a questi articoli. Un articolo può avere uno dei due stati: "Scrittura" (l'articolo è in fase di scrittura) o "Revisione" (l'articolo è in fase di revisione).

Trovare tutte le coppie scrittore-argomento. Inoltre, elencare tutti gli articoli e il loro stato.

Set di dati: Anche questo dataset è composto da quattro tabelle. La prima tabella è writercon lo script collegato qui:

id	first_name	last_name
1	Victoria	Thompson
2	Mike	McGill
3	Sky	Herrera
4	Jimmy	Goodman

La tabella successiva è topic. Ecco lo script:

id	topic_name
1	SQL
2	Python
3	ML
4	SQL Careers
5	Python Careers

La terza tabella è article_assignment. Collega lo scrittore con l'articolo e mostra lo stato attuale dell'articolo. Ecco lo script:

id	writer_id	article_id	status
1	4	3	Revising
2	4	1	Writing
3	3	5	Writing
4	3	8	Revising
5	3	10	Revising
6	1	7	Writing

La quarta tabella collega l'articolo con l'argomento. Si chiama articlee lo script è qui:

id	article_title	topic_id
1	What is FULL JOIN in SQL? An Explanation with 4 Examples	1
2	Pandas in Python	2
3	Supervised Learning	3
4	Basic SQL Interview Questions	4
5	Basic Python Interview Questions	5
6	SQL Window Functions	1
7	Ranking Data in Python	2
8	Unsupervised Learning	3
9	Intermediate SQL Interview Questions	4
10	Intermediate Python Interview Questions	5

Soluzione:

SELECT 
  first_name,
  last_name,
  topic_name,
  article_title,
  status
FROM writer w
FULL JOIN article_assignment aa
  ON w.id = aa.writer_id
FULL JOIN article a
  ON aa.article_id = a.id
FULL JOIN topic t
  ON a.topic_id = t.id;

Spiegazione: La query è simile alla precedente. Seleziona tutte le colonne rilevanti: il nome dello scrittore, l'argomento, il titolo dell'articolo e il suo stato. Successivamente, unisce le tabelle writer e article_assignment sull'ID dello scrittore utilizzando FULL OUTER JOIN.

Un altro FULL JOIN aggiunge la terza tabella article e la unisce all'ID dell'articolo. Infine, la quarta tabella viene unita all'ID dell'argomento.

Output: Ecco un elenco di tutti gli autori, gli argomenti, gli articoli e gli stati.

Gli argomenti di Jimmy Goodman sono ML e SQL. Sky Herrera scrive di carriere Python e ML. Victoria Thompson scrive solo di Python.

Mike McGill non ha articoli assegnati. Inoltre, ci sono un articolo su Python, uno su SQL e due su SQL Career non assegnati a nessuno scrittore.

first_name	last_name	topic_name	article_title	status
Jimmy	Goodman	ML	Supervised Learning	Revising
Jimmy	Goodman	SQL	What is FULL JOIN in SQL? An Explanation with 4 Examples	Writing
Sky	Herrera	Python Careers	Basic Python Interview Questions	Writing
Sky	Herrera	ML	Unsupervised Learning	Revising
Sky	Herrera	Python Careers	Intermediate Python Interview Questions	Revising
Victoria	Thompson	Python	Ranking Data in Python	Writing
Mike	McGill	NULL	NULL	NULL
NULL	NULL	Python	Pandas in Python	NULL
NULL	NULL	SQL	SQL Window Functions	NULL
NULL	NULL	SQL Careers	Basic SQL Interview Questions	NULL
NULL	NULL	SQL Careers	Intermediate SQL Interview Questions	NULL

No FULL JOIN, No SQL Mastery

FULL JOIN Probabilmente non è usato così spesso come JOIN o LEFT JOIN. È un'unione umile che aspetta fino a quando non è necessario per brillare. Ma senza di essa nel vostro repertorio, non potete definirvi un maestro di SQL.

Gli esempi precedenti hanno mostrato scenari pratici in cui è necessario utilizzare tutti i dati di due (o più) tabelle. FULL JOIN lo rende davvero facile!

Naturalmente, è necessario fare ancora un po' di pratica, poiché la parte più importante dell'uso delle join SQL è decidere quale join usare. Una volta acquisita la padronanza di questo aspetto, le join diventano facili: la sintassi è la stessa, indipendentemente dal tipo di join utilizzato. Il nostro SQL JOINs può aiutarvi a padroneggiare JOIN, perché è ricco di esercizi pratici. Lo stesso vale per il nostro articolo di esercitazione sulle unioni SQL con 12 esempi. Dopo aver imparato tutto questo, anche le domande del colloquio SQL JOIN non saranno difficili da rispondere.

Ora sta a voi utilizzare tutte queste risorse per padroneggiare FULL JOIN!