SQL 2 : Plusieurs tables
JOIN, UNION, INTERSECT...
Quentin Fortier
January 17, 2024
Plusieurs tables
Soit T une table à n colonnes.
Chaque enregistrement de T peut être vu comme un n-uplet.
Donc T peut être vue comme un ensemble de n-uplets, c’est-à-dire une
relation (au sens mathématique).
Plusieurs tables
Soit T une table à n colonnes.
Chaque enregistrement de T peut être vu comme un n-uplet.
Donc T peut être vue comme un ensemble de n-uplets, c’est-à-dire une
relation (au sens mathématique).
En voyant les tables comme des ensembles, on peut effectuer des
opérations ensemblistes classiques (union, intersection...) ainsi que des
opérations plus adaptées (produit cartésien, jointure...).
Opérations ensemblistes
Si R1 et R2 sont des tables ayant le même schéma relationnel,
R1 ∪ R2 contient les enregistrements dans R1 ou R2 :
R1 ∪ R2
R1 R2
A B C
A B C A B C
a1 b1 c1
a1 b1 c1 a1 b1 c1
a2 b2 c2
a2 b2 c2 a3 b3 c3
a3 b3 c3
En SQL :
SELECT * FROM R1 UNION SELECT * FROM R2;
Opérations ensemblistes
Si R1 et R2 sont des tables ayant le même schéma relationnel,
R1 − R2 contient les enregistrements dans R1 mais pas dans R2 :
R1 R2
R1 − R2
A B C A B C
A B C
a1 b1 c1 a1 b1 c1
a2 b2 c2
a2 b2 c2 a3 b3 c3
En SQL, on utilise MINUS (MySQL, Oracle) ou EXCEPT (PostgreSQL) :
SELECT * FROM R1 EXCEPT SELECT * FROM R2;
Opérations ensemblistes
Si R1 et R2 sont des tables ayant le même schéma relationnel,
R1 ∩ R2 contient les enregistrements à la fois dans R1 et R2 :
R1 R2
R1 ∩ R2
A B C A B C
A B C
a1 b1 c1 a1 b1 c1
a1 b1 c1
a2 b2 c2 a3 b3 c3
En SQL :
SELECT * FROM R1 INTERSECT SELECT * FROM R2;
Produit cartésien
On peut réaliser le produit cartésien R1 × R2 de deux tables :
R1 × R2
R1 R2 A B C D E
A B C D E a1 b1 c1 d1 e1
a1 b1 c1 d1 e1 a1 b1 c1 d2 e2
a2 b2 c2 d2 e2 a2 b2 c2 d1 e1
a2 b2 c2 d2 e2
En SQL :
SELECT * FROM R1, R2;
Produit cartésien
On peut réaliser le produit cartésien R1 × R2 de deux tables :
R1 × R2
R1 R2 A B C D E
A B C D E a1 b1 c1 d1 e1
a1 b1 c1 d1 e1 a1 b1 c1 d2 e2
a2 b2 c2 d2 e2 a2 b2 c2 d1 e1
a2 b2 c2 d2 e2
En SQL :
SELECT * FROM R1, R2;
On peut aussi sélectionner seulement certaines colonnes de R1 × R2 en
écrivant, par exemple, SELECT A, B FROM R1, R2;
Clé étrangère
Considérons une base de donnée bibliotheque avec les tables :
1 livre (titre : CHAR(50), auteur : CHAR(50), pages : INT)
2 emprunteur (id : INT, nom : CHAR(50))
3 emprunt (id_emprunteur : INT, titre_livre : CHAR(50))
Clé étrangère
Considérons une base de donnée bibliotheque avec les tables :
1 livre (titre : CHAR(50), auteur : CHAR(50), pages : INT)
2 emprunteur (id : INT, nom : CHAR(50))
3 emprunt (id_emprunteur : INT, titre_livre : CHAR(50))
Dans la table emprunt, id_emprunteur et titre_livre sont des clés
étrangères, ce qui signifie qu’elles font références à une clé primaire
d’une autre table.
Clé étrangère
1 livre (titre : CHAR(50), auteur : CHAR(50), pages : INT)
2 emprunteur (id : INT, nom : CHAR(50))
3 emprunt (id_emprunteur : INT, titre_livre : CHAR(50))
Comment obtenir les emprunteurs qui sont aussi auteurs?
Clé étrangère
1 livre (titre : CHAR(50), auteur : CHAR(50), pages : INT)
2 emprunteur (id : INT, nom : CHAR(50))
3 emprunt (id_emprunteur : INT, titre_livre : CHAR(50))
Comment obtenir les emprunteurs qui sont aussi auteurs?
SELECT nom FROM emprunteur, livre
WHERE nom = auteur;
Clé étrangère
1 livre (titre : CHAR(50), auteur : CHAR(50), pages : INT)
2 emprunteur (id : INT, nom : CHAR(50))
3 emprunt (id_emprunteur : INT, titre_livre : CHAR(50))
Comment obtenir les noms des personnes qui ont emprunté le livre dont
le titre est Le Banquet?
Clé étrangère
1 livre (titre : CHAR(50), auteur : CHAR(50), pages : INT)
2 emprunteur (id : INT, nom : CHAR(50))
3 emprunt (id_emprunteur : INT, titre_livre : CHAR(50))
Comment obtenir les noms des personnes qui ont emprunté le livre dont
le titre est Le Banquet?
SELECT nom FROM emprunteur, emprunt
WHERE id = id_emprunteur
AND titre_livre = 'Le Banquet';
Jointure
La jointure R1 ./A=D R2 de deux tables R1 et R2 revient à combiner
les enregistrements de R1 et R2 en identifiant les colonnes A et D :
Jointure
La jointure R1 ./A=D R2 de deux tables R1 et R2 revient à combiner
les enregistrements de R1 et R2 en identifiant les colonnes A et D :
R1
R2 R1 ./A=D R2
A B C
D E A B C E
a1 b1 c1
a1 e1 a1 b1 c1 e1
a2 b2 c2
a2 e2 a2 b2 c2 e2
a3 b3 c3
En SQL :
SELECT ... FROM R1 JOIN R2 ON A = D;
Jointure : Exemples
1 livre (titre : CHAR(50), auteur : CHAR(50), pages : INT)
2 emprunteur (id : INT, nom : CHAR(50))
3 emprunt (id_emprunteur : INT, titre_livre : CHAR(50))
Comment obtenir les noms des personnes qui ont emprunté le livre Le
Banquet?
Jointure : Exemples
1 livre (titre : CHAR(50), auteur : CHAR(50), pages : INT)
2 emprunteur (id : INT, nom : CHAR(50))
3 emprunt (id_emprunteur : INT, titre_livre : CHAR(50))
Comment obtenir les noms des personnes qui ont emprunté le livre Le
Banquet?
On peut aussi utiliser une jointure :
SELECT nom FROM emprunteur
JOIN emprunt ON id = id_emprunteur
WHERE titre_livre = 'Le Banquet';
Jointure : Exemples
1 livre (titre : CHAR(50), auteur : CHAR(50), pages : INT)
2 emprunteur (id : INT, nom : CHAR(50))
3 emprunt (id_emprunteur : INT, titre_livre : CHAR(50))
Comment obtenir les titres des livres empruntés par M. Machin?
Jointure : Exemples
1 livre (titre : CHAR(50), auteur : CHAR(50), pages : INT)
2 emprunteur (id : INT, nom : CHAR(50))
3 emprunt (id_emprunteur : INT, titre_livre : CHAR(50))
Comment obtenir les titres des livres empruntés par M. Machin?
SELECT titre_livre FROM emprunteur
JOIN emprunt ON id = id_emprunteur
WHERE nom = 'Machin';
Jointure : Exemples
1 livre (titre : CHAR(50), auteur : CHAR(50), pages : INT)
2 emprunteur (id : INT, nom : CHAR(50))
3 emprunt (id_emprunteur : INT, titre_livre : CHAR(50))
Comment obtenir les noms des personnes ayant emprunté un livre écrit
par Stephen King?
Jointure : Exemples
1 livre (titre : CHAR(50), auteur : CHAR(50), pages : INT)
2 emprunteur (id : INT, nom : CHAR(50))
3 emprunt (id_emprunteur : INT, titre_livre : CHAR(50))
Comment obtenir les noms des personnes ayant emprunté un livre écrit
par Stephen King?
SELECT nom FROM emprunteur
JOIN emprunt ON id = id_emprunteur
JOIN livre ON titre_livre = titre
WHERE auteur = 'Stephen King';
Jointure : Auto-jointure
1 livre (titre : CHAR(50), auteur : CHAR(50), pages : INT)
2 emprunteur (id : INT, nom : CHAR(50))
3 emprunt (id_emprunteur : INT, titre_livre : CHAR(50))
Comment obtenir les plus gros livres empruntés avec leur nombre de
pages?
Jointure : Auto-jointure
1 livre (titre : CHAR(50), auteur : CHAR(50), pages : INT)
2 emprunteur (id : INT, nom : CHAR(50))
3 emprunt (id_emprunteur : INT, titre_livre : CHAR(50))
Comment obtenir les plus gros livres empruntés avec leur nombre de
pages?
SELECT titre, pages FROM livre
JOIN emprunt ON titre_livre = titre
ORDER BY pages DESC;
Jointure : Auto-jointure
1 livre (id : INT, titre : CHAR(50), auteur : CHAR(50), pages :
INT)
2 emprunteur (id : INT, nom : CHAR(50))
3 emprunt (id_emprunteur : INT, titre_livre : CHAR(50))
Problème : comment savoir, dans livre × emprunteur, à quelle table id
fait référence?
Jointure : Auto-jointure
1 livre (id INT, titre CHAR(50), auteur CHAR(50), pages INT)
2 emprunteur (id INT, nom CHAR(50))
3 emprunt (id_emprunteur INT, titre_livre CHAR(50))
Tentative :
SELECT id FROM livre, emprunteur;
Résultat :
Column ’id’ in field list is ambiguous
Jointure : Auto-jointure
1 livre (id INT, titre CHAR(50), auteur CHAR(50), pages INT)
2 emprunteur (id INT, nom CHAR(50))
3 emprunt (id_emprunteur INT, titre_livre CHAR(50))
Solution :
SELECT livre.id FROM livre, emprunteur;
Jointure : Auto-jointure
1 livre (id INT, titre CHAR(50), auteur CHAR(50), pages INT)
2 emprunteur (id INT, nom CHAR(50))
3 emprunt (id_emprunteur INT, titre_livre CHAR(50))
Problème 2 : afficher tous les couples de livres ayant le même nombre
de pages.
Jointure : Auto-jointure
1 livre (id INT, titre CHAR(50), auteur CHAR(50), pages INT)
2 emprunteur (id INT, nom CHAR(50))
3 emprunt (id_emprunteur INT, titre_livre CHAR(50))
Problème 2 : afficher tous les couples de livres ayant le même nombre
de pages.
SELECT titre, titre FROM livre, livre WHERE pages = pages;
Ne marche pas du tout!
Jointure : Auto-jointure
1 livre (id INT, titre CHAR(50), auteur CHAR(50), pages INT)
2 emprunteur (id INT, nom CHAR(50))
3 emprunt (id_emprunteur INT, titre_livre CHAR(50))
Solution : renommer les tables.
Jointure : Auto-jointure
1 livre (id INT, titre CHAR(50), auteur CHAR(50), pages INT)
2 emprunteur (id INT, nom CHAR(50))
3 emprunt (id_emprunteur INT, titre_livre CHAR(50))
Solution : renommer les tables.
SELECT liv1.titre, liv2.titre
FROM livre AS liv1, livre AS liv2
WHERE liv1.pages = liv2.pages;
Jointure : Auto-jointure
