27 - SQL : JOIN ON

⏪

⏬

⏩

1 - Principe de la jointure

2 - emploi-titre

3 - emploi-personne

4 - double

5 - Autres exercices

6 - FAQ

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 :

La clé étrangère permet de faire le lien entre la table jeu et la table console

01° Est-il normal que les valeurs de l'attribut id de la relation jeu soient toutes différentes ?

Correction ?

...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 ?

La relation console contient les informations sur les consoles

Correction ?

...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 :

On rajoute les infos sur la console en plus des infos sur le jeu lui-même

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	id_support
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

SELECT *
FROM jeu
JOIN support

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 ?

...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	id_support
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

SELECT *
FROM jeu
JOIN support ON jeu.id_support = support.id

On obtient alors ceci :

id	jeu	description	sortie	editeur	genre	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

SELECT jeu.jeu, support.name
FROM jeu
JOIN support ON jeu.id_support = support.id

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 ?

...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

SELECT *
FROM titre
WHERE NULL = NULL

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

SELECT *
FROM titre
WHERE NULL is NULL

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

CREATE TABLE personne (
    id  INTEGER,
    nconst  VARCHAR(12) UNIQUE,
    name    VARCHAR(50),
    birth_year  INTEGER CHECK(birth_year IS NULL OR (birth_year > 1000 AND birth_year < 3000)),
    death_year  INTEGER CHECK(death_year IS NULL OR (death_year > 1000 AND death_year < 3000)),
    primary_profession  TEXT,
    known_for_titles    TEXT,
    PRIMARY KEY(nconst)
);
 
CREATE TABLE titre (
    id  INTEGER,
    tconst  VARCHAR(9) UNIQUE,
    title_type  VARCHAR(15),
    primary_title   VARCHAR(40),
    original_title  VARCHAR(40),
    start_year  INTEGER,
    end_year  INTEGER,
    runtime_minutes INTEGER,
    genres  TEXT,
    PRIMARY KEY(tconst)
);
 
CREATE TABLE emploi (
    id  INTEGER,
    tconst  VARCHAR(9),
    ordering    INTEGER,
    nconst  VARCHAR(12),
    category    VARCHAR(40),
    job VARCHAR(40),
    characters  VARCHAR(100),
    PRIMARY KEY(id),
    FOREIGN KEY (tconst) REFERENCES titre(tconst),
    FOREIGN KEY (nconst) REFERENCES personne(nconst)
);

05° Fournir le schéma relationnel de la relation titre en les écrivant sous forme abstraite. On utilisera donc les notations soulignements et #.

Correction ?

...CORRECTION...

titre (
    id  INTEGER,
    tconst  VARCHAR(9),
    title_type  VARCHAR(15),
    primary_title   VARCHAR(40),
    original_title  VARCHAR(40),
    start_year  INTEGER,
    end_year  INTEGER,
    runtime_minutes INTEGER,
    genres  TEXT
)

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 ?

...CORRECTION...

emploi (
    id  INTEGER,
    #tconst  VARCHAR(9),
    ordering  INTEGER,
    #nconst   VARCHAR(12),
    category  VARCHAR(40),
    job  VARCHAR(40),
    characters  VARCHAR(100),
)
    CLE ETRANGERE tconst en liaison avec titre(tconst)
    CLE ETRANGERE nconst en liaison avec personne(nconst)

JOIN titre ON titre.tconst = emploi.tconst

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 ?

...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

SELECT emploi.id, emploi.category, titre.primary_title, emploi.nconst
FROM emploi
JOIN titre ON titre.tconst = emploi.tconst

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 ?

...CORRECTION...

SELECT emploi.id, emploi.category, titre.primary_title, emploi.nconst, titre.start_year
FROM emploi
JOIN titre ON titre.tconst = emploi.tconst
WHERE titre.start_year > 2015
ORDER BY titre.start_year DESC

3 - Liaison emploi-personne

⇩ ⇧ ⤊

Nous allons maintenant faire le lien entre la relation emploi et la relation personne.

Les liaisons entre les 3 relations

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 ?

...CORRECTION...

1
2
3

SELECT emploi.tconst, personne.name, emploi.category
FROM emploi
JOIN personne ON emploi.nconst = personne.nconst

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.

SELECT titre.primary_title, personne.name, emploi.category
FROM emploi
JOIN personne ON emploi.nconst = personne.nconst
JOIN titre ON emploi.tconst = titre.tconst

Correction ?

...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 ?

...CORRECTION...

SELECT titre.primary_title, personne.name, emploi.category
FROM emploi
JOIN personne ON emploi.nconst = personne.nconst
JOIN titre ON emploi.tconst = titre.tconst
WHERE titre.start_year = 2000

AS

Le nom des relations sont parfois assez longues à taper. On peut utiliser des alias de façon à simplifier les notations.

SELECT t.primary_title, p.name, e.category
FROM emploi as e
JOIN personne AS p ON e.nconst = p.nconst
JOIN titre AS t ON e.tconst = t.tconst
WHERE t.title_type = "tvSeries"

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 ?

...CORRECTION...

SELECT COUNT(titre.primary_title)
FROM emploi
JOIN personne ON emploi.nconst = personne.nconst
JOIN titre ON emploi.tconst = titre.tconst
WHERE titre.title_type = "movie"

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 ?

...CORRECTION...

SELECT COUNT(DISTINCT titre.primary_title)
FROM emploi
JOIN personne ON emploi.nconst = personne.nconst
JOIN titre ON emploi.tconst = titre.tconst
WHERE titre.title_type = "movie"

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`	`WHERE personne.name LIKE '%Bruce Willis%'`

Correction ?

...CORRECTION...

SELECT DISTINCT titre.primary_title, titre.start_year
FROM emploi
JOIN personne ON emploi.nconst = personne.nconst
JOIN titre ON emploi.tconst = titre.tconst
WHERE personne.name LIKE '%Bruce Willis%'

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 ?

...CORRECTION...

SELECT DISTINCT titre.primary_title, titre.start_year, emploi.characters
FROM emploi
JOIN personne ON emploi.nconst = personne.nconst
JOIN titre ON emploi.tconst = titre.tconst
WHERE p.name LIKE '%Bruce Willis%' AND emploi.category = 'actor'

6 - FAQ

⇩ ⇧ ⤊

Rien pour le moment

Activité publiée le 20 01 2021
Dernière modification : 24 01 2021
Auteur : ows. h.

⏪

⏫

⏩