Données SQL Join

Identification

Infoforall

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 :

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

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

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

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

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 :

Les liaisons entre les 3 relations

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

1 2 3 4 5 6 7 8 9 10 11
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...

1 2 3 4 5 6 7 8 9
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 :

  1. la clé primaire de cet emploi,
  2. la catégorie de l'emploi,
  3. le nom du titre (le "primary_title", pas juste son identifiant) et
  4. 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
SELECT emploi.id, emploi.category, titre.primary_title, emploi.nconst FROM emploi JOIN titre ON titre.tconst = emploi.tconst
x

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

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

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

Les liaisons entre les 3 relations

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

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

1 2 3 4 5
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...

1 2 3 4 5
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...

1 2 3 4 5
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...

1 2 3 4 5
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...

1 2 3 4 5
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

On retiendra simplement la structure de base à base de

1 2 3 4 5
SELECT * FROM table_a JOIN table_b ON table_a.cle_etrangere = table_b.cle_primaire WHERE condition(s) ORDER BY expression
  1. SELECT : on choisit les attributs qu'on veut réellement récupérer dans les nuplets obtenus par FROM WHERE. On peut également faire des calculs (agrégation) sur ces nuplets avec MIN, MAX, AVG, SUM ou COUNT. L'utilisation de l'étoile * veut dire de récupérer l'intégralité des attributs disponibles sur les nuplets obtenus.
  2. FROM : on indique la table dans laquelle on veut récupèrer des nuplets
  3. JOIN ON : on indique la table qu'on veut fusionner avec la précédente et on précise quelle clé primaire comparer à quelle clé étrangère
  4. WHERE : on fournit les conditions à respecter pour qu'un nuplet soit sélectionné. On pourra utiliser AND, OR, LIKE...
  5. ORDER BY : on fournit l'attribut de tri avec un suffixe ASC ou DESC

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