Les jointures

I. Les jointures⚓︎

Nous allons créer une nouvelle base de données ayant deux tables :

À vous de jouer 1

1. Créez une table auteur comportant les attributs suivants

id : entier - clé primaire
nom : texte
prenom : texte
ann_naissance : entier
langue_ecriture : texte

Créez une table livre comportant les attributs suivants

id : entier - clé primaire
titre : texte
id_auteur : entier
ann_publi : entier
note : entier

Sql

Solution

CREATE TABLE auteur
(id INT PRIMARY KEY, nom TEXT, prenom TEXT, ann_naissance INT, langue_ecriture TEXT);

CREATE TABLE livre
(id INT PRIMARY KEY, titre TEXT, id_auteur INT, ann_publi INT, note INT);

2. Nous allons ajouter les données de la table auteur et de la table livre

Exécuter :

Sql

INSERT INTO auteur
(id, nom, prenom, ann_naissance, langue_ecriture)
VALUES
(1,'Orwell','George',1903,'anglais'),
(2,'Herbert','Frank',1920,'anglais'),
(3,'Asimov','Isaac',1920,'anglais'),
(4,'Huxley','Aldous',1894,'anglais'),
(5,'Bradbury','Ray',1920,'anglais'),
(6,'K.Dick','Philip',1928,'anglais'),
(7,'Barjavel','René',1911,'français'),
(8,'Boulle','Pierre',1912,'français'),
(9,'Van Vogt','Alfred Elton',1912,'anglais'),
(10,'Verne','Jules',1828,'français');

INSERT INTO livre
(id, titre, id_auteur, ann_publi, note)
VALUES
(1,'1984',1,1949,10),
(2,'Dune',2,1965,8),
(3,'Fondation',3,1951,9),
(4,'Le meilleur des mondes',4,1931,7), 
(5,'Fahrenheit 451',5,1953,7),
(6,'Ubik',6,1969,9),
(7,'Chroniques martiennes',5,1950,8),
(8,'La nuit des temps',7,1968,7),
(9,'Blade Runner',6,1968,8),
(10,'Les Robots',3,1950,9),
(11,'La Planète des singes',8,1963,8),
(12,'Ravage',7,1943,8),
(13,'Le Maître du Haut Château',6,1962,8),
(14,'Le monde des Ā',9,1945,7),
(15,'La Fin de l’éternité',3,1955,8),
(16,'De la Terre à la Lune',10,1865,10);

😊 Nous avons maintenant 2 tables que nous allons pouvoir associer grâce aux jointures.

Résumé

Il existe différents types de jointures en SQL. Cette année nous n’étudierons que : JOIN … ON

🌵 Remarque : vous trouverez parfois écrit INNER JOIN … ON. C'est la même chose.

JOIN ... ON

Exemple

Tester :

Sql

😊 Il s’agit de la jointure la plus simple. C’est une jointure dite interne. Ce type de jointure va renvoyer les entrées des deux tables si jamais une correspondance a été trouvée entre les deux tables.

Ici l’identifiant id de la table auteur correspond à l’identifiant : id stocké dans la table livre.

FROM livre JOIN auteur permet de créer une jointure entre les tables livre et auteur c’est-à-dire réunir les tables livre et auteur en une seule table.

ON livre.id_auteur = auteur.id signifie qu'une ligne de la table livre devra être fusionnée avec la ligne de la table auteur à condition que l'attribut id_auteur de la ligne de la table livre soit égal à l'attribut id de la ligne de la table auteur.

Noms d'attributs identiques

🌵 Si un même nom d'attribut est présent dans les 2 tables (par exemple ici l'attribut id), il est nécessaire d'ajouter le nom de la table devant afin de pouvoir les distinguer (auteur.id et livre.id)

AS

L’utilisation des « alias », mot clé AS, permet d’obtenir des écritures plus compactes. C’est très souvent utilisé.

Reprenons l’exemple précédant mais en écrivant les lignes suivantes :
La table livre est désignée par la lettre l et la table auteur par la lettre a.

Exécuter :

Sql

À vous de jouer 2

Exécutez

Sql

Quelle différence constatez-vous avec la requête précédente ?

Solution

On a d'abord les colonnes de la table auteur, puis ensuite celles de la table livre.

À vous de jouer 3

Écrire la jointure entre la table livre et la table auteur en ne sélectionnant que les attributs titre de la tables livre et nom et prenom de la table auteur.

Sql

Solution

SELECT  titre, nom , prenom
FROM livre
JOIN auteur ON livre.id_auteur = auteur.id;

Syntaxes

👉 Dans le cas d'une jointure, Il est possible d'utiliser la clause WHERE et ORDER BY après le JOIN ... ON ...

À vous de jouer 4

Écrire la jointure entre la table livre et la table auteur en ne sélectionnant que les attributs titre, nom, prenom ann_publi où seules les années de publication supérieures à 1950 sont considérées.

Le résultat sera donné par ordre alphabétique des nom d'auteur.

Sql

Solution

SELECT  titre, nom , prenom , ann_publi
FROM livre
JOIN auteur ON livre.id_auteur = auteur.id
WHERE ann_publi >= 1950
ORDER BY nom ASC;

II. Les clés étrangères⚓︎

Clé étrangère

Les jointures permettent d’associer plusieurs tables dans une même requête. Pour cela nous avons besoin comme nous l'avons vu dans le schéma relationnel d'une clé primaire et d'une clé étrangère.

Dans notre exemple l'attribut id_auteur de la tables livre est bien une clé étrangère puisque cet attribut correspond à l'attribut id de la table auteur.

Il est possible lors de la création d'une table de définir une clé étrangère.

À vous de jouer 5

1. Ajoutez à la table livre un enregistrement pour lequel id_auteur n’est pas possible compte-tenu de la table existante (par exemple 12).

Sql

Solution

INSERT INTO livre
(id, titre, id_auteur, ann_publi, note)
VALUES
(17, "L'Odyssée", 12, 1571, 9);

2. Que se passe-t-il ?

Solution

😢 Il ne se passe rien ... L'anomalie n'a pas été détectée.

Exemple

👉 Nous allons créer une nouvelle table livre_2, de la façon suivante :

Sql

Recommencer : essayez d’ajouter à la table livre_2 un enregistrement pour lequel id_auteur n’est pas possible compte-tenu de la table existante (par exemple 12)

Sql

Clé étrangère

Grâce à cette clé étrangère, SQLite sera capable de détecter les anomalies au niveau de la clé étrangère.

Crédits⚓︎

Jean-Louis THIROT, Valérie MOUSSEAUX et Mireille COILHAC

D'après David Roche, Pixees et Stéphan Van Zuijlen Lycée Jean Moulin