SQL (Structured Query Language)

Un corso completo sul linguaggio SQL (Structured Query Language): definizioni, esempi ed esercizi

Join - combinazioni di tabelle in SQL

Capitolo 5

Join – Combinazione di tabelle

Questo capitolo tratta un importante tipo di operazione tra le tabelle: il Join.

Il vocabolo join significa unione e nel caso di SQL sta ad indicare unione tra tabelle. Esistono vari tipi di join, ma tutti derivano o possono essere ricondotti a vari operatori dell’algebra insiemistica. L’importanza principale del join risiede nella possibilità che ci offre per correlare e visualizzare dati appartenenti a tabelle diverse o alla medesima tabella, logicamente correlati tra di loro. I semplici dati, da noi uniti, possono assumere la forma di complesse informazioni così come noi li vogliamo.

CROSS JOIN

Per comprendere a pieno l’operazione CROSS JOIN (unione incrociata) bisogna aver ben chiaro il concetto di prodotto cartesiano:

………………. Prodotto cartesiano……………….

Dati due insiemi D1 e D2 si chiama prodotto cartesiano di D1 e D2, l’insieme delle coppie ordinate (v1, v2), tali che v1 è un elemento di D1 e v2 un elemento di D2.

Vediamo cosa significa quanto affermato con un esempio:

insiemi

A x B = {(2, r), (2, s), (2, d), (2, 4), (f, r), (f, s), (f, d), (f, 4), (r, r), (r, s), (r, d), (r, 4)}

Come possiamo vedere il prodotto cartesiano fra i due insiemi è dato da tutti gli elementi di A combinati con ogni elemento di B. Nella rappresentazione delle varie coppie dobbiamo rispettare l’ordine di apparizione degli elementi, in quanto l’appartenenza dell’elemento all’insieme è individuabile proprio dalla suo ordine di apparizione. Nell’esempio abbiamo usato solo due insiemi ma il prodotto cartesiano è applicabile anche a più di due insiemi.

……………………………………………………………………………………………………………………………

Ora considerando che le tabelle non sono altro che insiemi i cui elementi sono le righe ecco che possiamo individuare l’operazione di CROSS JOIN in quella di prodotto cartesiano appartenente alle teorie degli insiemi. Dunque il prodotto cartesiano tra due o più tabelle si traduce in una istruzione chiamata CROSS JOIN. Il CROSS JOIN si ottiene in maniera molto semplice elencando dopo la FROM le tabelle che devono essere coinvolte. Vediamo un esempio di CROSS JOIN:

Per lo scopo usiamo due tabelle: TAB1 e TAB2

TAB1 TAB2
COLONTAB1 COLONTAB2
RIG1 TAB1 RIG1 TAB2
RIG2 TAB1 RIG2 TAB2
RIG3 TAB1 RIG3 TAB2
RIG4 TAB1
RIG5 TAB1

SELECT *

FROM TAB1, TAB2;

COLONTAB1 COLONTAB2
RIG1 TAB1 RIG1 TAB2
RIG2 TAB1 RIG1 TAB2
RIG3 TAB1 RIG1 TAB2
RIG4 TAB1 RIG1 TAB2
RIG5 TAB1 RIG1 TAB2
RIG1 TAB1 RIG2 TAB2
RIG2 TAB1 RIG2 TAB2
RIG3 TAB1 RIG2 TAB2
RIG4 TAB1 RIG2 TAB2
RIG5 TAB1 RIG2 TAB2
RIG1 TAB1 RIG3 TAB2
RIG2 TAB1 RIG3 TAB2
RIG3 TAB1 RIG3 TAB2
RIG4 TAB1 RIG3 TAB2
RIG5 TAB1 RIG3 TAB2

Questo è il risultato che si ottiene dal CROSS JOIN delle tabelle TAB1 e TAB2, come si può vedere non è altro che un prodotto cartesiano. Chiaramente avremmo potuto usare anche più di due tabelle.

Il CROSS JOIN non è particolarmente utile e viene usato raramente, ma se in una CROSS JOIN si utilizza la clausola WHERE potremmo ottenere join molto più interessanti.

NATURAL JOIN

Il NATURAL JOIN è un tipo di operazione che ci permette di correlare due o più tabelle sulla base di valori uguali in attributi contenenti lo stesso tipo di dati.

Vediamo un esempio:

Per lo scopo usiamo due tabelle: PERSONE e AUTO. La tabella AUTO fa riferimento alla persona proprietaria dell’auto attraverso il campo PROPRIETARIO in cui sono riportati i numeri di patente. Lo stesso tipo di dato è presente nella tabella PERSONE nel campo PATENTE.

PERSONE AUTO
NOME PATENTE TARGA PROPRIETARIO
ANTONIO 123 VT AC73949 156
GIOVANNI 156 ROMA J1003 172
ARTURO 172 MI GH3434 300
NA G666223 301

Vogliamo ottenere un join delle righe delle due tabelle la dove i valori dei campi PROPRIETARIO e PATENTE sono uguali .

SELECT *

FROM PERSONE, AUTO

WHERE PATENTE = PROPRIETARIO;

NOME PATENTE TARGA PROPRIETARIO
GIOVANNI 156 VT AC73949 156
ARTURO 172 ROMA J1003 172

Nel caso le due tabelle originarie avessero avuto i campi interessati al join (PATENTE e PROPRIETARIO) con lo stesso nome in entrambe, avremmo dovuto specificare dopo la WHERE prima del nome del campo il nome della tabella a cui facevamo riferimento. Facciamo un esempio considerando le tabelle PERSONE e AUTO così modificate

PERSONE AUTO
NOME NUM_PATENTE TARGA NUM_PATENTE
ANTONIO 123 VT AC73949 156
GIOVANNI 156 ROMA J1003 172
ARTURO 172 MI GH3434 300
NA G666223 301

In questo caso siamo obbligati a specificare l’appartenenza dei campi alle tabelle:

SELECT *

FROM PERSONE, AUTO

WHERE PERSONE.NUM_PATENTE = AUTO.NUM_PATENTE;

NOME NUM_PATENTE TARGA NUM_PATENTE
GIOVANNI 156 VT AC73949 156
ARTURO 172 ROMA J1003 172

INNER JOIN

È un tipo di join in cui le righe delle tabelle vengono combinate solo se i campi collegati con join soddisfano una determinata condizione.

Vediamo un esempio:

Vogliamo ottenere un join delle righe delle due tabelle PERSONE e AUTO, la dove i valori dei campi PROPRIETARIO e PATENTE sono uguali e dove il valore del campo NOME è uguale ad ‘ARTURO’.

SELECT *

FROM PERSONE, AUTO

WHERE PATENTE = PROPRIETARIO

AND NOME = ‘ARTURO’;

NOME PATENTE TARGA PROPRIETARIO
ARTURO 172 ROMA J1003 172

Esistono anche, delle parole chiavi specifiche per eseguire l’operazione di INNER JOIN.

SELECT *

FROM PERSONE INNER JOIN AUTO

ON

(PERSONE.PATENTE = AUTO.PROPRIETARIO AND NOME = ‘ARTURO’);

NOME PATENTE TARGA PROPRIETAR
ARTURO 172 ROMA J1003 172

Il risultato che otteniamo è lo stesso, ma la sintassi usata non è accettata da SQL Plus 8.0 Oracle (prodotto usato per testare la maggior parte degli esempi di questo corso), infatti questa query e quelle successive in cui appaiono parole chiave specifiche, sono state testate utilizzato Microsoft Access.

Usando le parole chiave specifiche dobbiamo indicare, per alcuni DBMS come nel caso di Access, a quale tabella appartengono i campi. C’è inoltre da far notare che quello che segue la clausola ON va messo tra parentesi se è presente più di una condizione.

Vediamo altri esempi:

Usando le parole chiave specifiche, vogliamo ottenere lo stesso JOIN che abbiamo usato come esempio nel paragrafo del NATURAL JOIN:

SELECT *

FROM PERSONE INNER JOIN AUTO

ON PERSONE.PATENTE = AUTO.PROPRIETARIO;

NOME PATENTE TARGA PROPRIETAR
GIOVANNI 156 VT AC73949 156
ARTURO 172 ROMA J1003 172

Possiamo renderci conto che questo tipo di join è simile al natural join; infatti il natural join è un particolare caso di inner join.

OUTER JOIN

Con l’OUTER JOIN è possibile estrapolare anche quei dati, appartenenti ad una delle tabelle, che non verrebbero estrapolati nei tipi di join visti fino a questo momento. Infatti OUTER significa esterno; dati esterni al normale tipo di join.

Dobbiamo specificare quale è la tabella di cui vogliamo estrapolare i dati anche se non soddisfano la condizione di join, questo lo facciamo indicando con LEFT o RIGHT se la tabella in questione è quella che appare a destra o a sinistra del comando JOIN.

SELECT . . .

FROM tabella1 [LEFTRIGHT]JOIN tabella2

ON tabella1.campox condizione tabella2.campoy

Vediamo alcuni esempi:

Vogliamo visualizzare nel nostro JOIN oltre a tutte le persone che possiedono un auto e l’auto appartenuta, anche quelle che non possiedono nessuna auto:

SELECT *

FROM PERSONE LEFT JOIN AUTO

ON PERSONE.PATENTE = AUTO.PROPRIETARIO;

NOME PATENTE TARGA PROPRIETAR
ANTONIO 123
GIOVANNI 156 VT AC73949 156
ARTURO 172 ROMA J1003 172

SELF JOIN

Il SELF JOIN ci consente di unire una tabella con se stessa. La sintassi è simile a quella della query vista nel paragrafo che trattava il CROSS JOIN. Vediamo un esempio usando la tabella TAB2:

TAB2

COLONTAB2

——————-

RIG1 TAB2

RIG2 TAB2

RIG3 TAB2

SELECT R1.COLONTAB2, R2.COLONTAB2

FROM TAB2 R1, TAB2 R2;

R1.COLONTAB2 R2.COLONTAB2
RIG1 TAB2 RIG1 TAB2
RIG2 TAB2 RIG1 TAB2
RIG3 TAB2 RIG1 TAB2
RIG1 TAB2 RIG2 TAB2
RIG2 TAB2 RIG2 TAB2
RIG3 TAB2 RIG2 TAB2
RIG1 TAB2 RIG3 TAB2
RIG2 TAB2 RIG3 TAB2
RIG3 TAB2 RIG3 TAB2

Come possiamo vedere dalla query otteniamo un prodotto cartesiano. Dopo la parola chiave SELECT siamo costretti a simulare l’esistenza di due tabelle mente invece ne abbiamo una soltanto. Dopo la parola chiave FROM faremo riferimento al nome delle colonne e alla tabella a cui appartengono:

SELECT nomeTabellaInesitente1.nomeColonna, nomeTabellaInesitente2.nomeColonna

FROM nomeColonna nomeTabellaInesitente1, nomeColonna nomeTabellaInesitente2

[WHERE condizioni];

Questo tipo di select non è particolarmente utile a meno che non si utilizzi la clausola where per unire dati che soddisfano una particolare condizione. Vediamo un esempio:

PERSONE
ID_PERSONA NOME CONIUGE SESSO
1 ANTONIO 12 M
12 SILVIA 1 F
2 GIULIO 7 M
3 MARIA F
6 ROBERTA 9 F
7 ANTONELLA 2 F
9 ARTURO 6 M

SELECT T1.NOME, T2.NOME

FROM PERSONE T1, PERSONE T2

WHERE T1.ID_PERSONA = T2.CONIUGE;

T1.NOME T2.NOME
ANTONELLA GIULIO
ANTONIO SILVIA
ARTURO ROBERTA
GIULIO ANTONELLA
ROBERTA ARTURO
SILVIA ANTONIO

La select funziona ma però ci accorgiamo che le coppie vengono ripetute e questo non è esattamente quello che volevamo. Come possiamo risolvere il problema? Lascio ai lettore il compito di trovare la soluzione.

JOIN tra tabelle usando operatori di confronto che non siano il segno di uguale (=)

Possiamo usare dopo la clausola where anche tipi di operatori che non siano l’operatore di uguale (=). In casi del genere il join che si ottiene è abbastanza inusuale, ma può accadere che si renda necessario eseguire query di questo tipo.

La sintassi, dunque sarà uguale a quella di tutti i join visti in precedenza con la sola differenza che la dove appare il segno di uguale (=) possiamo usare, al suo posto, qualsiasi altro operatore di confronto.

JOIN su più di due tabelle

Come il prodotto cartesiano può essere eseguito su più di due insiemi, anche i vari tipi di join possono essere applicati a più di due tabelle. Fa eccezione il self join, ma che comunque può simulare l’esistenza anche di più di due tabelle; vediamo un esempio senza però visualizzare il risultato della query:

SELECT T1.NOME, T2.NOME, T3.NOME

FROM PERSONE T1, PERSONE T2, PERSONE T3

WHERE . . .

Un altro caso particolare è quello dell’outer join che per essere applicato a più di due tabelle ha bisogno di una sintassi particolare; vediamo uno schema generale e rimandiamo l’approfondimento di tale tipo di sintassi al capitolo seguente.

SELECT . . .

FROM tabella1 [LEFT RIGHT] JOIN ( specifiche di join

tra altre due o più tabelle)

ON . . .

I restanti tipi di join non presentano particolari sintassi o eccezioni ad essere applicati su più di due tabelle.

Vediamo ora un caso di join che si presenta frequentemente tra tre tabelle: chi conosce bene il modello relazionale sa che non è possibile ‘correlare’ due tabelle usando una relazione di tipo molti a molti. In questo caso si utilizza una terza tabella che contiene come chiavi esterne la chiave primaria della prima tabella e la chiave primaria della seconda tabella. Nell’esempio seguente abbiamo la tabella PERSONE correlata con la tabella CONTI-CORRENTI; il tipo di relazione è di tipo molti a molti. Infatti una persona può avere più conti correnti e un conto corrente può appartenere a più persone.

join

Se vogliamo visualizzare i dati così correlati usiamo la seguente query:

SELECT . . .

FROM PERSONE, UNIONE, CONTI-CORRENTI

WHERE PERSONE.ID-PERSONA = UNIONE.ID-PERSONA

AND UNIONE.ID-CONTI = CONTI-CORRENTI.ID-CONTI;

Alla select precedente nulla ci impedisce di aggiungere altre condizioni, magari per poter visualizzare soltanto i conti appartenenti al Sig. Rossi Antonio. Lascio al lettore il compito di aggiungere all’espressione l’ulteriore condizione.

ESERCIZI (capitolo 5)

Nella tabella ISCRITTI sono presenti gli associati ad un club, nella tabella FAMILIARI sono registrati gli appartenenti alla famiglia di ogni associato. Per alcuni esercizi faremo riferimento a queste due tabelle.

ISCRITTI
NOME MATRICOLA
GIOVANNI 1
ANTONIO 21
RICCARDO 9

 

FAMILIARI
NOME PARENTELA MATRICOLA_DEL_PARENTE_ISCRITTO ETA
GIULIA FIGLIA 21 5
MARIA MOGLIE 21 35
RUGERO FIGLIO 1 21

1) Dalle tabelle ISCRITTI e FAMILIARI estrapolare un’unica tabella in cui compaiono per ogni iscritto i

suoi familiari e il tipo di parentela. Gli iscritti senza familiari non dovranno apparire.

2) Dalle tabelle ISCRITTI e FAMILIARI estrapolare un’unica tabella in cui compaiono per ogni iscritto i

suoi familiari e il tipo di parentela. Gli iscritti senza familiari dovranno apparire.

3) Dalle tabelle ISCRITTI e FAMILIARI estrapolare un’unica tabella in cui compaiono per ogni iscritto i

suoi familiari e il tipo di parentela, solo se tali familiari hanno un’età inferiore ai 20 anni. Gli iscritti con

famigliari che non soddisfano la condizione non dovranno essere visualizzati.

4) La tabella MARCHE contiene dati riguardanti le industrie costruttrici di automobili; la tabella MODELLI contiene dati riguardanti i vari modelli di auto esistenti. I nomi dei campi in neretto rappresentano le chiavi primarie.

MARCHE MODELLI
COD_CASA NOME_AUTO
NOME_CASA COD_CASA
STATO TIPO
CITTA CILINDRATA

Scrivere una select che restituisca come risultato il codice della casa, il nome della casa, la città e il nome delle auto per le automobili di tipo sport che hanno una cilindrata compresa tra 1000 cc e 2000 cc, estremi inclusi.

5) Scrivere una select che restituisca come risultato, dalle tabelle dell’esercizio N° 4, il codice della casa e il nome della casa che produce più di due modelli di automobili di tipo sport. Utilizzare la ‘tecnica’ dell’unione fra tabelle.

6) Date due tabelle (TABELLA1 e TABELLA2) che contengono una colonna chiamata NUMERO, come fareste a trovare quei numeri che appartengono a entrambe le tabelle? Scrivere una query.

7) Date le tabelle STUDENTI(matricola, nome_esame, voto), MATRICOLA(matricola, cognome, nome)

scrivere una select che abbia come risultato il cognome e la matricola degli studenti che hanno sostenuto l’esame di informatica riportando una votazione compresa tra 23 e 28, oppure hanno sostenuto l’esame di informatica.

Soluzioni agli esercizi del Capitolo 5

Autore: Art Net

Condividi questo articolo su

12 Commenti

  1. Ho imparato alcune cose buone qui. Certamente merita il bookmarking per la rivisitazione.

    Mi chiedo quanti sforzi hai impostato per creare questo tipo di fantastico sito informativo.

    Rispondi al Commento
  2. guida molto interessante grazie se ci fossero anche le istruzioni per creare la tabella sarebbe completa secondo me

    Rispondi al Commento
  3. Grazie per la guida. Semplice, interessante ed utile.
    Vorremmo segnalare due cose. Un errore ed un dubbio.
    Errore:
    SELECT DIVISIONE, AVG(STIPENDIO)
    FROM DIPENDENTI
    HAVING AVG(STIPENDIO) > 2200000;

    manca la “group by DIVISIONE” prima della HAVING.

    Di conseguenza il dubbio sul punto 6.
    HAVING non è necessario che venga posta dopo la clausola GROUP BY.
    Sicuri?

    Rispondi al Commento
  4. Guida Molto utile, segnalo un errore nella query 9, capitolo 2, delle soluzioni:
    PrezzoIngrosso è moltiplicato * 1,5 ma si chiedeva un aumento del 15%, ciò significa moltiplicare * 1,15

    Rispondi al Commento
  5. Veramente complimenti, una guida dettagliata, semplice e adatta a tutti 🙂

    FortyZone

    Rispondi al Commento
  6. Davvero complimenti ragazzi. La prima guida che per chi parte da zero è manna dal cielo…PERFETTA!!

    Rispondi al Commento
  7. nel punto 8 la soluzione non dovrebbe essere con HAVING MIN(IMPORTO) > 400 000; cioè: SELECT BENEFICIARI FROM ASSEGNI GROUP BY BENEFICIARIO HAVING MIN(IMPORTO) > 400 000;

    Rispondi al Commento
  8. un ottimo Manuale unico errore (se vogliamo chiamarlo cosi) i tipi di dati Char(5) number(5,2) cosa sono e come si usano

    Rispondi al Commento
  9. Complimenti ottimo sito! fatto davvero bene. Grazie!

    Rispondi al Commento

Invia commento

Il tuo indirizzo email non sarà pubblicato.