Base de données
Séance 8
Algèbre relationnelle et SQL :
- Jointures externes
- Requêtes complexes
La jointure externe
La jointure externe permet de récupérer
les lignes des tables correspondant au
critère de jointure, mais aussi celles
pour lesquelles il n'existe pas de
correspondances.
Plusieurs types de jointure externe :
Jointure (externe) à gauche
Jointure (externe) à droite
Jointure (externe) complète
2
Jointure à gauche : introduction
Soit une table décrivant des
commerciaux, et une autre décrivant
des affaires.
On veut établir le comptage du chiffre
d’affaire de tous les commerciaux, y
compris ceux qui n’ont rien fait.
3
L’echec de la jointure classique
SELECT
id_commercial, SUM([Link])
FROM
commercial AS c, affaire AS a
WHERE
id_commercial = [Link]
GROUP BY id_commercial;
Ne donne pas le résultat escompté
Il manque les commerciaux feignants
4
Solution : la jointure gauche
SELECT
id_commercial, SUM([Link])
FROM
commercial AS c
LEFT JOIN
affaire AS a
ON
id_commercial = [Link]
GROUP BY [Link];
Ajoute à la jointure interne les
commerciaux qui n’ont pas d’affaire
5
commercial affaire
id nom id ca id_commercial
1 John 1 100 2
2 Henri 2 350 1
3 Chuck 3 50 1
4 200 2
R = commercial [Link] = id_commercial affaire
id nom id ca id_commercial
1 John 2 350 1 id_comFid_com,SUM(ca)(R)
1 John 3 50 1 id_commercial SUM(ca)
2 Henri 1 100 2 1 400
2 Henri 4 200 2 2 300
3 Chuck NULL NULL NULL 3 0
6
La jointure externe : LEFT, RIGHT et FULL
RIGHT OUTER JOIN :
la table à droite de l'expression clef "RIGHT
OUTER" renvoie des lignes sans correspondance
avec la table à gauche.
LEFT OUTER JOIN :
la table à gauche de l'expression clef "LEFT
OUTER" renvoie des lignes sans correspondance
avec la table à droite.
FULL OUTER JOIN :
les deux tables renvoient des lignes sans
correspondance entre elles.
7
La jointure externe : syntaxe SQL
SELECT ... FROM
<table gauche>
LEFT |RIGHT | FULL [OUTER] JOIN
<table droite>
ON
<condition de jointure>
WHERE … ;
8
Les jointures externes
tab1 tab2
col11 col12 col21 col22
a x Lignes avec a 1
correspondance
b y b 2
Lignes sans
c z correspondance d 3
9
Les jointures externes : LEFT
tab1 tab2
col11 col12 col21 col22
a x a 1 axa 1
b y b 2 byb 2
c z d 3 c z NULL NULL
SELECT *
FROM tab1 tab1 col11=col21tab2
LEFT OUTER JOIN tab2
ON tab1.col11 = tab2.col21;
10
Les jointures externes : LEFT 2
tab1 tab2
col11 col12 col21 col22
a x a 1 axa 1
b y b 2 byb 2
c z d 3 c z NULL NULL
SELECT *
FROM tab1,tab2
WHERE tab1.col1 = tab2.col1(+) ;
11
Les jointures externes : RIGHT
tab1 tab2
col11 col12 col21 col22
a x a 1 a x a 1
b y b 2 b y b 2
c z d 3 NULL NULL d 3
SELECT *
FROM tab1 tab1 col11=col21tab2
RIGHT OUTER JOIN tab2
ON tab1.col11 = tab2.col21;
12
Les jointures externes : RIGHT 2
tab1 tab2
col11 col12 col21 col22
a x a 1
a x a 1
b y b 2
b y b 2
NULL NULL d 3
c z d 3
SELECT *
FROM tab1,tab2
WHERE tab1.col11(+) = tab2.col21;
13
Les jointures externes : FULL
tab1 tab2
col11 col12 col21 col22
a x a 1 a x a 1
b y b 2 b y b 2
c z d 3 c z NULL NULL
SELECT * NULL NULL d 3
FROM tab1
FULL OUTER JOIN tab2 tab1 col11=col21tab2
ON tab1.col11 = tab2.col21;
14
Les jointures externes : exemple 1
SELECT [Link],COUNT(r.id_vin)
FROM
producteur p
LEFT OUTER JOIN
recolte r
ON
r.id_producteur = [Link]
GROUP BY [Link] ;
[Link], COUNT(id_vin)(
rp(producteur) [Link]=id_producteur recolte)
15
Les jointures externes : exemple 1
SELECT
[Link],COUNT(r.id_vin)
FROM
producteur p, recolte r
WHERE
[Link] = r.id_producteur (+)
GROUP BY
[Link] ;
16
Les jointures externes : exemple 2
Calculer le nombre de films joués par
chaque individu (y compris ceux qui ne sont
pas acteurs)
17
SELECT [Link], [Link], COUNT(j.num_film)
FROM individu i LEFT OUTER JOIN jouer j
ON i.num_ind = j.num_ind
GROUP BY i.num_ind, [Link], [Link];
SELECT [Link], [Link], COUNT(j.num_film)
FROM individu i, jouer j
WHERE i.num_ind = j.num_ind (+)
GROUP BY i.num_ind, [Link], [Link];
i.num_ind,[Link],[Link],[Link],COUNT(j.num_film)(
ri(individu) i.num_ind=j.num_ind rj(jouer))
Les jointures externes : clauses WHERE
SELECT * FROM tab1
LEFT OUTER JOIN tab2
ON tab1.col11 = tab2.col21
AND tab1.col11 <> 'a';
N’est pas identique à
SELECT * FROM tab1
LEFT OUTER JOIN tab2
ON tab1.col11 = tab2.col21
WHERE tab1.col1 <> 'a';
19
Les jointures externes : clauses WHERE
tab1 tab2
col11 col12 col21 col22
a x a 1 a x NULL NULL
b y b 2 byb 2
c z d 3 c z NULL NULL
SELECT * FROM tab1
LEFT OUTER JOIN tab2
ON col11 = col21 tab1 col11=col21 and col11≠'a'tab2
AND col11 <> 'a';
20
Les jointures externes : clauses WHERE
tab1 tab2
col11 col12 col21 col22
a x a 1 byb 2
b y b 2 c z NULL NULL
c z d 3
SELECT * FROM tab1
scol11≠'a'(tab1 tab2)
LEFT OUTER JOIN tab2 col11=col21
ON tab1.col11 = tab2.col21
WHERE tab1.col11 <> 'a' ;
21
Les jointures croisées
SELECT
colonnes
FROM
table1 t1
CROSS JOIN
table2 t2
[WHERE prédicat] ...
22
Jointures : résumé
Jointure interne = jointure avec pivot
Jointure naturelle
Jointure externe : gauche, droit,
complète
Jointure croisée : produit cartésien
Requêtes complexes
Décomposer le problème en sous-
problèmes simples
Relier les sous-résultats par :
opérateur ensembliste
jointure
sous-requête (SQL)
Projeter les informations utiles
24
Décomposition 1
Donner les degrés des vins de cru Morgon
et de millésime 1978
R1 = scru = 'Morgon'(VINS)
R2 = smill = 1978(VINS)
R3 = R1 ∩ R2
Résultat = Pdegré(R3)
Arbre algébrique : exemple 1
Résultat
degré
cru = 'Morgon' mill = 1978
Vins Vins
Décomposition 2
Donner les noms et prénoms des buveurs
habitant à Paris ayant bu du Chablis
depuis le 1er janvier 1992
R1 = sadresse = 'Paris'(BUVEURS)
R2 = scru = 'Chablis'(VINS)
R3 = sdate ≥ 01/01/1992(ABUS)
R4 = JOIN(R1,R3)
R5 = JOIN(R2,R4)
Résultat = Pnom, prénom(R5)
Arbre algébrique : exemple 2
Résultat
nom, prénom
adresse = 'Paris' date > 01/01/1992 cru = 'Chablis'
Buveurs Abus Vins
Sous-requêtes dépendantes
Une sous-requête dépendante utilise
une donnée de la requête principale.
29
WHERE EXISTS et WHERE NOT EXISTS
SELECT
c.id_client
FROM Dépendance
client c
WHERE EXISTS
(SELECT cde.id_client
FROM commande cde
WHERE cde.id_client = c.id_client)
Cette requête utilise une sous-requête
dépendante.
Le prédicat est vrai quand le résultat de la sous-
requête est non vide (contient au moins une ligne).
30
Division : AR vers SQL
Quels sont les acteurs qui ont joué dans tous
les films de Lars von Trier ?
En algèbre relationnelle :
Individu(num_ind, nom, prenom)
Jouer(num_ind, num_film, role)
Film(num_film, num_ind, titre, genre, annee)
Idée :
Résultat = Jouer ÷ (films de Lars von Trier)
Division : AR vers SQL
Les films de Lars von Trier :
R1 = snom='von Trier' and prenom='Lars'(Film Individu)
num_film num_ind titre genre année nom prenom
05 13 Dogville Drame 2002 von Trier Lars
04 13 Breaking … Drame 1996 von Trier Lars
R2 = Pnum_film(R1)
Pnum_ind, num_film(Jouer)
num_ind num_film R2
÷
num_film
01 05
05
02 05 04
03 04
04 04 num_ind num_ind
05 03 01 03
06 03 02 ∩ 04
07 03 04
08 02
09 01 R3
num_ind
10 01
04
11 01
04 05
16 07
Pnum_ind, num_film(Jouer)
num_ind num_film R2
÷
num_film
01 05
05
02 05 04
03 04
04 04 num_ind num_ind
05 03 01 03
06 03 02 ∩ 04
07 03 04
08 02
09 01 R3
num_ind Individu
10 01
04
11 01
Pprenom,nom
04 05
16 07
R4
prenom nom
Stellan Skarsgard
Division en SQL avec EXISTS
Quels sont les acteurs qui ont joué
dans tous les films de Lars von Trier ?
Reformulation :
Quels sont les acteurs qui vérifient : il
est faux qu’il existe un film de Lars
von Trier dans lequel l’acteur n’a pas
joué.
Traduction SQL
SELECT DISTINCT nom, prenom FROM individu acteur_tous_lars
WHERE NOT EXISTS (
SELECT * FROM film film_lars JOIN individu i
ON film_lars.num_ind = i.num_ind
AND nom = 'von Trier' AND prenom = 'Lars‘
WHERE NOT EXISTS (
SELECT * FROM individu i JOIN jouer j
ON i.num_ind = j.num_ind
WHERE i.num_ind = acteur_tous_lars.num_ind
AND num_film = film_lars.num_film));
Autre formulation en SQL
Quels sont les acteurs qui vérifient :
le nombre de films réalisés par Lars
von Trier dans lesquels l’acteur a joué
est égal au nombre de films réalisés
par Lars von Trier.
Traduction SQL
SELECT [Link], [Link]
FROM individu i, jouer j, film f
WHERE i.num_ind = j.num_ind
AND j.num_film = f.num_film
AND f.num_film IN ( SELECT num_film
FROM film f, individu i
WHERE f.num_ind = i.num_ind
AND [Link] = 'von Trier'
AND [Link] = 'Lars')
GROUP BY [Link], [Link]
HAVING COUNT (DISTINCT f.num_film) = (SELECT COUNT(*)
FROM film NATURAL JOIN individu
WHERE nom = 'von Trier' AND prenom = 'Lars');