SQL OUTER JOIN

Un join SQL è un'o­pe­ra­zio­ne di in­ter­ro­ga­zio­ne che collega più tabelle di un database re­la­zio­na­le e re­sti­tui­sce i loro dati (tuple) filtrati in base a una con­di­zio­ne di selezione definita dal­l'u­ten­te.

Il tipo di join più comune del modello di database re­la­zio­na­le è l'inner join SQL. In pratica, gli utenti uti­liz­za­no 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 cor­ri­spon­den­te record di dati del­l'al­tra tabella. I record di dati per i quali il sistema di gestione dei database (DBMS) non riesce a trovare una cor­ri­spon­den­za nel­l'al­tra tabella vengono nascosti.

Una outer join SQL, invece, re­sti­tui­sce non solo i record di dati di entrambe le tabelle che sod­di­sfa­no la con­di­zio­ne di selezione (ad esempio l'u­gua­glian­za dei valori di due colonne), ma anche tutte le altre tuple dell'una o del­l'al­tra tabella.

Ogni outer join è rea­liz­za­ta come left outer join, right outer join o full outer join.

Il­lu­stria­mo la fun­zio­na­li­tà delle outer join uti­liz­zan­do le tabelle del database "col­la­bo­ra­to­ri" e "auto".

Tabella: col­la­bo­ra­to­ri

La tabella "col­la­bo­ra­to­ri" comprende il nome e il cognome dei di­pen­den­ti di un'a­zien­da fittizia e l'ID dell'auto aziendale assegnata (auto_id). La chiave primaria della tabella è un ID di­pen­den­te coerente (c_id). Al col­la­bo­ra­to­re con ID 5 (Marco Verdi) non è stata ancora assegnata un'auto aziendale. La cella della colonna cor­ri­spon­den­te contiene quindi un valore zero.

Tabella: auto

Le in­for­ma­zio­ni sul parco veicoli della società sono state riportate nella tabella "auto": la marca dell'auto aziendale, il modello, la targa, l'anno di co­stru­zio­ne 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 "col­la­bo­ra­to­ri". Questo ci permette di collegare entrambe le tabelle tramite una colonna comune.

In una left outer join la tabella sul lato sinistro dell’operatore join viene con­si­de­ra­ta la tabella dominante. Nell’algebra re­la­zio­na­le, le left outer join sono annotate con il seguente operatore: ⟕.

Per collegare le tabelle "col­la­bo­ra­to­ri" e "auto" in una left outer join è possibile ricorrere alla seguente ope­ra­zio­ne:

col­la­bo­ra­to­ri ⟕ _{auto_id=auto_id}auto

L’in­te­ra­zio­ne con DBMS è nel lin­guag­gio di database SQL. La formula descritta sopra cor­ri­spon­de alla seguente di­chia­ra­zio­ne SQL:

La tabella "col­la­bo­ra­to­ri" si trova sul lato sinistro del­l'o­pe­ra­to­re join, la tabella "auto" sul lato destro. Definiamo col­la­bo­ra­to­ri.auto_id = auto.auto_id come con­di­zio­ne 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 sod­di­sfa­no la con­di­zio­ne di join. Solo i record di dati della tabella "auto" sono inclusi nei risultati del join che con­ten­go­no un valore nella colonna auto_id per il quale il DBMS trova un valore cor­ri­spon­den­te anche nella tabella "col­la­bo­ra­to­ri".

I valori mancanti nella tabella dei risultati vengono re­sti­tui­ti come valori zero.

Come risultato della left outer join otteniamo la seguente tabella.

Tabella: left outer join tra le tabelle "col­la­bo­ra­to­ri" e "auto"

La tabella dei risultati ha due pe­cu­lia­ri­tà:

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 "col­la­bo­ra­to­ri" per una chiave primaria con il valore 4. La con­di­zio­ne di selezione non è sod­di­sfat­ta. Il record di dati deriva dalla tabella di output destra ed è quindi nascosto.

La chiave esterna auto_id della tabella di output "col­la­bo­ra­to­ri" contiene un valore zero per il record di dati del di­pen­den­te Marco Verdi. Pertanto, nella tabella "auto" non si trova nessuna chiave primaria cor­ri­spon­den­te. Anche in questo caso la con­di­zio­ne di selezione non è sod­di­sfat­ta. 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.

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 del­l'o­pe­ra­to­re join e le tuple della tabella sinistra che sod­di­sfa­no la con­di­zio­ne di join. Come operatore viene uti­liz­za­to il seguente simbolo: ⟖.

Ri­par­tia­mo dalle tabelle di output "col­la­bo­ra­to­ri" e "auto" e definiamo la stessa con­di­zio­ne di selezione per la right join come nel­l'e­sem­pio per la left join.

Algebra re­la­zio­na­le:

col­la­bo­ra­to­ri ⟖ _{auto_id=auto_id}auto

Di­chia­ra­zio­ne SQL:

Tabella: right outer join tra le tabelle "col­la­bo­ra­to­ri" e "auto"

Il record di dati per il di­pen­den­te Marco Verdi non è contenuto nella tabella dei risultati. La ragione di ciò è che auto_id del record di dati cor­ri­spon­de 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 cor­ri­spon­den­te a 4, a cui non è stata assegnata nessuna tupla della tabella "col­la­bo­ra­to­ri". I valori mancanti vengono emessi anche qui come valori zero.

Una full outer join è una com­bi­na­zio­ne di left outer join e right outer join. Per il fun­zio­na­men­to nel­l'al­ge­bra re­la­zio­na­le è stato definito il seguente operatore: ⟗.

Il­lu­stria­mo anche la full join nelle tabelle di output "col­la­bo­ra­to­ri" e "auto" e partiamo dalla stessa con­di­zio­ne di selezione di prima.

Algebra re­la­zio­na­le:

col­la­bo­ra­to­ri ⟗ _{auto_id=auto_id}auto

Di­cha­ra­zio­ne SQL:

Il risultato cor­ri­spon­de alla tabella seguente.

Tabella: full outer join per le tabelle "col­la­bo­ra­to­ri" e "auto"

La full join collega i record di dati delle tabelle di output in base alla con­di­zio­ne 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 sod­di­sfa­no la con­di­zio­ne di selezione.

Anche i valori mancanti nella full join sono con­tras­se­gna­ti con la dicitura “ZERO”.

ZERO

Come le inner join, anche le outer join possono essere rea­liz­za­te come natural join. Gli operatori cor­ri­spon­den­ti sono:

O:

Le natural outer join collegano le tabelle uti­liz­zan­do colonne con lo stesso nome. Le colonne se­le­zio­na­te possono essere definite espli­ci­ta­men­te uti­liz­zan­do la parola chiave USING:

In al­ter­na­ti­va, è possibile ricorrere a una notazione ab­bre­via­ta in cui il DMBS cerca au­to­ma­ti­ca­men­te le colonne con lo stesso nome e collega le tabelle elencate uti­liz­zan­do queste:

Ri­fe­ren­do­si alle tabelle di esempio in­tro­dot­te sopra, entrambe le di­chia­ra­zio­ni SQL giungono allo stesso risultato.

La natural left join combina le colonne col­la­bo­ra­to­ri.auto_id e auto.auto_id per formare la colonna comune auto_id.

Di solito le outer join portano a gruppi di tabelle con valori zero. Questo è utile, ad esempio, se si vogliono mettere in evidenza esat­ta­men­te questi valori. Nel nostro esempio, la Porsche Boxster non è ancora stata assegnata a nessun di­pen­den­te, cosa che non può essere desunta dalla tabella "auto". Anche la tabella "col­la­bo­ra­to­ri" mostra solo che Marco Verdi non utilizza ancora un'auto aziendale, e non quale sia quella di­spo­ni­bi­le. Dopo una full join tra entrambe le tabelle, è chiaro a colpo d'occhio che Marco potrebbe guidare la Porsche in futuro.