Che cos'è un LEFT OUTER JOIN in SQL? Spiegazione con 4 esempi

Nell'articolo di oggi parleremo del LEFT OUTER JOIN in SQL. Esamineremo diversi esempi di utilizzo della LEFT OUTER JOIN e la confronteremo con la INNER JOIN.

JOIN è una funzione di SQL che consente di combinare i dati di due o più tabelle. La natura dei database relazionali rende JOIN una delle funzioni più utilizzate in SQL. Perché? In pratica, è molto raro che tutti i dati richiesti si trovino in un'unica tabella. Anche i database più primitivi sono composti da almeno due tabelle.

Esistono diversi tipi di JOINs. In questa sede ci concentreremo su LEFT OUTER JOIN. Per avere un quadro completo dei diversi tipi di JOINs, provate il nostro corso interattivo. SQL JOINs interattivo. Oltre alle nozioni di base, spiega l'unione di più tabelle e l'uso di join auto- e non-equi. Completerete oltre 90 esercizi pratici, tutti basati su esempi reali!

Introduciamo il sito LEFT OUTER JOIN; poi lo renderemo più interessante con esempi pratici. Se nel corso dell'esercitazione avete bisogno di un ripasso di alcuni concetti, ricordate la nostra scheda informativa sulle JOIN di SQL.

Che cos'è un LEFT OUTER JOIN?

La più comune JOIN è INNER JOIN. È un tipo di join che restituisce solo le righe corrispondenti di entrambe le tabelle unite. Esistono altri tipi JOIN che possono restituire le righe di una tabella unita anche se la riga non ha una corrispondenza nell'altra tabella. Questi tipi di JOINs sono chiamati join esterni.

Una LEFT JOIN è un tipo di join esterno che restituisce tutte le righe della tabella di sinistra e le righe corrispondenti della tabella di destra.

Qual è la differenza tra LEFT OUTER JOIN e LEFT JOIN?

Risposta breve: Non c'è differenza!

Risposta lunga: Il nome completo di questo tipo di join è LEFT OUTER JOIN. Probabilmente avete visto esempi di codice SQL in cui le tabelle sono unite solo con LEFT JOIN. Questo perché SQL accetta sia LEFT OUTER JOIN che LEFT JOIN.

Poiché LEFT JOIN è più corto, viene usato più spesso. Quando lo si vede, significa semplicemente LEFT OUTER JOIN. L'aggettivo "Outer" è implicito, in quanto non esiste altra join sinistra se non una left outer join.

Sintassi della congiunzione sinistra [esterna]

La sintassi di LEFT JOIN segue quella standard di JOIN:

1. Fare riferimento alla prima tabella in FROM.
2. Usare la parola chiave LEFT JOIN per fare riferimento alla seconda tabella.
3. Usare la parola chiave ON per specificare la condizione di unione.

In altre parole, la sintassi è la seguente:

SELECT …
FROM table_1
LEFT JOIN table_2
ON table_1.column = table_2.column;

Come funziona LEFT OUTER JOIN? Nell'esempio generico di cui sopra, la query farà riferimento a table_1 e la unirà a sinistra con table_2. In primo luogo restituirà tutte le righe di table_1, indipendentemente dalla condizione di unione. Perché? Perché questa è la natura di LEFT JOIN: restituisce tutte le righe della tabella di sinistra (cioè la prima).

Poi la query esaminerà la condizione di unione - in questo caso, quando un valore della colonna di table_2 corrisponde a un valore della colonna da table_1. LEFT JOIN restituirà solo i valori della tabella di destra (cioè della seconda tabella, o tabella_2) in cui la condizione di unione è soddisfatta. Se nella tabella di destra non ci sono valori di questo tipo, i valori restituiti saranno NULL. Si può vedere un esempio visivo di questo nel nostro articolo su come funziona il LEFT JOIN.

Quando le tabelle sono unite, è ovvio che si può scegliere qualsiasi colonna da entrambe le tabelle in SELECT.

Ora che si conosce la sintassi, non resta che metterla in pratica.

4 Esempi di LEFT OUTER JOIN

Il set di dati per gli esempi 1-3

Per prima cosa, introduciamo il set di dati.

La prima tabella, departmentscontiene i seguenti dati:

La query per la creazione della tabella è disponibile qui.

La seconda tabella è employeesed è possibile crearla utilizzando questa query. La tabella contiene i seguenti dati:

Due dipendenti hanno il valore NULL nella colonna department_id. Si tratta di nuovi record di dipendenti che non sono ancora stati aggiornati con il reparto.

Esempio 1: Trovare tutti i dipendenti e i loro dipartimenti

Utilizziamo le tabelle precedenti per elencare tutti i dipendenti e i loro reparti.

Questo è un articolo su LEFT OUTER JOIN, quindi non c'è da sorprendersi: useremo proprio questa join per risolvere questo problema. Ecco la query:

SELECT e.id,
	 e.first_name,
	 e.last_name,
	 d.department_name
FROM employees e 
LEFT JOIN departments d
ON e.department_id = d.id
ORDER BY e.id;

I dati che ci servono sono i numeri identificativi, i nomi e i reparti dei dipendenti. Ecco perché queste colonne sono in SELECT.

Per ottenere tutte queste colonne, dobbiamo accedere ai dati di entrambe le tabelle. Vediamo come fare.

Innanzitutto, la tabella employees è citata in FROM. Perché questa tabella e non l'altra? Ricordare: La tabella a cui si fa riferimento per prima è importante in LEFT JOIN. Perché? Perché quella tabella è la tabella di sinistra e LEFT JOIN restituirà tutte le righe di quella tabella.

Facciamo riferimento alla tabella employees perché dobbiamo elencare tutti i dipendenti di quella tabella. Quindi facciamo riferimento alla tabella departments. Uniamo entrambe le tabelle a condizione che la colonna department_id della tabella employees sia uguale alla colonna id della tabella departments. Queste due colonne sono dimensioni condivise (chiave primaria e chiave esterna) tra le due tabelle, quindi sono ideali per essere utilizzate nella condizione di join.

Per avere un output più leggibile, ordiniamo i risultati per ID dipendente:

L'output elenca tutti i dipendenti, tutti e 15, e mostra anche i loro dipartimenti, che sono i più importanti. Mostra anche i loro reparti, che sono "Contabilità" e "Vendite". Si noterà che alcuni dipendenti hanno valori NULL nella colonna department_name. Due di questi sono stati menzionati in precedenza: nuovi dipendenti senza un ID reparto aggiornato nella tabella. Questi avevano i valori NULL nella tabella iniziale employees iniziale e sono contrassegnati in blu.

Ci sono invece altri dipendenti, contrassegnati in verde, con valori NULL. Questo è il risultato di LEFT JOIN. Tutti questi dipendenti hanno un ID reparto di 4, ma la tabella non contiene questo valore. departments non contiene questo valore. Sembra che la tabella departments potrebbe richiedere un aggiornamento. Un database davvero scadente per un'azienda fittizia così rispettabile!

Ricordate: I valori della tabella di sinistra non trovati nella tabella di destra vengono visualizzati come NULL.

E se usassimo una INNER JOIN?

Scriviamo di nuovo la query precedente, questa volta con un INNER JOIN (di solito abbreviato in JOIN) invece di un LEFT JOIN:

SELECT e.id,
	 e.first_name,
	 e.last_name,
	 d.department_name
FROM departments d
JOIN employees e
ON e.department_id = d.id
ORDER BY e.id;

La sintassi è esattamente la stessa; abbiamo solo usato un tipo di join diverso. Vediamo quale sarà il risultato:

Mancano alcuni dipendenti; qui ce ne sono solo nove. Un'analisi più attenta mostra che i dipendenti con ID 3, 4, 7, 9, 11 e 12 non sono inclusi nel risultato. Perché? Se si torna indietro, si vedrà che i dipendenti mancanti sono quelli con NULLs in department_name nell'output LEFT OUTER JOIN.

Ricordate che INNER JOIN restituisce solo le righe corrispondenti di entrambe le tabelle, ovvero solo i dipendenti che hanno un ID reparto presente in entrambe le tabelle.

Quindi, in questa situazione, in cui volevamo tutti i dipendenti e i loro reparti, LEFT JOIN è la scelta giusta. Ora vediamo anche i dipendenti senza il reparto e possiamo dire che il nostro database deve essere aggiornato.

Esempio 2: Elenco di tutti i reparti e dei loro dipendenti

Per ottenere il risultato desiderato, dobbiamo cambiare l'ordine delle tabelle in LEFT JOIN.

Ecco la query:

SELECT d.id,
	 d.department_name,
	 e.first_name,
	 e.last_name
FROM  departments d
LEFT JOIN employees e
ON d.id = e.department_id
ORDER BY d.id;

Modifichiamo l'ordine delle colonne in SELECT per presentare meglio il risultato. La maggior parte delle colonne sono le stesse di prima. Questa volta, selezioniamo l'ID del reparto dalla tabella departmentse non l'ID del dipendente.

Ora la tabella departments è la nostra tabella di sinistra. Questo perché vogliamo mostrare tutti i reparti di quella tabella, indipendentemente dal fatto che compaiano o meno nella tabella employees - la nostra tabella di destra.

La condizione ON rimane la stessa; abbiamo solo invertito l'ordine delle colonne. Non ha molta importanza; avrebbe potuto rimanere nello stesso ordine. È solo per ragioni estetiche, perché è più facile leggere la condizione quando segue l'ordine delle tabelle in LEFT JOIN.

Inoltre, ordiniamo l'output per ID reparto.

Ecco il risultato:

Questo output mostra solo nove dipendenti, rispetto ai 15 dell'esempio precedente. Tutti i dipendenti con valori NULL nell'output dell'ultimo esempio non compaiono in questo output.

Il motivo è, ancora una volta, LEFT JOIN. Quando abbiamo creato la departments la nostra tabella di sinistra, ha elencato tutti i dipartimenti e i loro dipendenti. Tutti gli altri dipendenti non sono presenti. Perché? O hanno l'ID di reparto 4, che non compare nella tabella departmentsoppure sono nuovi dipendenti (ve li ricordate?) con NULL come ID reparto.

L'unico NULLs in questo output appare nell'ultima riga. C'è il reparto "Compliance", che non ha alcun dipendente assegnato ad esso. In altre parole, non ci sono dipendenti con il valore 5 nella colonna department_id della tabella. employees tabella.

Ricordate: L'ordine delle tabelle in LEFT JOIN è importante!

E se avessimo usato una INNER JOIN?

Cambiamo la query e usiamo una INNER JOIN:

SELECT d.id,
	 d.department_name,
	 e.first_name,
	 e.last_name
FROM  departments d
INNER JOIN employees e
ON d.id = e.department_id
ORDER BY d.id;

L'output è praticamente lo stesso di quello ottenuto con l'inner join dell'Esempio 1. L'unica differenza è che c'è un ID reparto invece di una tabella. L'unica differenza è che c'è un ID reparto invece di un ID dipendente. A parte questo, è lo stesso, quindi non perderemo tempo ad analizzarlo.

Esempio 3: Elenco di tutti i dipartimenti e del numero di dipendenti in ciascuno di essi

LEFT JOINLa funzione di join, come tutte le altre, viene spesso utilizzata con le funzioni aggregate di SQL. In questo caso ci tornerà utile.

Date un'occhiata a questa query:

SELECT d.department_name, 
 COUNT(e.id) AS number_of_employees
FROM departments d
LEFT JOIN employees e
ON d.id = e.department_id
GROUP BY d.department_name;

Partiamo dalla clausola FROM. Poiché vogliamo elencare tutti i reparti esistenti, facciamo prima riferimento alla tabella departments in FROM. Perché? Perché ci sono alcuni dipendenti senza reparto e non li vogliamo nel nostro risultato. Come abbiamo visto, c'è anche un reparto senza dipendenti; vogliamo che questo reparto sia presente nel risultato.

Segue la tabella employees dopo LEFT JOIN. Le tabelle sono unite in base alla stessa condizione dell'esempio precedente.

Per ottenere l'output corretto, è necessario elencare il nome del reparto in SELECT. È inoltre necessario utilizzare la funzione aggregata COUNT(e.id) per contare il numero di ID dei dipendenti.

Perché non si può usare COUNT(*)? Perché conta tutte le righe, compresi i valori di NULL. Questo falserebbe i risultati. Il dipartimento di conformità ha zero dipendenti, che sarebbero stati mostrati come NULL al momento dell'unione. COUNT(*) avrebbe contato questo NULL come uno, il che non sarebbe stato accurato.

L'opzione COUNT(e.id) ignora i valori di NULL e conterà solo i dipendenti il cui ID non è NULL. Questo è un argomento che riguarda più le funzioni aggregate che LEFT JOIN, quindi non entreremo in ulteriori dettagli.

Tuttavia, è estremamente importante capire quando utilizzare le varie opzioni COUNT(). Per ulteriori informazioni, potete consultare il nostro articolo che illustra in dettaglio tutte le diverse incarnazioni e gli usi della funzione COUNT().

Torniamo al nostro codice. Dopo il conteggio e l'unione, l'output viene raggruppato in base al nome del reparto. Il risultato sarà l'elenco dei reparti con il numero di dipendenti di ciascun reparto:

Il risultato mostra che ci sono in totale nove dipendenti in tutti i reparti dell'azienda: sei nella Contabilità, zero nella Conformità e tre nelle Vendite.

E se usassimo una INNER JOIN?

Ora eseguiamo la stessa query ma con INNER JOIN.

SELECT d.department_name, 
	 COUNT(e.id) AS number_of_employees
FROM departments d
JOIN employees e
ON d.id = e.department_id
GROUP BY d.department_name;

Il risultato sarà diverso? Vediamo.

Il risultato, ancora una volta, mostra che ci sono nove dipendenti nei vari dipartimenti. Ciò che ovviamente manca è che l'azienda ha un reparto di Conformità che non viene mostrato in questo risultato.

Se avessimo usato INNER JOIN, avremmo concluso che ci sono solo due dipartimenti nell'azienda. LEFT JOIN è stata, ancora una volta, la scelta giusta perché potrebbe esserci (almeno temporaneamente) un dipartimento senza dipendenti.

Set di dati per l'esempio 4

In questo esempio utilizzeremo tre tabelle. La prima è artistscontiene i seguenti dati sugli artisti musicali:

Creare la tabella utilizzando la query qui.

La tabella successiva è albums:

Ecco la query per creare questa tabella.

La query per la terza tabella è qui. La tabella si chiama grammy_award. È un elenco delle categorie dei premi Grammy. Ci sono solo due categorie: il vincitore del miglior album e il candidato al miglior album.

Esempio 4: Elenco di tutti gli artisti, dei loro album e della categoria dei premi Grammy

LEFT JOIN può essere utilizzato anche per unire più di due tabelle e lo vedremo nella query che segue:

SELECT artist_name,
	 album_title,
	 grammy_category
FROM artists ar
LEFT JOIN albums al
ON ar.id = al.artist_id
LEFT JOIN grammy_award ga
ON al.grammy_category_id = ga.id;

Per prima cosa, abbiamo elencato tutte le colonne richieste per mostrare il nome dell'artista, il titolo dell'album, l'anno di pubblicazione e la categoria del Grammy Award.

Dalle tabelle presentate vediamo che ci sono album senza informazioni sull'artista. Il nostro catalogo è incompleto, quindi il modo più sicuro per elencare tutti gli artisti disponibili è LEFT JOIN e considerare la tabella artists come tabella di sinistra. È unita a sinistra con la tabella albums sull'ID dell'artista.

Per recuperare i dati sui Grammy, dobbiamo unire in qualche modo la terza tabella. Come si fa? Semplice: scrivere di nuovo il comando LEFT JOIN e fare riferimento alla tabella grammy_award dopo di esso. In questo modo LEFT JOIN albums con grammy_award. Le tabelle sono unite dall'ID della categoria Grammy.

Vediamo il risultato:

Il risultato mostra tutti gli artisti, i loro album e se l'album è stato candidato o vincitore del premio Album dell'anno. Vengono inoltre mostrati gli album che non sono stati né candidati né vincitori ai Grammy.

E se usassimo INNER JOIN?

Ecco lo stesso codice di prima, con JOIN al posto di LEFT JOIN:

SELECT artist_name,
	 album_title,
	 grammy_category
FROM artists ar
JOIN albums al
ON ar.id = al.artist_id
JOIN grammy_award ga
ON al.grammy_category_id = ga.id;

Vediamo cosa restituisce il codice:

Questo risultato è incompleto. Manca un artista: George Benson. Mancano anche gli album senza categoria Grammy. Poiché non stavamo cercando di elencare solo gli album con una categoria Grammy, LEFT JOIN era la scelta giusta.

Ecco alcune spiegazioni aggiuntive su come effettuare il LEFT JOIN di più tabelle.

Angolo dei principianti: Alcuni suggerimenti sulla scrittura delle JOIN

Ecco una panoramica degli errori più comuni che i principianti commettono quando usano LEFT OUTER JOIN in SQL.

Li abbiamo già trattati negli esempi, ma potrebbero non essere così evidenti per un occhio meno esperto. Vediamo quindi di tradurli in parole.

Scegliere la join corretta

Abbiamo confrontato tutti gli esempi LEFT JOIN con le versioni INNER JOIN. Come si è visto, l'output cambia in modo significativo. Per questo motivo, è necessario scegliere con attenzione quale join utilizzare.

Il consiglio migliore è quello di pensare alle due definizioni di join. Quindi, se avete bisogno di far corrispondere solo i dati di due tabelle, allora avete bisogno di INNER JOIN. In tutti gli altri casi, LEFT JOIN sarà probabilmente la scelta corretta.

Decidere quale sia la tabella di sinistra

Una volta scelto di usare LEFT JOIN, come si fa a sapere quale tabella deve essere quella di sinistra? Innanzitutto, la tabella di sinistra è quella che viene subito dopo FROM. Quella di destra è quella che viene dopo LEFT JOIN.

Ma come si fa a decidere qual è? È necessario comprendere correttamente il problema che si sta risolvendo. Cercate spunti nella formulazione e nella logica. Se avete bisogno di tutti i dipendenti, la tabella con i dipendenti sarà quella di sinistra. In altre parole, se si ritiene che una delle tabelle debba essere mostrata invariata, quella è la tabella di sinistra.

Attenzione alle funzioni aggregate

Anche se si commette un errore e si sceglie la join sbagliata, è molto probabile che si riesca a individuarla nell'output se non si aggregano i dati. Se si ha familiarità con i dati, si noteranno alcune righe mancanti o non NULLs dove ci si aspettava che fossero.

Tuttavia, bisogna fare molta attenzione quando si usano le funzioni di aggregazione. Poiché l'output sarà aggregato, sarà molto più difficile vedere l'errore dall'output, a meno che non si conoscano a memoria tutti i risultati dell'aggregazione dei dati, cosa molto improbabile. In questo caso non si avrebbe bisogno di SQL, no?

Questo è particolarmente vero quando si usa COUNT(). Come si è visto in uno dei nostri esempi, COUNT(*) può dare risultati diversi da COUNT(column_name). È estremamente importante sapere che cosa si vuole ottenere e come funziona COUNT().

Ricordate: COUNT(column_name) ignora i valori di NULL, mentre COUNT(*) non lo fa!

Pertanto, quando si lavora con le funzioni aggregate, è bene testare la query con INNER JOIN - se se ne ha la possibilità - e vedere se restituisce risultati diversi. In caso contrario, non ha importanza quale join utilizzare; in caso contrario, tornate alla definizione del problema e verificate quale logica di join si adatta meglio alla vostra logica aziendale.

Giunzioni SINISTRE a cascata

Si osserva quando si uniscono più di due tabelle. Se si sceglie di LEFT JOIN le prime due tabelle, di solito è necessario LEFT JOIN tutte le altre tabelle.

Lo si è visto nell'ultimo esempio, quando abbiamo usato due LEFT JOINs. Anche se avessimo usato prima una LEFT JOIN e poi una INNER JOIN, non avremmo ottenuto l'elenco di tutti gli artisti e gli altri dati. Includendo INNER JOIN in questa catena di join si otterrà lo stesso risultato dell'utilizzo di INNER JOIN al posto di ogni LEFT JOIN.

Questo non è sempre il caso. Ma è abbastanza sicuro seguire questa regola empirica: se serve una LEFT JOIN, servono tutte le LEFT JOIN.

Volete fare più pratica con le JOIN SINISTRE?

Questo articolo vi ha dato un assaggio di cosa sia una LEFT JOIN e di come gestire le sue occasionali astuzie. Abbiamo anche fatto pratica con la scrittura del codice per familiarizzare con la sintassi di LEFT JOIN.

Per padroneggiare tutte le sfumature di LEFT OUTER JOIN in SQL, vi consigliamo di fare più pratica. Tre esempi non sono sufficienti! Per approfondire ciò che avete imparato qui, vi consigliamo il nostro SQL JOINs corso. Oltre alle basi teoriche, avrete anche modo di fare molta pratica con scenari reali.

Ecco alcune idee aggiuntive su come esercitarsi su SQL JOINs. Anche le domande delle interviste di SQL JOINs sono una risorsa preziosa per esercitarsi sui materiali. Buon apprendimento!