Inner Join Vs Outer Join: Preparati ad esplorare le differenze esatte tra Inner Join e Outer Join

Prima di esplorare le differenze tra Inner Join Vs Outer Join, vediamo prima cos’è una SQL JOIN?

Una clausola di join è usata per combinare i record o per manipolare i record di due o più tabelle attraverso una condizione di join. La condizione di join indica come le colonne di ogni tabella sono abbinate tra loro.

Join si basa su una colonna correlata tra queste tabelle. Un esempio più comune è l’unione tra due tabelle attraverso la colonna chiave primaria e la colonna chiave esterna.

Supponiamo di avere una tabella che contiene lo stipendio dei dipendenti e un’altra tabella che contiene i dettagli dei dipendenti.

In questo caso, ci sarà una colonna comune come ID dipendente che unirà queste due tabelle. Questa colonna ID dipendente sarebbe la chiave primaria delle tabelle dei dettagli del dipendente e la chiave esterna nella tabella dello stipendio del dipendente.

È molto importante avere una chiave comune tra le due entità. Puoi pensare a una tabella come a un’entità e alla chiave come a un collegamento comune tra le due tabelle che viene utilizzato per l’operazione di join.

Fondamentalmente, ci sono due tipi di join in SQL, cioè Inner Join e Outer Join. Outer Join è ulteriormente suddiviso in tre tipi: Left Outer Join, Right Outer Join e Full Outer Join.

In questo articolo, vedremo la differenza tra Inner Join e Outer Join in dettaglio. Terremo le Cross Join e le Unequal Joins fuori dallo scopo di questo articolo.

Che cos’è l’Inner Join?

Un Inner Join restituisce solo le righe che hanno valori corrispondenti in entrambe le tabelle (stiamo considerando che la join è fatta tra le due tabelle).

Che cos’è l’Outer Join?

L’Outer Join include le righe corrispondenti e alcune delle righe non corrispondenti tra le due tabelle. Un Outer Join differisce fondamentalmente dall’Inner Join per come gestisce la condizione di falsa corrispondenza.

Ci sono 3 tipi di Outer Join:

• Left Outer Join: Restituisce tutte le righe della tabella LEFT e i record corrispondenti tra le due tabelle.
• Right Outer Join: Restituisce tutte le righe della tabella DESTRA e i record corrispondenti tra le due tabelle.
• Full Outer Join: Combina il risultato del Left Outer Join e del Right Outer Join.

Differenza tra Inner e Outer Join

Come mostrato nel diagramma sopra, ci sono due entità cioè la tabella 1 e la tabella 2 ed entrambe le tabelle condividono alcuni dati.

Un Inner Join restituirà l’area comune tra queste tabelle (l’area verde ombreggiata nel diagramma sopra) cioè tutti i record che sono comuni tra la tabella 1 e la tabella 2.

Un Left Outer Join restituirà tutte le righe della tabella 1 e solo quelle righe della tabella 2 che sono comuni anche alla tabella 1. Un Right Outer Join farà esattamente il contrario. Darà tutti i record della tabella 2 e solo i record corrispondenti della tabella 1.

Inoltre, una Full Outer Join ci darà tutti i record della tabella 1 e della tabella 2.

Partiamo con un esempio per rendere tutto più chiaro.

Supponiamo di avere due tabelle: EmpDetails e EmpSalary.

Tabella EmpDetails:

Tabella degli stipendi:

Facciamo un Inner Join su queste due tabelle e osserviamo il risultato:

Query:

SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalaryFROM EmpDetails INNER JOIN EmpSalaryON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Risultato:

Nel set di risultati sopra, potete vedere che l’Inner Join ha restituito i primi 6 record che erano presenti sia in EmpDetails che in EmpSalary avendo una chiave corrispondente, cioè EmployeeID.cioè EmployeeID. Quindi, se A e B sono due entità, l’Inner Join restituirà l’insieme di risultati che sarà uguale a ‘Records in A e B’, basato sulla chiave corrispondente.

Vediamo ora cosa farà un Left Outer Join.

Query:

SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalaryFROM EmpDetails LEFT JOIN EmpSalaryON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Risultato:

Nella serie di risultati sopra, si può vedere che il left outer join ha restituito tutti i 10 record dalla tabella LEFT i.e. tabella EmpDetails e poiché i primi 6 record sono corrispondenti, ha restituito lo stipendio del dipendente per questi record corrispondenti.

Come il resto dei record non hanno una chiave corrispondente nella tabella DESTRA, cioè la tabella EmpSalary, ha restituito NULL corrispondente a quelli. Dato che Lily, Sita, Farah e Jerry non hanno un ID dipendente corrispondente nella tabella EmpSalary, il loro stipendio appare come NULL nell’insieme dei risultati.

Quindi, se A e B sono due entità, allora left outer join restituirà l’insieme dei risultati che sarà uguale a ‘Records in A NOT B’, basato sulla chiave corrispondente.

Ora osserviamo cosa fa il Right Outer Join.

Query:

SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalaryFROM EmpDetails RIGHT join EmpSalaryON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Risultato:

Nell’insieme dei risultati sopra, potete vedere che il Right Outer Join ha fatto proprio l’opposto del Left Join. Ha restituito tutti gli stipendi dalla tabella di destra, cioè EmpSalary.

Ma, siccome Rose, Sakshi e Jack non hanno un ID dipendente corrispondente nella tabella di sinistra, cioè EmpDetails, abbiamo ottenuto il loro ID dipendente e EmployeeName come NULL dalla tabella di sinistra.

Quindi, se A e B sono due entità, allora la join esterna destra restituirà l’insieme di risultati che sarà uguale a ‘Records in B NOT A’, basato sulla chiave corrispondente.

Vediamo anche quale sarà l’insieme di risultati se stiamo facendo un’operazione di selezione su tutte le colonne in entrambe le tabelle.

Query:

SELECT *FROM EmpDetails RIGHT JOIN EmpSalaryON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Risultato:

Ora, passiamo al Full Join.

Un full outer join viene fatto quando vogliamo tutti i dati di entrambe le tabelle indipendentemente dal fatto che ci sia una corrispondenza o meno. Quindi, se voglio tutti i dipendenti anche se non trovo una chiave corrispondente, eseguirò una query come mostrato di seguito.

Query:

SELECT *FROM EmpDetails FULL JOIN EmpSalaryON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Risultato:

Si può vedere nella serie di risultati di cui sopra che i primi sei record sono corrispondenti in entrambe le tabelle, abbiamo ottenuto tutti i dati senza alcun NULL. I successivi quattro record esistono nella tabella di sinistra ma non in quella di destra, quindi i dati corrispondenti nella tabella di destra sono NULL.

Gli ultimi tre record esistono nella tabella di destra e non in quella di sinistra, quindi abbiamo NULL nei dati corrispondenti della tabella di sinistra. Quindi, se A e B sono due entità, il join esterno completo restituirà l’insieme di risultati che sarà uguale a ‘Records in A AND B’, indipendentemente dalla chiave corrispondente.

Teoricamente, è una combinazione di Left Join e Right Join.

Performance

Confrontiamo un Inner Join con un Left Outer Join nel server SQL. Parlando della velocità di funzionamento, una left outer JOIN non è ovviamente più veloce di una inner join.

Come da definizione, una outer join, sia essa di sinistra o di destra, deve eseguire tutto il lavoro di una inner join insieme al lavoro aggiuntivo null-estensione dei risultati. Ci si aspetta che una giunzione esterna restituisca un numero maggiore di record che aumenta ulteriormente il suo tempo di esecuzione totale solo a causa dell’insieme di risultati più grande.

Quindi, una giunzione esterna è più lenta di una giunzione interna.

Inoltre, ci possono essere alcune situazioni specifiche in cui il Left join sarà più veloce di un Inner join, ma non possiamo continuare a sostituirli l’uno con l’altro poiché un left outer join non è funzionalmente equivalente a un inner join.

Discutiamo un caso in cui il Left Join potrebbe essere più veloce dell’Inner Join. Se le tabelle coinvolte nell’operazione di join sono troppo piccole, diciamo che hanno meno di 10 record e le tabelle non possiedono indici sufficienti per coprire la query, in questo caso, il Left Join è generalmente più veloce dell’Inner Join.

Creiamo le due tabelle seguenti e facciamo una INNER JOIN e una LEFT OUTER JOIN tra di loro come esempio:

CREATE TABLE #Table1(ID int NOT NULL PRIMARY KEY,Name varchar(50) NOT NULL)INSERT #Table1 (ID, Name) VALUES (1, 'A')INSERT #Table1 (ID, Name) VALUES (2, 'B')INSERT #Table1 (ID, Name) VALUES (3, 'C')INSERT #Table1 (ID, Name) VALUES (4, 'D')INSERT #Table1 (ID, Name) VALUES (5, 'E')CREATE TABLE #Table2(ID int NOT NULL PRIMARY KEY,Name varchar(50) NOT NULL)INSERT #Table2 (ID, Name) VALUES (1, 'A')INSERT #Table2 (ID, Name) VALUES (2, 'B')INSERT #Table2 (ID, Name) VALUES (3, 'C')INSERT #Table2 (ID, Name) VALUES (4, 'D')INSERT #Table2 (ID, Name) VALUES (5, 'E')SELECT *FROM #Table1 t1INNER JOIN #Table2 t2ON t2.Name = t1.Name

SELECT * FROM (SELECT 38 AS bah) AS foo JOIN (SELECT 35 AS bah) AS barON (55=55);

Come potete vedere sopra, entrambe le query hanno restituito lo stesso set di risultati. In questo caso, se guardate il piano di esecuzione di entrambe le query, troverete che l’inner join è costato di più dell’outer join. Questo perché, per un join interno, il server SQL fa un hash match mentre fa i loop annidati per il join sinistro.

Un hash match è normalmente più veloce dei loop annidati. Ma, in questo caso, dato che il numero di righe è così piccolo e non c’è nessun indice da usare (dato che stiamo facendo un join sulla colonna nome), l’operazione hash si è rivelata una query di join interno più costosa.

Tuttavia, se si cambia la chiave di corrispondenza nella query di join da Nome a ID e se ci sono un gran numero di righe nella tabella, allora si scoprirà che il join interno sarà più veloce del join esterno sinistro.

MS Access Inner and Outer Join

Quando si usano più fonti di dati nella query di MS Access, allora si applicano le JOIN per controllare i record che si vogliono vedere, a seconda di come le fonti di dati sono collegate tra loro.

In una join interna, solo quelli correlati da entrambe le tabelle vengono combinati in un unico set di risultati. Questo è un join predefinito in Access e anche il più usato. Se applichi un join ma non specifichi esplicitamente che tipo di join è, allora Access assume che sia un join interno.

Nei join esterni, tutti i dati correlati da entrambe le tabelle sono combinati correttamente, più tutte le righe rimanenti da una tabella. Nelle giunzioni esterne complete, tutti i dati vengono combinati quando possibile.

Left Join vs Left Outer Join

In SQL server, la parola chiave outer è opzionale quando si applica la left outer join. Quindi, non fa alcuna differenza se si scrive ‘LEFT OUTER JOIN’ o ‘LEFT JOIN’, poiché entrambi danno lo stesso risultato.

A LEFT JOIN B è una sintassi equivalente a A LEFT OUTER JOIN B.

Di seguito la lista delle sintassi equivalenti nel server SQL:

Left Outer Join vs Right Outer Join

Abbiamo già visto questa differenza in questo articolo. Puoi fare riferimento alle query Left Outer Join e Right Outer Join e al set di risultati per vedere la differenza.

La differenza principale tra Left Join e Right Join sta nell’inclusione delle righe non abbinate. La join esterna sinistra include le righe non abbinate della tabella che si trova a sinistra della clausola di join, mentre la join esterna destra include le righe non abbinate della tabella che si trova a destra della clausola di join.

La gente si chiede quale sia meglio usare, cioè la join sinistra o la join destra? Fondamentalmente, sono lo stesso tipo di operazioni tranne che con i loro argomenti invertiti. Quindi, quando chiedi quale join usare, in realtà stai chiedendo se scrivere a<b o b>a. È solo una questione di preferenze.

Generalmente, le persone preferiscono usare Left join nelle loro query SQL. Suggerirei di rimanere coerenti nel modo in cui si scrive la query per evitare qualsiasi confusione nell’interpretazione della query.

Abbiamo visto tutto su Inner join e tutti i tipi di Outer join finora. Riassumiamo velocemente la differenza tra Inner Join e Outer Join.

Differenza tra Inner Join e Outer Join in formato tabellare

Inner and Outer Join vs Union

A volte, confondiamo Join e Union e questa è anche una delle domande più frequenti nelle interviste SQL. Abbiamo già visto la differenza tra inner join e outer join. Ora, vediamo come una JOIN è diversa da una UNION.

UNION mette una linea di query dopo l’altra, mentre join crea un prodotto cartesiano e lo suddivide. Quindi, UNION e JOIN sono operazioni completamente diverse.

Eseguiamo le due query seguenti in MySQL e vediamo il loro risultato.

UNIONE Query:

SELECT 28 AS bahUNIONSELECT 35 AS bah;

Risultato:

JOIN Query:

SELECT * FROM(SELECT 38 AS bah) AS fooJOIN(SELECT 35 AS bah) AS barON (55=55);

Risultato:

Un’operazione UNION mette il risultato di due o più query in un unico insieme di risultati. Questo insieme di risultati contiene tutti i record che vengono restituiti attraverso tutte le query coinvolte nell’UNIONE. Quindi, fondamentalmente, una UNION combina i due set di risultati insieme.

Un’operazione di join recupera i dati da due o più tabelle in base alle relazioni logiche tra queste tabelle, cioè in base alla condizione di join. Nella join query, i dati di una tabella sono usati per selezionare i record di un’altra tabella. Ti permette di collegare dati simili che sono presenti su tabelle diverse.

Per capirlo molto semplicemente, si può dire che una UNION combina righe da due tabelle mentre un join combina colonne da due o più tabelle. Quindi, entrambi sono utilizzati per combinare i dati da n tabelle, ma la differenza sta nel modo in cui i dati vengono combinati.

Di seguito sono le rappresentazioni pittoriche di UNION e JOIN.

Questa è una rappresentazione grafica di un’operazione di join che mostra che ogni record nel set di risultati contiene colonne da entrambe le tabelle, cioè la tabella A e la tabella B. Questo risultato viene restituito in base alla condizione di join applicata nella query.

Un join è generalmente il risultato della denormalizzazione (l’opposto della normalizzazione) e utilizza la chiave esterna di una tabella per cercare i valori delle colonne utilizzando la chiave primaria di un’altra tabella.

Questa è una rappresentazione grafica di un’operazione UNION che mostra che ogni record nel risultato è una riga di una delle due tabelle. Così, il risultato dell’unione ha combinato le righe della tabella A e della tabella B.

Altra lettura =>> MySQL UNION spiegato con esempi

Conclusione

In questo articolo, abbiamo visto le principali differenze tra Inner Join e Outer Join in SQL. Abbiamo anche visto la classificazione di un Outer Join cioè Left join, Right join e Full join. Abbiamo visto come ognuno di questi tipi di join funziona e come variano l’uno dall’altro.

Abbiamo anche fatto un confronto delle prestazioni tra questi tipi di join. Abbiamo anche discusso come un join è diverso da un’unione.

Leggi anche =>> MySQL Join Types

Spero che questo articolo ti abbia aiutato a chiarire i tuoi dubbi sulle differenze tra i vari tipi di join. Siamo sicuri che questo vi farà decidere quale tipo di join scegliere in base al set di risultati desiderato.