SQL OUTER JOIN
La redazione di IONOS10 mins
Un join SQL è un'operazione di interrogazione che collega più tabelle di un database relazionale e restituisce i loro dati (tuple) filtrati in base a una condizione di selezione definita dall'utente.
Il tipo di join più comune del modello di database relazionale è l'inner join SQL. In pratica, gli utenti utilizzano le inner join, ad esempio, se due tabelle di database devono essere collegate con le stesse colonne. Ogni record di dati di una tabella è combinato con un corrispondente record di dati dell'altra tabella. I record di dati per i quali il sistema di gestione dei database (DBMS) non riesce a trovare una corrispondenza nell'altra tabella vengono nascosti.
Una outer join SQL, invece, restituisce non solo i record di dati di entrambe le tabelle che soddisfano la condizione di selezione (ad esempio l'uguaglianza dei valori di due colonne), ma anche tutte le altre tuple dell'una o dell'altra tabella.
Più di un semplice nome.
Registra il tuo dominio con IONOS e approfitta di tutte le nostre funzionalità.
- SSL Wildcard
- Supporto 24/7
Riferendosi alla direzione di lettura della sintassi SQL si parla di tabella sinistra e destra. Le rispettive operazioni sono pertanto denominate left outer join e right outer join. Se, oltre ai record di dati che soddisfano la condizione di selezione, si desidera anche visualizzare tutti i record di dati della tabella sinistra e destra nelle query del database, si parla di una full outer join.
Il principio dei diversi tipi di join si può chiarire bene servendosi di una rappresentazione grafica:
Sottocategorie dell’outer join
Ogni outer join è realizzata come left outer join, right outer join o full outer join.
La parola chiave OUTER è opzionale nella sintassi SQL. Solitamente gli utenti usano la notazione abbreviata left join, right join e full join
Illustriamo la funzionalità delle outer join utilizzando le tabelle del database "collaboratori" e "auto".
Tabella: collaboratori
| c_id | cognome | nome | auto_id |
|---|---|---|---|
| 1 | Bianchi | Maria | 3 |
| 2 | Rossi | Giada | 1 |
| 3 | Padovan | Gianluca | 1 |
| 4 | Terragna | Elisabetta | 2 |
| 5 | Verdi | Marco | ZERO |
La tabella "collaboratori" comprende il nome e il cognome dei dipendenti di un'azienda fittizia e l'ID dell'auto aziendale assegnata (auto_id). La chiave primaria della tabella è un ID dipendente coerente (c_id). Al collaboratore con ID 5 (Marco Verdi) non è stata ancora assegnata un'auto aziendale. La cella della colonna corrispondente contiene quindi un valore zero.
Il valore zero rappresenta l’assenza di un valore. Non corrisponde al valore numerico 0.
Tabella: auto
| auto_id | marca | modello | targa | anno di costruzione | revisione |
|---|---|---|---|---|---|
| 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 2 | Opel | Astra | B PO 654 | 2010 | 12.08.2019 |
| 3 | BMW | X6 | B MW 780 | 2017 | 01.09.2018 |
| 4 | Porsche | Boxster | B AA 123 | 2018 | 23.12.2020 |
Le informazioni sul parco veicoli della società sono state riportate nella tabella "auto": la marca dell'auto aziendale, il modello, la targa, l'anno di costruzione e la data della prossima revisione (revisione). A ogni auto aziendale viene assegnato un ID coerente (auto_id), che funge da chiave primaria della tabella.
Entrambe le tabelle sono collegate da una relazione a chiave esterna. La chiave primaria della tabella "auto" (auto_id) è stata integrata come chiave esterna nella tabella "collaboratori". Questo ci permette di collegare entrambe le tabelle tramite una colonna comune.
Mentre le chiavi primarie valide non possono comprendere valori zero, gli stessi valori nelle chiavi esterne non danneggiano l’integrità di un record di dati.
Left outer join SQL
In una left outer join la tabella sul lato sinistro dell’operatore join viene considerata la tabella dominante. Nell’algebra relazionale, le left outer join sono annotate con il seguente operatore: ⟕.
Per collegare le tabelle "collaboratori" e "auto" in una left outer join è possibile ricorrere alla seguente operazione:
collaboratori ⟕ auto_id=auto_idauto
L’interazione con DBMS è nel linguaggio di database SQL. La formula descritta sopra corrisponde alla seguente dichiarazione SQL:
SELECT * FROM collaboratori LEFT JOIN auto ON collaboratori.auto_id = auto.auto_id;La tabella "collaboratori" si trova sul lato sinistro dell'operatore join, la tabella "auto" sul lato destro. Definiamo collaboratori.auto_id = auto.auto_id come condizione di selezione. L'insieme dei risultati di una left outer join include tutti i record di dati della tabella di sinistra così come quei record di dati della tabella di destra che soddisfano la condizione di join. Solo i record di dati della tabella "auto" sono inclusi nei risultati del join che contengono un valore nella colonna auto_id per il quale il DBMS trova un valore corrispondente anche nella tabella "collaboratori".
I valori mancanti nella tabella dei risultati vengono restituiti come valori zero.
A differenza delle inner join, l'ordine delle tabelle nella dichiarazione SQL deve essere rispettato per le outer join. In una left join vengono restituiti completamente tutti i record di dati della tabella sinistra dell'operatore join, mentre in una right join vengono emessi tutti i record di dati della tabella destra dell'operatore join.
Come risultato della left outer join otteniamo la seguente tabella.
Tabella: left outer join tra le tabelle "collaboratori" e "auto"
| c_id | cognome | nome | collaboratori.auto_id | auto.auto_id | marca | modello | targa | anno di costruzione | revisione |
| 1 | Bianchi | Maria | 3 | 3 | BMW | X6 | B MW 780 | 2017 | 01.09.2018 |
| 2 | Rossi | Giada | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 3 | Padovan | Gianluca | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 4 | Terragna | Elisabetta | 2 | 2 | Opel | Astra | B PO 654 | 2010 | 12.08.2019 |
| 5 | Verdi | Marco | ZERO | ZERO | ZERO | ZERO | ZERO | ZERO | ZERO |
La tabella dei risultati ha due peculiarità:
il record di dati della tabella "auto" con auto_id 4 (Porsche Boxster) non appare nella tabella dei risultati. La ragione di ciò è che non esiste una chiave esterna adeguata nella tabella "collaboratori" per una chiave primaria con il valore 4. La condizione di selezione non è soddisfatta. Il record di dati deriva dalla tabella di output destra ed è quindi nascosto.
La chiave esterna auto_id della tabella di output "collaboratori" contiene un valore zero per il record di dati del dipendente Marco Verdi. Pertanto, nella tabella "auto" non si trova nessuna chiave primaria corrispondente. Anche in questo caso la condizione di selezione non è soddisfatta. Tuttavia, poiché il record di dati deriva dalla tabella di output sinistra, questo è ancora incluso nella tabella dei risultati in una left join. I valori mancanti nella tupla della tabella dei risultati sono impostati su ZERO.
Right outer join SQL
La right outer join segue lo stesso principio della left outer join, ma la tabella dominante qui non è quella di sinistra, ma quella di destra.
Il risultato di una right outer join comprende tutte le tuple della tabella sul lato destro dell'operatore join e le tuple della tabella sinistra che soddisfano la condizione di join. Come operatore viene utilizzato il seguente simbolo: ⟖.
Ripartiamo dalle tabelle di output "collaboratori" e "auto" e definiamo la stessa condizione di selezione per la right join come nell'esempio per la left join.
Algebra relazionale:
collaboratori ⟖ auto_id=auto_idauto
Dichiarazione SQL:
SELECT * FROM collaboratori RIGHT JOIN auto ON collaboratori.auto_id = auto.auto_id;Tabella: right outer join tra le tabelle "collaboratori" e "auto"
| c_id | cognome | nome | collaboratori.auto_id | auto.auto_id | marca | modello | targa | anno di costruzione | revisione |
| 1 | Bianchi | Maria | 3 | 3 | BMW | X6 | B MW 780 | 2017 | 01.09.2018 |
| 2 | Rossi | Giada | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 3 | Padovan | Gianluca | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 4 | Terragna | Elisabetta | 2 | 2 | Opel | Astra | B PO 654 | 2010 | 12.08.2019 |
| ZERO | ZERO | ZERO | 4 | 4 | Porsche | Boxter | B AA 123 | 2018 | 23.12.2020 |
Il record di dati per il dipendente Marco Verdi non è contenuto nella tabella dei risultati. La ragione di ciò è che auto_id del record di dati corrisponde al valore ZERO e quindi non può essere assegnato a nessun record di dati nella tabella di destra.
Come risultato della right join otteniamo tutti i record della tabella "auto", anche il record con valore auto_id corrispondente a 4, a cui non è stata assegnata nessuna tupla della tabella "collaboratori". I valori mancanti vengono emessi anche qui come valori zero.
Full outer join SQL
Una full outer join è una combinazione di left outer join e right outer join. Per il funzionamento nell'algebra relazionale è stato definito il seguente operatore: ⟗.
Illustriamo anche la full join nelle tabelle di output "collaboratori" e "auto" e partiamo dalla stessa condizione di selezione di prima.
Algebra relazionale:
collaboratori ⟗ auto_id=auto_idauto
Dicharazione SQL:
SELECT * FROM collaboratori FULL JOIN auto ON collaboratori.auto_id = auto.auto_id;Il risultato corrisponde alla tabella seguente.
Tabella: full outer join per le tabelle "collaboratori" e "auto"
| c_id | nome | cognome | collaboratori.auto_id | auto.auto_id | marca | modello | targa | anno di costruzione | revisione |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Bianchi | Maria | 3 | 3 | BMW | X6 | B MW 780 | 2017 | 01.09.2018 |
| 2 | Rossi | Giada | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 3 | Padovan | Gianluca | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 4 | Terragna | Elisabetta | 2 | 2 | Opel | Astra | B PO 654 | 2010 | 12.08.2019 |
| 5 | Verdi | Marco | ZERO | ZERO | ZERO | ZERO | ZERO | ZERO | ZERO |
| ZERO | ZERO | 4 | Porsche | Boxster | B AA 123 | 2018 | 23.12.2020 |
La full join collega i record di dati delle tabelle di output in base alla condizione di selezione, ma non elenca solo i record di dati collegati nella tabella dei risultati, ma anche i record di dati di entrambe le tabelle che non soddisfano la condizione di selezione.
Anche i valori mancanti nella full join sono contrassegnati con la dicitura “ZERO”.
Le full outer join hanno poca importanza nella prassi e non sono supportate da sistemi di gestione di database leader di mercato come MySQL e MariaDB
- ZERO
Natural outer join
Come le inner join, anche le outer join possono essere realizzate come natural join. Gli operatori corrispondenti sono:
LEFT/RIGHT JOIN ... USINGO:
NATURAL LEFT/RIGHT JOINLe natural outer join collegano le tabelle utilizzando colonne con lo stesso nome. Le colonne selezionate possono essere definite esplicitamente utilizzando la parola chiave USING:
SELECT * FROM collaboratori LEFT JOIN auto USING(auto_id);In alternativa, è possibile ricorrere a una notazione abbreviata in cui il DMBS cerca automaticamente le colonne con lo stesso nome e collega le tabelle elencate utilizzando queste:
SELECT * FROM collaboratori NATURAL LEFT JOIN reparti;Riferendosi alle tabelle di esempio introdotte sopra, entrambe le dichiarazioni SQL giungono allo stesso risultato.
| c_id | cognome | nome | auto_id | marca | modello | targa | anno di costruzione | revisione |
|---|---|---|---|---|---|---|---|---|
| 1 | Bianchi | Maria | 3 | BMW | X6 | B MW 780 | 2017 | 01.09.2018 |
| 2 | Rossi | Giada | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 3 | Padovan | Gianluca | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 4 | Terragna | Elisabetta | 2 | Opel | Astra | B PO 654 | 2010 | 12.08.2019 |
| 5 | Verdi | Marco | ZERO | ZERO | ZERO | ZERO | ZERO | ZERO |
La natural left join combina le colonne collaboratori.auto_id e auto.auto_id per formare la colonna comune auto_id.
Outer join nella prassi
Di solito le outer join portano a gruppi di tabelle con valori zero. Questo è utile, ad esempio, se si vogliono mettere in evidenza esattamente questi valori. Nel nostro esempio, la Porsche Boxster non è ancora stata assegnata a nessun dipendente, cosa che non può essere desunta dalla tabella "auto". Anche la tabella "collaboratori" mostra solo che Marco Verdi non utilizza ancora un'auto aziendale, e non quale sia quella disponibile. Dopo una full join tra entrambe le tabelle, è chiaro a colpo d'occhio che Marco potrebbe guidare la Porsche in futuro.
