Join SQL - Query su più tabelle di dati
Join SQL (in italiano: "giunzione") è un'operazione nei database relazionali che permette di interrogare più tabelle di database. I join combinano i dati memorizzati in diverse tabelle e li restituiscono in forma filtrata in una tabella dei risultati.
Il principio del join SQL si basa sull'omonima operazione algebrica relazionale, cioè una combinazione derivata da un prodotto cartesiano e da una selezione. L'utente determina quali dati delle tabelle di output vengono trasferiti alla tabella dei risultati selezionando un tipo di join e definendo una condizione di selezione.
Questo articolo sui join SQL presuppone la conoscenza di determinati concetti legati ai database relazionali, in particolare per quanto riguarda le relazioni, le tuple, gli attributi o le chiavi. Trovate un'introduzione nel nostro articolo di base sui database relazionali.
Vi presentiamo le basi matematiche dei join SQL, confrontiamo diversi tipi di join e vi mostriamo come implementarli nelle query di database tramite SQL utilizzando esempi pratici.
Come funzionano i join SQL?
Il principio di base dei join SQL può essere illustrato derivando il funzionamento del database dalle sue sottoperazioni. Ogni join si basa sulle seguenti operazioni di algebra relazionale:
- Prodotto cartesiano
- Selezione
Il prodotto cartesiano
Il prodotto cartesiano (o unione incrociata) è un'operazione di teoria degli insiemi in cui due o più insiemi sono collegati tra loro. Nel modello di database relazionale, il prodotto cartesiano viene utilizzato per collegare i set di tuple sotto forma di tabelle. Il risultato di questa operazione è di nuovo un insieme di tuple ordinate, dove ogni tupla è composta da un elemento di ogni set iniziale.
Nell'algebra relazionale, il segno di moltiplicazione (×) viene utilizzato come operatore per il prodotto cartesiano.
Di seguito un esempio:
il prodotto cartesiano A × B dei due set A = {x, y, z} e B = {1, 2, 3} è:
A × B = {(x,1), (x,2), (x,3), (y,1), (y,2), (y,3), (z,1), (z,2), (z,3)}
Il calcolo si può chiarire grazie al grafico seguente:
La sequenza di formazione delle coppie deve essere rispettata. Ad esempio, il prodotto cartesiano A × B non corrisponde alla stessa quantità del prodotto cartesiano B × A.
A × B = {(x,1), (x,2), (x,3), (y,1), (y,2), (y,3), (z,1), (z,2), (z,3)}
B × A = {(1,x), (1,y), (1,z), (2,x), (2,y), (2,z), (3,x), (3,y), (3,z)}
Nella terminologia SQL, un'operazione in cui il prodotto cartesiano è formato da due tabelle di database è denominata cross join. In pratica, le cross join sono raramente utilizzate a causa del risultato non filtrato.
La selezione
La selezione è un'operazione di algebra relazionale che consente di selezionare specifiche tuple da un set iniziale e di inviarle come risultato. Un'espressione di confronto può essere usata per determinare quali tuple sono incluse nel set di risultati. Il risultato della selezione è quindi un insieme di tuple che soddisfano la condizione di selezione definita nell'espressione di confronto. Come operatore viene utilizzata la lettera greca Sigma (σ). L'operazione è descritta di seguito:
σF (R)
Il segnaposto "F" corrisponde all'espressione di confronto, una formula di predicati logici che definiscono la condizione di selezione. R sta per il set di dati da selezionare. In alternativa, è possibile utilizzare la notazione lineare R[F].
Per la formulazione delle condizioni di selezione sono disponibili i soliti operatori di confronto: ad esempio, uguale (=), maggiore (>) o minore (<).
Spieghiamo la selezione utilizzando un esempio che abbiamo già introdotto nel testo di base del modello di database relazionale. La tabella seguente riporta i dati personali fittizi che un'azienda avrebbe potuto inserire per i propri dipendenti. Per ogni collaboratore, vengono specificati l'id personale (c_id), generalità (cognome, nome), il codice fiscale (cf), l'indirizzo (via, CAP, città) e l'auto aziendale assegnata (auto_id).
Tabella: collaboratori
| c_id | cognome | nome | cf | via | n. | CAP | città | auto_id |
|---|---|---|---|---|---|---|---|---|
| 1 | Bianchi | Maria | BNCMRA55F59F500S | Via Roma | 1 | 11111 | Roma | 3 |
| 2 | Rossi | Giada | RSSGDI97G54B243L | Via Matteotti | 2 | 22222 | Genova | 1 |
| 3 | Padovan | Gianluca | PDVGLC55M21L424T | Via F.lli Cervi | 3 | 33333 | Gela | 1 |
| 4 | Terragna | Elisabetta | TRRLBT32R56C352Z | Via Quasimodo | 4 | 44444 | Novara | 2 |
Se vogliamo selezionare la tabella "collaboratori" in modo tale che vengano visualizzati solo i dipendenti che guidano l'auto aziendale con auto_id 1, possiamo procedere come segue:
σauto_id=1(collaboratori)
Recuperiamo semplicemente le tuple dove il valore nella colonna auto_id è uguale a 1.
Il risultato corrisponde alla seguente tabella.
Tabella: collaboratori (selezionata)
| c_id | nome | cognome | cf | via | n. | CAP | città | auto_id |
|---|---|---|---|---|---|---|---|---|
| 2 | Rossi | Giada | RSSGDI97G54B243L | Via Matteotti | 2 | 22222 | Genova | 1 |
| 3 | Padovan | Gianluca | PDVGLC55M21L424T | Via F.lli Cervi | 3 | 33333 | Gela | 1 |
Nel linguaggio SQL del database, le condizioni di selezione sono definite utilizzando il comando WHERE.
SELECT * FROM collaboratori WHERE collaboratori.auto_id = 1;Se una tupla soddisfa la condizione auto_id = 1, i valori di tutte le colonne devono essere visualizzati per questa tupla.
L'asterisco (*) rappresenta tutte le colonne di una tabella nella sintassi SQL.
Combinazione di prodotto cartesiano e selezione
Tutti i comuni tipi di join combinano il prodotto cartesiano a una condizione di selezione. Al fine di spiegare tale operazione di database, per motivi di chiarezza, riduciamo la tabella "collaboratori" a quattro colonne. Inoltre, introduciamo la tabella "auto", in cui sono memorizzate informazioni dettagliate sul parco veicoli dell'azienda.
Entrambe le tabelle sono collegate tra loro da una relazione a chiave esterna. La chiave primaria della tabella "auto" (auto_id) funge da chiave esterna nella tabella "collaboratori".
Tabella: collaboratori
| c_id | cognome | nome | auto_id |
|---|---|---|---|
| 1 | Bianchi | Maria | 3 |
| 2 | Rossi | Giada | 1 |
| 3 | Padovan | Gianluca | 1 |
| 4 | Terragna | Elisabetta | 2 |
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 |
L'esternalizzazione di informazioni nelle diverse tabelle di database è un concetto di base del modello di database relazionale. I vantaggi di una struttura simile di database così come la sua implementazione sono discussi in un ulteriore articolo sulla normalizzazione dei database relazionali.
Se si desidera unire le due tabelle e selezionare contemporaneamente le relative tuple, è possibile combinare le operazioni del database precedentemente introdotte:
σauto_id=auto_id(collaboratori × auto)
Per primo viene formato il prodotto cartesiano collaboratori × auto. Il risultato (intermedio) è una cross join, cioè una tabella dei risultati in cui ogni tupla della tabella "collaboratori" è combinata con ogni tupla della tabella "auto".
Tabella: prodotto cartesiano "collaboratori" × "auto"
| c_id | cognome | nome | collaboratori.auto_id | auto.auto_id | marca | modello | targa | anno di costruzione | revisione |
| 1 | Bianchi | Maria | 3 | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 1 | Bianchi | Maria | 3 | 2 | Opel | Astra | B PO 654 | 2010 | 12.08.2019 |
| 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 |
| 2 | Rossi | Giada | 1 | 2 | Opel | Astra | B PO 654 | 2010 | 12.08.2019 |
| 2 | Rossi | Giada | 1 | 3 | BMW | X6 | B MW 780 | 2017 | 01.09.2018 |
| 3 | Padovan | Gianluca | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 3 | Padovan | Gianluca | 1 | 2 | Opel | Astra | B PO 654 | 2010 | 12.08.2019 |
| 3 | Padovan | Gianluca | 1 | 3 | BMW | X6 | B MW 780 | 2017 | 01.09.2018 |
| 4 | Terragna | Elisabetta | 2 | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 4 | Terragna | Elisabetta | 2 | 2 | Opel | Astra | B PO 654 | 2010 | 12.08.2019 |
| 4 | Terragna | Elisabetta | 2 | 3 | BMW | X6 | B MW 780 | 2017 | 01.09.2018 |
Poi vengono selezionate solo quelle tuple dove il record auto_id della tabella "auto" corrisponde al record auto_id della tabella "collaboratori". La condizione di selezione è che la chiave esterna della tabella "collaboratori" corrisponda alla chiave primaria della tabella "auto".
Il risultato (finale) è una tabella che unisce entrambe le tabelle di output senza ridondanze.
Tabella: join tra "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 |
I join combinano entrambe le operazioni in un'unica operazione, in quanto combinazione del prodotto cartesiano e della successiva selezione. Il simbolo "bow tie" (⋈) viene utilizzato come operatore.
Si applica pertanto:
σauto_id=auto_id(collaboratori × auto) := collaboratori⋈auto_id=auto_idauto
L'operazione σauto_id=auto_id(collaboratori × auto) corrisponde a un join tra le tabelle "collaboratori" e "auto" con il prerequisito auto_id=auto_id.
Trasferita alla sintassi SQL, l'operazione qui sopra corrisponderebbe alla seguente dichiarazione:
SELECT * FROM collaboratori INNER JOIN auto ON collaboratori.auto_id = auto.auto_id;L'inner join è uno dei più importanti join utilizzati per le query al database. A volte, tuttavia, per ottenere il risultato desiderato sono necessari tipi speciali di join.
Tipi di join SQL
Nel modello di database relazionale, vengono utilizzati diversi tipi di join SQL che consentono di eseguire query utilizzando un insieme di tabelle di database. Il prerequisito per fare ciò è che le tabelle selezionate siano collegate tra loro utilizzando relazioni a chiave esterna.
I tipi di join più importanti sono i seguenti:
- inner join: una inner join è una forma filtrata della cross join in cui solo le tuple di entrambe le tabelle di output che soddisfano la condizione di selezione definita dall'utente vengono unite insieme nel risultato impostato.
- outer join: una outer join è un'estensione dell'inner join. L'insieme dei risultati di una outer join contiene le tuple di entrambe le tabelle di output che soddisfano la condizione di selezione definita dall'utente, così come tutte le tuple rimanenti della prima tabella, della seconda tabella o di entrambe le tabelle. Le outer join sono implementate di conseguenza come left outer join, right outer join o full outer join.
Trovate una descrizione dettagliata delle inner join e delle outer join negli articoli di approfondimento ai tipi di join.
Le differenze tra le inner join e le diverse varianti dell'outer join possono essere illustrate da diagrammi. Il grafico seguente illustra i tipi di join presentati:
Indipendentemente dalla distinzione tra inner join e outer join, i join SQL possono anche essere classificati nei seguenti tipi di join:
- EQUI JOIN
- NON EQUI JOIN
- SELF JOIN
Le inner join e le outer join possono essere implementate come equi join e non equi join. Tutti gli esempi di join presentati finora sono equi join. Le equi join sono caratterizzate dal fatto che permettono solo il segno uguale come operatore di confronto (=). La condizione di selezione di una equi join è quindi sempre l'uguaglianza dei valori delle colonne. In linea di principio, i join (così come la selezione nell'algebra relazionale) non sono però limitati all'uguaglianza delle colonne. Possibili operatori di confronto sono:
| Operatori di confronto | Significato |
|---|---|
| = | uguale |
| < | minore |
| > | maggiore |
| ≤ | minore o uguale |
| ≥ | maggiore o uguale |
| <> | disuguale |
| != | disuguale |
A partire da SQL 92 il linguaggio del database offre una notazione abbreviata per equi join con la parola chiave USING. Tuttavia, ciò presuppone che le colonne interessate abbiano lo stesso nome, anche se non è sempre così.
L'esempio seguente mostra due diverse dichiarazioni SQL che portano allo stesso risultato. Nella prima dichiarazione, l'operazione join è esplicitamente definita utilizzando la parola chiave ON. Per la seconda dichiarazione, usiamo la notazione abbreviata con la parola chiave USING.
SELECT * FROM collaboratori INNER JOIN auto ON collaboratori.auto_id = auto.auto_id;SELECT * FROM collaboratori INNER JOIN auto USING auto_id;Le non equi join, invece, escludono le operazioni basate sull'uguaglianza delle colonne. Sono quindi consentite tutte le operazioni di confronto ad eccezione del segno uguale (=).
Visto che le relazioni nei database relazionali vengono definiti solitamente tramite l'uguaglianza delle chiavi primarie ed esterne, le non equi join nel modello di database relazionali hanno un'importanza secondaria. Non in ultimo perché queste, come le cross join, portano spesso a set di risultati.
Infine, una self join è una forma speciale di join SQL in cui una tabella di database è collegata a se stessa. In linea di principio, qualsiasi tipo di join può essere eseguito come self join.
Se due tabelle sono collegate da colonne con lo stesso nome, si parla di natural join. Una natural join è implementata di default come inner join utilizzando la parola chiave omonima. Tuttavia, le natural join non sono fissate a questo tipo di join. È possibile anche una natural left outer join o una natural right outer join.
Poiché le natural join sono collegate utilizzando colonne con lo stesso nome, i rispettivi valori non vengono emessi due volte nel set di risultati, ma vengono uniti per formare una colonna comune. Trovate esempi di natural join negli articoli di approfondimento su inner join e outer join.