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

Documents de cours : open document ou pdf

MODE COURS (cacher les questions)

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

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 (la première "colonne") 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.

Il s'agit de la contrainte d'UNICITE.

02° Est-il normal que les valeurs de l'attribut id_support de la relation jeu (la dernière "colonne") ne soient pas toutes différentes ?

Comment se nomme l'attribut id de la relation console ? Peut-on avoir deux fois la même valeur cette fois ?

Correction ?

...CORRECTION...

Oui, on peut avoir plusieurs fois la même valeur : id_support est juste un attribut servant de clé étrangère.

Sa valeur doit correspondre à une valeur disponible en tant que clé primaire dans une autre table, ou NULL.

Il s'agit de la contrainte de REFERENCE.

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.

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, la clé primaire id :

de la table jeu pourra être noté jeu.id.
de la table support pourra être noté support.id.

Voici la concaténation des 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

Il reste maintenant à remplir les derniers champs de la table résultante. On ne va pas les remplir au hasard : nous allons expliquer comment faire la liaison entre les deux tables en explicitant quelle clé étrangère référence quelle clé primaire.

Clause JOIN ... ON ...

Lorsqu'on veut réunir les attributs de deux tables ou plus, il suffit d'utiliser

JOIN qui précise dans quelle table aller chercher les nouveaux attributs, puis
ON qui précise comment réaliser concrétement cette jointure : on y précise quelle clé étrangère doit être égale à quelle clé primaire.

Le principe est d'écrire quelque chose que ressemble à

JOINDRE les valeurs d'un nuplet de B LORSQUE la valeur d'une clé étrangère de A vaut la valeur d'une clé primaire de B.

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 effectue d'abord ceci :

1
2
3

SELECT *
FROM jeu
JOIN support ON ...

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	?	?	?
3	PITFALL!	Pitfall Harry, un explorateur, ramasse des objets éparpillés dans la jungle.	1982	Activision	Plate-formes	2	2	?	?	?
4	PITFALL!	Pitfall Harry, un explorateur, ramasse des objets éparpillés dans la jungle.	1983	Activision	Plate-formes	3	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	3	?	?	?

Il ne reste alors qu'à remplir correctement les champs en allant lire les valeurs de n-uplets en utilisant la valeur de la clé.

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 projeter uniquement les attributs avec SELECT :

1
2
3

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

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

2 - Jointure entre les tables emploi-titre

⇩ ⇧ ⤊

Voici le schéma relationnel (sous forme de graphique cette fois) 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 UNIQUE,
    nconst  VARCHAR(12),
    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 UNIQUE,
    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,
    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)
);

04° 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
)

05° 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

06° Ouvrir la base de données avec DBBrownser si ce n'est pas encore fait. Proposer la requête qui permet obtenir pour chaque emploi présent dans la relation emploi :

la clé primaire de cet emploi,
la catégorie de l'emploi,
le nom du titre (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

07° Quelle requête doit-on faire pour obtenir la même chose mais uniquement avec les films de 2016 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

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

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

10° 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

⇩ ⇧ ⤊

11° 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 les n-uplets récupérés.

Correction ?

...CORRECTION...

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

12° 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" AND runtime_minutes > 180

13° 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%'

Pourquoi DISTINCT ? Simplement pour ne pas comptabiliser deux fois un film où il aurait été acteur et producteur par exemple.

14° 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 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

⇩ ⇧ ⤊

NULL et None, c'est pareil ?

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

J'espère vous avoir montrer que None et Null ne caractérise pas le même type d'informations.

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

⏪

⏫

⏩