27 - SQL : JOIN ON
Prerequis : l'activité précédente sur SQL.
Logiciel nécessaire : DB Brownser
Fichier SQLite : la base de données est à télécharger. Si vous n'avez pas de compte, vous pouvez me contacter avec le bouton en bas de page.
1 - Principe de la jointure
Pour l'instant, nous n'avons travaillé qu'avec une relation à la fois.
Mais nous avions vu avec l'exercice sur les jeux vidéos retro qu'il était possible de stocker les informations sur les jeux dans une relation jeu et stocker les informations sur les consoles dans une relation support.
Voici nos deux tables :
01° Est-il normal que les valeurs de l'attribut id de la relation jeu soient toutes différentes ?
...CORRECTION...
Oui : il s'agit de la clé primaire de la relation : une valeur doit être unique pour identifier à coup sûr l'un des nuplets.
02° Est-il normal que les valeurs de l'attribut id_support de la relation jeu ne soient pas toutes différentes ?
...CORRECTION...
Oui : il s'agit juste d'un attribut servant de clé étrangère : sa valeur doit correspondre à une valeur disponible en tant que clé primaire dans une autre table.
Nous avons deux tables mais nous voudrions créer un affichage ressemblant à ceci pour notre site Web parlant de jeux vidéo retro :

Le principe : faire une jointure.
Qu'est-ce que cela veut dire ?
Partir des enregistrements d'une relation et rajouter les attributs d'une autre relation à ces enregistrements.
Par exemple, on part de la relation jeu et on rajoute les attributs de la relation support. On dit qu'on fait une jointure.
Le principe est de générer de nouveaux nuplets qui possèderont les attributs des deux tables.
id | jeu | description | sortie | editeur | genre | fichier_image | id_support | id | nom | RAM | date_sortie |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | Flight Simulator | Simulation de piotage d'avion avec des pixels gros comme des camions ! | 1980 | subLOGIC | Simulateur | 1 | |||||
2 | DONKEY KONG | Un méchant singe géant a capturé la fiancé de JumpMan (qui portera plus tard le nom de Mario). Guide JumpMan pour qu'il délivre sa dulcinée. | 1981 | Nintendo | Plate-formes | NULL | |||||
3 | PITFALL! | Pitfall Harry, un explorateur, ramasse des objets éparpillés dans la jungle. | 1982 | Activision | Plate-formes | 2 | |||||
4 | PITFALL! | Pitfall Harry, un explorateur, ramasse des objets éparpillés dans la jungle. | 1983 | Activision | Plate-formes | 3 | |||||
5 | Boulder Dash | Rockford, mineur téméraire, ramasse des diamants et tente de ne pas se faire écraser par les énormes pierres instables. | 1983 | First Star Software | Plate-formes | ![]() |
3 |
Voici la requête SQL permettant de faire plus ou moins cela :
1
2
3 |
|
03° Que constatez-vous au niveau des noms des attributs de la réponse ?
id | jeu | description | sortie | editeur | genre | fichier_image | id_support | id | nom | RAM | date_sortie |
---|
...CORRECTION...
On remarque qu'on trouve plusieurs fois les mêmes noms d'attributs.
Il y a ainsi un id pour la relation jeu et un id pour la relation support.
Identification des attributs d'une relation
Pour différencier sans ambiguïté les attributs des relations, on applique le même principe que pour les attributs des objets : on note le nom de la relation suivi d'un point et du nom de l'attribut.
Ainsi, l'identifiant :
- de la table jeu pourra être noté
jeu.id
. - de la table support pourra être noté
support.id
.
Il reste maintenant à remplir les derniers champs de la table résultante : beaucoup ne sont pas présentes dans la table de base. Mais, on ne va pas les remplir au hasard : il faut chercher les valeurs manquantes dans la table support en s'aidant des valeurs fournies dans la clé secondaire id_support
.
id | jeu | description | sortie | editeur | genre | fichier_image | id_support | id | nom | RAM | date_sortie |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | Flight Simulator | Simulation de piotage d'avion avec des pixels gros comme des camions ! | 1980 | subLOGIC | Simulateur | 1 | |||||
2 | DONKEY KONG | Un méchant singe géant a capturé la fiancé de JumpMan (qui portera plus tard le nom de Mario). Guide JumpMan pour qu'il délivre sa dulcinée. | 1981 | Nintendo | Plate-formes | NULL | |||||
3 | PITFALL! | Pitfall Harry, un explorateur, ramasse des objets éparpillés dans la jungle. | 1982 | Activision | Plate-formes | 2 | |||||
4 | PITFALL! | Pitfall Harry, un explorateur, ramasse des objets éparpillés dans la jungle. | 1983 | Activision | Plate-formes | 3 | |||||
5 | Boulder Dash | Rockford, mineur téméraire, ramasse des diamants et tente de ne pas se faire écraser par les énormes pierres instables. | 1983 | First Star Software | Plate-formes | ![]() |
3 |
Pour la compléter, nous allons finaliser la clause JOIN : pour l'instant nous ne lui avons pas expliqué ce qu'il fallait ramener et dans quelles conditions.
Pour cela, on utilise le mot-clé ON qui va permettre de transmettre le lien à faire entre les deux tables.
Ici, le lien est clair : il s'agit d'un lien clé étrangère - clé primaire.
Clause JOIN ON
Lorsqu'on veut réunir les résultats de deux tables ou plus, il suffit d'utiliser la clause JOIN pour préciser la relation qu'on veut rajouter à la première suivi de ON pour préciser comment faire cette jointure : on précise quelle clé étrangère doit être égale à quelle clé primaire.
Exemple avec les relations
- jeu qui possède un attribut id_support qui est une clé étrangère faisant référence
- à la clé primaire id de la relation support.
1
2
3 |
|
On obtient alors ceci :
id | jeu | description | sortie | editeur | genre | fichier_image | id_support | id | nom | RAM | date_sortie |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | Flight Simulator | Simulation de piotage d'avion avec des pixels gros comme des camions ! | 1980 | subLOGIC | Simulateur | 1 | 1 | Apple II | 4 ko | 1977-06-10 | |
3 | PITFALL! | Pitfall Harry, un explorateur, ramasse des objets éparpillés dans la jungle. | 1982 | Activision | Plate-formes | 2 | 2 | Atari 2600 | 4 ko | 1977-10-14 | |
4 | PITFALL! | Pitfall Harry, un explorateur, ramasse des objets éparpillés dans la jungle. | 1983 | Activision | Plate-formes | 3 | 3 | C64 | 64 ko | 1982-08-01 | |
5 | Boulder Dash | Rockford, mineur téméraire, ramasse des diamants et tente de ne pas se faire écraser par les énormes pierres instables. | 1983 | First Star Software | Plate-formes | ![]() |
3 | 3 | C64 | 64 ko | 1982-08-01 |
On remarquera un point essentiel : le nuplet Donkey Kong n'est plus là puisque sa clé étrangère (valant NULL, absence d'information) ne peut pas correspondre à une autre clé primaire.
Si vous ne voulez pas tout récupérer, il suffit de fournir la liste des attributs voulus avec SELECT :
1
2
3 |
|
04° Même si c'est possible, pourquoi ne faut-il pas utiliser le même nom pour la relation et l'un de ses attributs ?
...CORRECTION...
Tout simplement car c'est source de confusion.
En python, on n'utilise pas non plus une variable locale portant le même nom que sa fonction par exemple.
NULL
NULL signifie "Absence d'informations".
Les bases de données sont conçues pour répondre FAUX à ceci : NULL = NULL
.
En effet, les bases de données doivent répondre exactement. Or, on ne peut pas affirmer qu'une absence d'informations corresponde à une autre absence d'informations. Puisque le champ n'est pas rempli, il est possible que les deux soient en réalité égaux ou différents. La réponse est donc FAUX.
1
2
3 |
|
Vous n'allez rien obtenir avec cette requête : NULL = NULL sera toujours évalué à FAUX.
Comment tester alors qu'une valeur est NULL ? Il faut utiliser cette façon :
1
2
3 |
|
Cette fois, la condition de WHERE sera toujours vraie : on aura donc toute la table.
En Python, on aurait Vrai dans les deux cas avec None :
>>> None == None
True
>>> None is None
True
2 - Jointure entre les tables emploi-titre
Voici le schéma relationnel des trois relations emploi , titre et personne de la base de données que nous allons encore utiliser aujourd'hui.
Version graphique :

Version en SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36 |
|
05° Fournir le schéma relationnel de la relation titre en les écrivant sous forme abstraite. On utilisera donc les notations soulignements et #.
...CORRECTION...
1
2
3
4
5
6
7
8
9
10
11 |
|
06° Fournir le schéma relationnel de la relation emploi en les écrivant sous forme abstraite. On utilisera donc les notations soulignements et #.
Comment doit-on utiliser le JOIN ON si on veut joindre les attributs de la relation titre en plus de ceux de la table emploi ?
...CORRECTION...
1
2
3
4
5
6
7
8
9 |
|
|
|
07° Ouvrir la base de données avec DBBrownser si ce n'est pas encore fait. Proposer la requête qui permet obtenir :
- la clé primaire de cet emploi,
- la catégorie de l'emploi,
- le nom du titre (le "primary_title", pas juste son identifiant) et
- l'identifiant nconst de l'employé (pas son vrai nom).
Expliquer votre requête comme si vous vous adressiez à quelqu'un qui débute en SQL mais qui a un peu de mal avec les jointures...
...CORRECTION...
On créer des nuplets à partir de la relation emploi en leur rajoutant les attributs de la relation titre. Pour trouver les bonnes informations à rajouter dans ce nouveau nuplet, on réalise la jointure en comparant la clé étrangère emploi.tconst
à la clé primaire titre.tconst
.
1
2
3 |
|

08° Quelle requête doit-on faire pour obtenir la même chose mais uniquement avec les films de 2015 et plus ? Trier le résultat par année de sortie. On rajoutera l'année dans la réponse de façon à vérifier si la requête fonctionne bien.
...CORRECTION...
1
2
3
4
5 |
|

3 - Liaison emploi-personne
Nous allons maintenant faire le lien entre la relation emploi et la relation personne.
09° Quelle requête doit-on faire pour obtenir le tconst du titre, le nom de la personne occupant un emploi dans ce film, et le poste qu'elle occupe ?
...CORRECTION...
1
2
3 |
|
4 - Double jonction
On remarquera que la relation emploi est particulière puisqu'elle contient deux clés étrangères. Elle joue un rôle central dans les jonctions entre les titres et les personnes qui y ont participé : titre - emploi - personne

En réalité, on peut utiliser plusieurs fois JOIN dans une requête. Nous allons donc pouvoir faire la liaison entre un titre et les gens qui ont travaillé sur ce projet.
10° Expliquer clairement ce que fait cette requête.
1
2
3
4 |
|
...CORRECTION...
On va créer des nuplets en utilisant les trois tables à la fois.
L2 : On commence par récupérer les attributs de la table emploi.
L3 : On rajoute ensuite les attributs de la relation personne (en cherchant une liaison de type valeur de clé étrangère = valeur de clé primaire).
L4 : On rajoute finalement les attributs de la relation titre (en cherchant une liaison de type valeur de clé étrangère = valeur de clé primaire).
L1 : On ne ramène pas la totalité des attributs dans les nuplets obtenus, mais juste les attributs fournis sur cette première ligne.
11° Filtrer les résultats précédents pour obtenir uniquement les films des années 2000 précisement.
Pensez à réaliser au préalable une requête SELECT DISTINCT pour obtenir les catégories disponibles.
...CORRECTION...
1
2
3
4
5 |
|
AS
Le nom des relations sont parfois assez longues à taper. On peut utiliser des alias de façon à simplifier les notations.
1
2
3
4
5 |
|
5 - Autres exercices
12° Comptabiliser le nombre d'emplois où on dispose à la fois d'informations sur le film et les personnes qui y ont participé.
Il suffit donc de faire la jointure entre les trois tables et d'utiliser SELECT pour récupérer uniquement la clé primaire de emploi.
Une fois que cela marche, il suffit d'utiliser un COUNT sur cet attribut.
...CORRECTION...
1
2
3
4
5 |
|
13° Comptabiliser le nombre de films de plus de 180 minutes pour lesquels on dispose d'employés : il suffit de chercher les valeurs DISCTINCTES de l'un des attributs de cette jointure titre-emploi et de les compter.
...CORRECTION...
1
2
3
4
5 |
|
14° Obtenir la liste des titres dans lesquels Bruce Willis a participé d'une façon où d'une autre. Vous pourriez utiliser une recherche de ce type :
5 |
|
...CORRECTION...
1
2
3
4
5 |
|
15° Obtenir la liste des titres dans lesquels Bruce Willis a participé en tant qu'acteur. On affichera également les rôles qu'il incarne dans ces films.
...CORRECTION...
1
2
3
4
5 |
|
6 - FAQ
Rien pour le moment
Activité publiée le 20 01 2021
Dernière modification : 24 01 2021
Auteur : ows. h.