Join (SQL)

La equi-join, noto come "l'unica operazione ammissibile", ha un particolare tipo di comparatore, detto theta join, che utilizza come metodo di verifica, solamente l'uguaglianza matematica come regola di confronto. Usare altri operatori di confronto (come ad esempio <) squalifica la join come equi-join. La query riportata di seguito utilizza una equi-join:

SELECT *
FROM   Impiegati 
       INNER JOIN Dipartimenti 
          ON Impiegati.ID_dipartimento = Dipartimenti.ID_dipartimento

SQL fornisce una scorciatoia per definire le equi-joins, attraverso la parola riservata USING

SELECT *
FROM   Impiegati 
       INNER JOIN Dipartimenti 
          USING (ID_dipartimento)

La keyword USING è supportata da Microsoft SQL Server Management Studio, MySQL, Oracle, PostgreSQL, SQLite, e DB2/400.

Una NATURAL JOIN offre ulteriori specializzazioni di equi-join. Solitamente la JOIN confronta colonne di tabelle diverse che hanno lo stesso nome. La NATURAL JOIN fa proprio questo.

Nell'esempio che segue viene riportata la notazione NATURAL JOIN equivalente alla prima INNER JOIN definita:

SELECT *
FROM   Impiegati 
       NATURAL JOIN Dipartimenti

Una CROSS JOIN, cartesian join o product fornisce le basi (le infrastrutture informatiche) attraverso cui tutti i tipi di INNER JOIN operano. Il risultato di una CROSS JOIN è il prodotto cartesiano di tutte le righe delle tabelle che concorrono alla query di join. È come dire che stiamo facendo una INNER JOIN senza impostare la regola di confronto o in cui la regola di confronto ritorna sempre vero.

Date le due tabelle di partenza ${\displaystyle A}$ e ${\displaystyle B}$, la cross join si scrive ${\displaystyle A}$ × ${\displaystyle B}$.

Esempio di CROSS JOIN esplicito:

SELECT *
FROM   Impiegati CROSS JOIN Dipartimenti

Altro esempio implicito di CROSS JOIN:

SELECT *
FROM   Impiegati, Dipartimenti;

Si sottolinea come esista un ordine di esecuzione tra le condizioni specificate nella WHERE e quelle presenti nella ON.

Specificatamente le clausole presenti nella ON sono valutate anteriormente all'esecuzione della JOIN mentre le clausole nella where sono valutate successivamente all'esecuzione della JOIN.

Il risultato di una query LEFT OUTER JOIN (o semplicemente LEFT JOIN) per le tabelle ${\displaystyle A}$ e ${\displaystyle B}$ contiene sempre tutti i record della tabella di sinistra (left) ${\displaystyle A}$, mentre vengono estratti dalla tabella di destra (right) ${\displaystyle B}$ solamente le righe che trovano corrispondenza nella regola di confronto della join. Questo significa che se la clausola ON trova zero righe in ${\displaystyle B}$, la JOIN mostrerà una riga risultante con valore NULL in tutte le colonne corrispondenti al risultato per le colonne di ${\displaystyle B}$.

Esempio di una left outer join:

SELECT *  
FROM   Impiegati  LEFT OUTER JOIN Dipartimenti  
          ON Impiegati.ID_dipartimento = Dipartimenti.ID_dipartimento

Una RIGHT OUTER JOIN (o RIGHT JOIN) semplicemente ricalca il funzionamento della LEFT OUTER JOIN, ma invertendo l'ordine delle tabelle interessate.

Il risultato di una query RIGHT OUTER JOIN per le tabelle ${\displaystyle A}$ e ${\displaystyle B}$ contiene sempre tutti i record della tabella di destra (right) ${\displaystyle B}$, mentre vengono estratti dalla tabella di sinistra (left) ${\displaystyle A}$ solamente le righe che trovano corrispondenza nella regola di confronto della JOIN. Questo significa che se la clausola ON trova zero righe in ${\displaystyle A}$, la JOIN mostrerà una riga risultante con valore NULL in tutte le colonne corrispondenti al risultato per le colonne di ${\displaystyle A}$.

Esempio di una RIGHT OUTER JOIN:

SELECT * 
FROM   Impiegati RIGHT OUTER JOIN Dipartimenti 
          ON Impiegati.ID_dipartimento = Dipartimenti.ID_dipartimento

In pratica sono utilizzate maggiormente le query di LEFT OUTER JOIN rispetto a quelle di RIGHT OUTER JOIN, ma possono verificarsi rari casi in cui in query molto complesse ci sia la necessità di utilizzare contemporaneamente il criterio di LEFT OUTER JOIN e di RIGHT OUTER JOIN.

Lo stesso risultato della precedente RIGHT OUTER JOIN si può ripetere usando il tipo LEFT OUTER JOIN:

SELECT * 
FROM   Dipartimenti LEFT OUTER JOIN Impiegati
          ON Impiegati.ID_dipartimento = Dipartimenti.ID_dipartimento

Una FULL OUTER JOIN combina i risultati delle due tabelle ${\displaystyle A}$ e ${\displaystyle B}$ tenendo conto di tutte le righe delle tabelle, anche di quelle che non hanno corrispondenza tra di loro.

Il risultato di una query FULL OUTER JOIN per le tabelle ${\displaystyle A}$ e ${\displaystyle B}$ contiene sempre tutti i record della tabella di sinistra (left) ${\displaystyle A}$, estraendo dalla tabella di destra (right) ${\displaystyle B}$ solamente le righe che trovano corrispondenza nella regola di confronto della join; inoltre verranno estratti tutti i record della tabella di sinistra (left) ${\displaystyle A}$ che non trovano corrispondenza nella tabella di destra (right) ${\displaystyle B}$ impostando a NULL i valori di tutte le colonne della tabella ${\displaystyle B}$ e tutti i record della tabella di destra (right) ${\displaystyle B}$ che non trovano corrispondenza nella tabella di sinistra (left) ${\displaystyle A}$ impostando a NULL i valori di tutte le colonne della tabella ${\displaystyle A}$.

Esempio di una FULL OUTER JOIN:

SELECT *  
FROM   Impiegati 
       FULL OUTER JOIN Dipartimenti 
          ON Impiegati.ID_dipartimento = Dipartimenti.ID_dipartimento

Alcuni database (come per esempio MySQL) non supportano direttamente questa funzionalità, ma la si può emulare attraverso la combinazione di LEFT OUTER JOIN e RIGHT OUTER JOIN per mezzo della parola riservata UNION.

La creazione di una query di FULL OUTER JOIN si realizzerà come segue:

SELECT *
FROM   Impiegati 
       LEFT JOIN Dipartimenti 
          ON Impiegati.ID_dipartimento = Dipartimenti.ID_dipartimento
UNION
SELECT *
FROM   Impiegati
       RIGHT JOIN Dipartimenti
          ON Impiegati.ID_dipartimento = Dipartimenti.ID_dipartimento
WHERE  Impiegati.ID_dipartimento IS NULL

Allo stesso modo senza utilizzare nemmeno la query di RIGHT OUTER JOIN:

SELECT Impiegati.*, Dipartimenti.*
FROM   Impiegati 
       LEFT JOIN Dipartimenti 
          ON Impiegati.ID_dipartimento = Dipartimenti.ID_dipartimento
UNION
SELECT Impiegati.*, Dipartimenti.*
FROM   Dipartimenti
       LEFT JOIN Impiegati
          ON Impiegati.ID_dipartimento = Dipartimenti.ID_dipartimento
WHERE  Impiegati.ID_dipartimento IS NULL