il Join è l'operatore più interessante dell'algebra...
Transcript of il Join è l'operatore più interessante dell'algebra...
20/03/2006 1
Join� il Join è l'operatore più interessante
dell'algebra relazionale� permette di correlare dati in relazioni
diverse
20/03/2006 2
Prove scritte in un concorsopubblico
� I compiti sono anonimi e ad ognuno èassociata una busta chiusa con il nomedel candidato
� Ciascun compito e la relativa bustavengono contrassegnati con uno stessonumero
20/03/2006 3
1 252 133 274 28
1 Mario Rossi2 Nicola Russo3 Mario Bianchi4 Remo Neri
25Mario Rossi13Nicola Russo27Mario Bianchi28Remo Neri
20/03/2006 4
1 252 133 274 28
Numero Voto1 Mario Rossi2 Nicola Russo3 Mario Bianchi4 Remo Neri
Numero Candidato
25Mario Rossi13Nicola Russo27Mario Bianchi28Remo Neri
VotoCandidato1234
Numero
20/03/2006 5
Join naturale
� operatore binario (generalizzabile)� produce un risultato
� sull'unione degli attributi degli operandi� con ennuple costruite ciascuna a partire da
una ennupla di ognuno degli operandi
20/03/2006 6
Join, sintassi e semantica
� R1(X1), R2(X2)� R1 JOIN R2 è una relazione su X1X2
{ t su X1X2 | esistono t1∈R1e t2∈R2
con t[X1] =t1 e t[X2] =t2 }
20/03/2006 7
A MoriB Bruni
Reparto CapoRossi ANeri B
Bianchi B
Impiegato Reparto
Rossi A MoriNeri B Bruni
Impiegato Reparto Capo
Bianchi B Bruni
Rossi ANeri B
Bianchi B
Rossi ANeri B
Bianchi B
A MoriB BruniA MoriB BruniB BruniB Bruni
� ogni ennupla contribuisce al risultato:� join completo
20/03/2006 8
Neri B MoriImpiegato Reparto Capo
Bianchi B Mori
Impiegato RepartoRossi ANeri B
Bianchi B
Reparto CapoB MoriC Bruni
AC
Un join non completo
20/03/2006 9
Impiegato RepartoRossi ANeri B
Bianchi B
Reparto CapoD MoriC Bruni
Impiegato Reparto Capo
Un join vuoto
20/03/2006 10
Rossi B Mori
Neri B Mori
Impiegato Reparto Capo
Bianchi B Bruni
Rossi ANeri B
Impiegato RepartoRossi ANeri B
Rossi BNeri B
A MoriB Bruni
Reparto CapoA MoriB BruniB MoriB BruniB BruniB Bruni
Rossi B Bruni
Un join completo, con n x mennuple
20/03/2006 11
Un join in SQL� Impiegati(CodiceImp, CodReparto)� Reparto(CodReparto, Capo)
Impiegati JOIN Reparti
SELECT CodiceImp, i.CodReparto, CapoFROM Impiegati i, Reparti rWhere i.CodiceReparto=r.CodiceReparto
20/03/2006 12
Cardinalità del join� Il join di R1 e R2 contiene un numero di
ennuple compreso fra zero e il prodotto di|R1| e |R2|
� se il join coinvolge una chiave di R2, allora ilnumero di ennuple è compreso fra zero e |R1|
� se il join coinvolge una chiave di R2 e unvincolo di integrità referenziale, allora ilnumero di ennuple è pari a |R1|
20/03/2006 13
Cardinalità del join, esempi
� R1(A,B) , R2 (B,C)� in generale
0 ≤ |R1 JOIN R2| ≤ |R1| × |R2|� se B è chiave in R2
0 ≤ |R1 JOIN R2| ≤ |R1|� se B è chiave in R2 ed esiste vincolo di
integrità referenziale fra B (in R1) e R2:|R1 JOIN R2| = |R1|
20/03/2006 14
Impiegato RepartoRossi ANeri B
Bianchi B
Reparto CapoB MoriC Bruni
Neri B MoriImpiegato Reparto Capo
Bianchi B Mori
AC
Join, una difficoltà
� alcune ennuple non contribuiscono alrisultato: vengono "tagliate fuori"
20/03/2006 15
Join esterno
� Il join esterno estende, con valori nulli,le ennuple che verrebbero tagliate fuorida un join (interno)
� esiste in tre versioni:� sinistro, destro, completo
20/03/2006 16
Join esterno
� sinistro: mantiene tutte le ennuple del primooperando, estendendole con valori nulli, senecessario
� destro: mantiene tutte le ennuple del delsecondo operando , estendendole con valorinulli, se necessario
� completo: mantiene tutte le ennuple di entrambigli operandi, estendendole con valori nulli, senecessario
20/03/2006 17
Impiegato RepartoRossi ANeri B
Bianchi B
ImpiegatiReparto Capo
B MoriC Bruni
Reparti
Neri B MoriImpiegato Reparto Capo
Bianchi B Mori
Impiegati JOINLEFT Reparti
C
Rossi A NULL
ARossi
20/03/2006 18
Impiegato RepartoRossi ANeri B
Bianchi B
ImpiegatiReparto Capo
B MoriC Bruni
Reparti
Neri B MoriImpiegato Reparto Capo
Bianchi B Mori
Impiegati JOINRIGHT Reparti
A
NULL C Bruni
C Bruni
20/03/2006 19
Impiegato RepartoRossi ANeri B
Bianchi B
ImpiegatiReparto Capo
B MoriC Bruni
Reparti
Neri B MoriImpiegato Reparto Capo
Bianchi B Mori
Impiegati JOINFULL Reparti
NULL C Bruni
C BruniARossi
Rossi A NULL
20/03/2006 20
Join e proiezioniImpiegato Reparto
Rossi ANeri B
Bianchi B
Reparto CapoB MoriC Bruni
Neri B MoriImpiegato Reparto Capo
Bianchi B Mori
Impiegato RepartoNeri B
Bianchi B
Reparto CapoB Mori
20/03/2006 21
Proiezioni e joinNeri B Mori
Impiegato Reparto Capo
Bianchi B BruniVerdi A Bini
Neri BImpiegato Reparto
Bianchi BVerdi A
B MoriReparto Capo
B BruniA Bini
Verdi A Bini
Neri B MoriImpiegato Reparto Capo
Bianchi B BruniNeri B Bruni
Bianchi B Mori
20/03/2006 22
Join e proiezioni
� R 1(X1), R 2(X2)
PROJX1(R 1 JOIN R2 ) ⊆ R 1
� R(X), X = X1 ∪ X2
(PROJX1(R)) JOIN (PROJX2
(R))⊇ R
20/03/2006 23
Prodotto cartesiano
� un join naturale su relazioni senza attributi incomune
� contiene sempre un numero di ennuple pari alprodotto delle cardinalità degli operandi (leennuple sono tutte combinabili )
20/03/2006 24
Rossi ANeri B
Bianchi B
Impiegato RepartoImpiegati
A MoriB BruniB BruniB Bruni
Codice CapoReparti
Impiegati JOIN RepartiImpiegato Reparto CapoCodice
Rossi A MoriAAARossi A B BruniNeri B MoriANeri B B Bruni
Bianchi B MoriABianchi B B Bruni
20/03/2006 25
� Il prodotto cartesiano, in pratica, ha sensosolo se seguito da selezione:
SELCondizione (R1 JOIN R2)� L'operazione viene chiamata theta-join e
indicata conR1 JOINCondizione R2
20/03/2006 26
Perché "theta-join"?� La condizione C è spesso una congiunzione
(AND) di atomi di confronto A1ϑ A2 dove ϑ èuno degli operatori di confronto (=, >, <, …)
� se l'operatore è sempre l'uguaglianza (=)allora si parla di equi-join
20/03/2006 27
Rossi ANeri B
Bianchi B
Impiegato RepartoImpiegati
A MoriB BruniB BruniB Bruni
Codice CapoReparti
Impiegati JOINReparto=Codice RepartiImpiegato Reparto CapoCodice
Rossi A MoriAAARossi A B BruniNeri B MoriANeri B B Bruni
Bianchi B MoriABianchi B B Bruni
Rossi A MoriAAANeri B B Bruni
Bianchi B B Bruni
20/03/2006 28
Rossi ANeri B
Bianchi B
Impiegato RepartoImpiegati
A MoriB BruniB BruniB Bruni
Reparto CapoReparti
Impiegati JOIN Reparti
20/03/2006 29
Join naturale ed equi-join
Impiegato RepartoImpiegati
Reparto CapoReparti
Impiegati JOIN Reparti
PROJImpiegato,Reparto,Capo (
)RENCodice ←←←← Reparto (Reparti)Impiegati JOIN
SELReparto=Codice
( )
20/03/2006 30
EsempiImpiegati Nome Età StipendioMatricola
Bianchi 37 385998Rossi 34 457309
Bruni 43 425698Neri 42 359553
Mori 45 504076Lupi 46 608123
Supervisione Impiegato Capo
59987309
56989553
4076
56985698
40764076
8123
20/03/2006 31
� Trovare matricola, nome, età e stipendiodegli impiegati che guadagnano più di 40milioni
SELStipendio>40(Impiegati)
20/03/2006 32
Nome Età StipendioMatricola
Bianchi 37 385998Rossi 34 457309
Bruni 43 425698Neri 42 359553
Mori 45 504076Lupi 46 608123
SELStipendio>40(Impiegati)
Bianchi 37 385998Neri 42 359553
Rossi 34 457309
Bruni 43 425698Mori 45 504076Lupi 46 608123
Rossi 34 457309Bruni 43 425698Mori 45 504076Lupi 46 608123
20/03/2006 33
� Trovare matricola, nome ed età degliimpiegati che guadagnano più di 40 milioni
PROJMatricola, Nome, Età (SELStipendio>40(Impiegati))
20/03/2006 34
Nome Età StipendioMatricola
Bianchi 37 385998Rossi 34 457309
Bruni 43 425698Neri 42 359553
Mori 45 504076Lupi 46 608123
Bianchi 37 385998Neri 42 359553
Rossi 34 457309
Bruni 43 425698Mori 45 504076Lupi 46 608123
Rossi 34 457309Bruni 43 425698Mori 45 504076Lupi 46 608123
SELStipendio>40(Impiegati)PROJMatricola, Nome, Età
( )
20/03/2006 35
� Trovare le matricole dei capi degliimpiegati che guadagnano più di 40 milioni
PROJCapo (SupervisioneJOIN Impiegato=Matricola
(SELStipendio>40(Impiegati)))
20/03/2006 36
� Trovare nome e stipendio dei capi degliimpiegati che guadagnano più di 40 milioni
PROJNome,Stipendio (Impiegati JOIN Matricola=Capo
PROJCapo(SupervisioneJOIN Impiegato=Matricola (SELStipendio>40(Impiegati))))
20/03/2006 37
� Trovare gli impiegati che guadagnano più del proprio capo, mostrando matricola, nome e stipendiodell'impiegato e del capo
PROJMatr,Nome,Stip,MatrC,NomeC,StipC(SELStipendio>StipC(
RENMatrC,NomeC,StipC,EtàC ← Matr,Nome,Stip,Età(Impiegati)JOIN MatrC=Capo
(Supervisione JOIN Impiegato=Matricola Impiegati)))
SELECT i1.Matricola, i1.nome, i1.stipendio,i2.matricola, i2.nome, i2.stipendio
FROM Impiegati i1, Impiegati i2, Supervisione sWHERE (i1.matricola=s.impiegato) AND
(i1.stipendio>i2.stipendio) AND(i2.matricola=s.capo);