15 OUTER JOIN (join esterne)

Pubblicato il di

Nelle INNER JOINS abbiamo visto, un determinato predicato dede essere soddisfatto, che sia uguaglianza (equi join) o qualsiasi altro theta join. Le righe che non soddisfano il criterio saranno scartate.

Tornando al nostro database Olimpiadi potrei avere una nazione che non ha ancora atleti inseriti, oppure qualche atleta che non è stata inserita la nazione. Effettuando una select * from atleti, nazioni con una join tra le 2 tabelle potrei perdere dei risultati. Mi piacerebbe magari sapere quali sono gli atleti che non hanno il campo nazione compilato. Ecco un modo di ottenere il risultato:

select distinct nomeNazione
from nazioni
where (select count(*) from atleti where idNazione=idNazione)=0;

è un comando abbastanza contorto e richiede diversa pratica SQL per scriverlo in modo naturale. In questo caso (ma anche in altri ancor più complessi) ci aiutano le outlet join, e meglio:

quando nel risultato si vogliono preservare tutte le righe dell’una e/o dell’altra tabella e parleremo di right outer join quando dovremmo vedere, almeno una volta, tutte le righe della tabella che sta a destra nella query , mentre left outer join quelle a sinistra. Quando invece vorremo vedere tutte le righe di tutte le tabelle (collegate nella query) si parlerà di full outer join.

select nomeNazione
from nazioni
LEFT OUTER JOIN atleti 
ON nazione = idNazione;

La tabella che sta a sinistra (nazione) viene visualizzata (mentre atleti che sta destra no). In questo caso vedrei solo tutte le nazioni, quindi aggiungiamo *

select *
from nazioni
LEFT OUTER JOIN atleti 
ON nazione = idNazione;

ora mi vengono presentati tutti i risultati anche degli atleti con nazionalità non segnata, cosa che con join interne non potrei vedere e in corrispondenza dei records atleti mi restituirebbe NULL. Potrei applicare una condizione per estrarre i null

select *
from nazioni LEFT OUTER JOIN atleti 
ON nazione = idNazione
where idNazione IS NULL ;

Da notare che imporre la condizione sulla chiave esterna mette al sicure da stranezze; ad esempio il cognome potrebbe essere null anche se l’atleta ha la nazione collegata.

Le inner join non mostrerebbero proprio questi risultati, di conseguenza non potrei applicare alcun filtro is null su qualcosa che non viene restituito.

Vediamo con la right outer join 

select * 
from nazioni RIGHT OUTER JOIN atleti 
ON nazione = idNazione
where idNazione IS NULL ;

Vengono restituiti gli atleti il cui campo nazione non è stato compilato, quindi il valore è null

full outer join

select * 
from nazioni FULL OUTER JOIN atleti 
ON nazione = idNazione
where idNazione IS NULL ;

Purtroppo questa sintassi non è ad oggi supportata da MySQL, per ottenerla bisogna unire i risultati di left e right

select * from nazioni 
LEFT OUTER JOIN atleti 
ON nazione = idNazione 

UNION

select * from nazioni 
RIGHT OUTER JOIN atleti 
ON nazione = idNazione ;