Principe des jointures
Sans surprise, le principe des jointures est de joindre plusieurs tables. Pour ce faire, on utilise les informations communes des tables.
Par exemple, lorsque nous avons ajouté dans notre base les informations sur les espèces (leur nom latin et leur description), je vous ai dit que ce serait une très mauvaise idée de tout mettre dans la tableAnimal, car il nous faudrait alors répéter la même description pour tous les chiens, la même pour toutes les tortues, etc.
Cependant, vous avez sans doute remarqué que du coup, si vous voulez afficher la description de l'espèce de Cartouche (votre petit préféré), vous avez besoin de deux requêtes.
Cependant, vous avez sans doute remarqué que du coup, si vous voulez afficher la description de l'espèce de Cartouche (votre petit préféré), vous avez besoin de deux requêtes.
Étape 1 : on trouve l'id de l'espèce de Cartouche grâce à la table Animal.
SELECT espece_id FROM Animal WHERE nom = 'Cartouche';
espece_id
|
|---|
1
|
Étape 2 : on trouve la description de l'espèce grâce à son id.
SELECT description FROM Espece WHERE id = 1;
description
|
|---|
Bestiole à quatre pattes qui aime les caresses et tire souvent la langue
|
Ne serait-ce pas merveilleux de pouvoir faire tout ça (et plus encore) en une seule requête ?
C'est là que les jointures entrent en jeu ; on va utiliser l'information commune entre les deux tables : l'idde l'espèce, qui est présente dans Animal avec la colonne espece_id, et dans Espece avec la colonne id.
C'est là que les jointures entrent en jeu ; on va utiliser l'information commune entre les deux tables : l'idde l'espèce, qui est présente dans Animal avec la colonne espece_id, et dans Espece avec la colonne id.
SELECT Espece.description
FROM Espece
INNER JOIN Animal
ON Espece.id = Animal.espece_id
WHERE Animal.nom = 'Cartouche';
Et voilà le travail !
description
|
|---|
Bestiole à quatre pattes qui aime les caresses et tire souvent la langue
|
En fait, lorsque l'on fait une jointure, on crée une table virtuelle et temporaire qui reprend les colonnes des tables liées. Le schéma ci-dessous illustre ce principe.
Au départ, on a deux tables : Animal (id, sexe, nom, race_id, espece_id) et Espece (id, nom_courant,nom_latin). Les deux premières lignes d'Animal correspondent à la première ligne d'Espece, et la troisième ligne d'Animal à la deuxième ligne d'Espece. Une fois les deux tables jointes, on obtient une table possédant toutes les colonnes d'Animal et toutes les colonnes d'Espece, avec les valeurs correspondantes de chaque table. On peut voir que les cinquième et sixième colonnes de la table de jointure ont les mêmes valeurs.
Ensuite, de cette table virtuelle, on peut extraire ce que l'on veut. La colonne nom_latin pour la ligne ayant "Caribou" dans la colonne nom, par exemple.
Ensuite, de cette table virtuelle, on peut extraire ce que l'on veut. La colonne nom_latin pour la ligne ayant "Caribou" dans la colonne nom, par exemple.
Notion d'alias
Je fais ici une petite parenthèse avant de vous expliquer en détail le fonctionnement des jointures pour vous parler d'un petit truc bien utile : les alias.
Les alias sont des noms de remplacement, que l'on donne de manière temporaire (le temps d'une requête en fait) à une colonne, une table, une donnée. Les alias sont introduits par le mot-clé
AS. Ce mot-clé est facultatif, vous pouvez très bien définir un alias sans utiliser AS, mais je préfère personnellement toujours le mettre. Je trouve qu'on y voit plus clair.
Comment ça marche ?
Prenons cette requête toute simple :
SELECT 5+3;
5+3
|
|---|
8
|
Imaginons que ce calcul savant représente en fait le nombre de chiots de Cartouche, qui a eu une première portée de 5 chiots, et une seconde de seulement 3 chiots. Nous voudrions donc indiquer qu'il s'agit bien de ça, et non pas d'un calcul inutile destiné simplement à illustrer une notion obscure.
Facile ! Il suffit d'utiliser les alias :
SELECT 5+3 AS Chiots_Cartouche;
-- OU, sans utiliser AS
SELECT 5+3 Chiots_Cartouche;
Chiots_Cartouche
|
|---|
8
|
Bon, tout ça c'est bien joli, mais pour l'instant ça n'a pas l'air très utile…
Prenons un exemple plus parlant : retournez voir le schéma qui explique le principe des jointures un peu plus haut. La table virtuelle résultant de la jointure des tables Espece et Animal possède plusieurs colonnes :
id sexe nom race_id espece_id id nom_courant nom_latin
Mais que vois-je ? J'ai deux colonnes id ! Comment faire pour les différencier ? Comment être sûr de savoir à quoi elles se rapportent ?
Avec les alias pardi ! Il suffit de donner l'alias espece_id à la colonne id de la table Espece, et animal_id à la colonne id de la table Animal.
Tout à coup, ça vous semble plus intéressant non ?
Prenons un exemple plus parlant : retournez voir le schéma qui explique le principe des jointures un peu plus haut. La table virtuelle résultant de la jointure des tables Espece et Animal possède plusieurs colonnes :
id sexe nom race_id espece_id id nom_courant nom_latin
Mais que vois-je ? J'ai deux colonnes id ! Comment faire pour les différencier ? Comment être sûr de savoir à quoi elles se rapportent ?
Avec les alias pardi ! Il suffit de donner l'alias espece_id à la colonne id de la table Espece, et animal_id à la colonne id de la table Animal.
Tout à coup, ça vous semble plus intéressant non ?
- id
- sexe
- nom
- race_id
- espece_id
- id
- nom_courant
- nom_latin
Je vais vous laisser sur ce sentiment. Il n'y a pas grand-chose de plus à dire sur les alias, vous en comprendrez toute l'utilité à travers les nombreux exemples dans la suite de ce cours. L'important pour l'instant est que vous sachiez que ça existe et comment les définir.
L'air de rien, dans l'introduction, je vous ai déjà montré comment faire une jointure. La première émotion passée, vous devriez vous être dit "Tiens, mais ça n'a pas l'air bien compliqué en fait, les jointures".
En effet, une fois que vous aurez compris comment réfléchir aux jointures, tout se fera tout seul. Personnellement, ça m'aide vraiment d'imaginer la table virtuelle créée par la jointure, et de travailler sur cette table pour tout ce qui est conditions, tris, etc.
En effet, une fois que vous aurez compris comment réfléchir aux jointures, tout se fera tout seul. Personnellement, ça m'aide vraiment d'imaginer la table virtuelle créée par la jointure, et de travailler sur cette table pour tout ce qui est conditions, tris, etc.
Revoici la jointure que je vous ai fait faire, et qui est en fait une jointure interne.
SELECT Espece.description
FROM Espece
INNER JOIN Animal
ON Espece.id = Animal.espece_id
WHERE Animal.nom = 'Cartouche';
Décomposons !
SELECT Espece.description: je sélectionne la colonne description de la table Espece.FROM Espece: je travaille sur la table Espece.INNER JOIN Animal: je la joins (avec une jointure interne) à la table Animal.ON Espece.id = Animal.espece_id: la jointure se fait sur les colonnes id de la table Especeet espece_id de la table Animal, qui doivent donc correspondre.WHERE Animal.nom = 'Cartouche': dans la table résultant de la jointure, je sélectionne les lignes qui ont la valeur "Cartouche" dans la colonne nom venant de la table Animal.
Si vous avez compris ça, vous avez tout compris !
Syntaxe
Comme d'habitude, voici donc la syntaxe à utiliser pour faire des requêtes avec jointure(s) interne(s).
SELECT * -- comme d'habitude, vous sélectionnez les colonnes que vous voulez
FROM nom_table1
[INNER] JOIN nom_table2 -- INNER explicite le fait qu'il s'agit d'une jointure interne, mais c'est facultatif
ON colonne_table1 = colonne_table2 -- sur quelles colonnes se fait la jointure
-- vous pouvez mettre colonne_table2 = colonne_table1, l'ordre n'a pas d'importance
[WHERE ...]
[ORDER BY ...] -- les clauses habituelles sont bien sûr utilisables !
[LIMIT ...]
Condition de jointure
La clause
ON sert à préciser la condition de la jointure. C'est-à-dire sur quel(s) critère(s) les deux tables doivent être jointes. Dans la plupart des cas, il s'agira d'une condition d'égalité simple, comme ON Animal.espece_id = Espece.id. Il est cependant tout à fait possible d'avoir plusieurs conditions à remplir pour lier les deux tables. On utilise alors les opérateurs logiques habituels. Par exemple, une jointure peut très bien se faire sur plusieurs colonnes :
SELECT *
FROM nom_table1
INNER JOIN nom_table2
ON colonne1_table1 = colonne1_table2
AND colonne2_table1 = colonne2_table2
[AND ...];
Expliciter le nom des colonnes
Il peut arriver que vous ayez dans vos deux tables des colonnes portant le même nom. C'est le cas dans notre exemple, puisque la table Animal comporte une colonne id, tout comme la table Espece. Il est donc important de préciser de quelle colonne on parle dans ce cas-là.
Vous l'avez vu dans notre requête, on utilise pour ça l'opérateur . (nom_table.nom_colonne). Pour les colonnes ayant un nom non-ambigu (qui n'existe dans aucune autre table de la jointure), il n'est pas obligatoire de préciser la table.
En général, je précise la table quand il s'agit de grosses requêtes avec plusieurs jointures. En revanche, pour les petites jointures courantes, il est vrai que c'est moins long à écrire si on ne précise pas la table.
Vous l'avez vu dans notre requête, on utilise pour ça l'opérateur . (nom_table.nom_colonne). Pour les colonnes ayant un nom non-ambigu (qui n'existe dans aucune autre table de la jointure), il n'est pas obligatoire de préciser la table.
En général, je précise la table quand il s'agit de grosses requêtes avec plusieurs jointures. En revanche, pour les petites jointures courantes, il est vrai que c'est moins long à écrire si on ne précise pas la table.
Exemple : sélection du nom des animaux commençant par "Ch", ainsi que de l'id et la description de leur espèce.
SELECT Espece.id, -- ici, pas le choix, il faut préciser
Espece.description, -- ici, on pourrait mettre juste description
Animal.nom -- idem, la précision n'est pas obligatoire. C'est cependant plus clair puisque les espèces ont un nom aussi
FROM Espece
INNER JOIN Animal
ON Espece.id = Animal.espece_id
WHERE Animal.nom LIKE 'Ch%';
id
|
description
|
nom
|
|---|---|---|
2
|
Bestiole à quatre pattes qui saute très haut et grimpe aux arbres
|
Choupi
|
3
|
Bestiole avec une carapace très dure
|
Cheli
|
3
|
Bestiole avec une carapace très dure
|
Chicaca
|
Utiliser les alias
Les alias sont souvent utilisés avec les jointures. Ils permettent notamment de renommer les tables, et ainsi d'écrire moins de code.
Exemple : on renomme la table Espece "e", et la table Animal "a".
SELECT e.id,
e.description,
a.nom
FROM Espece AS e -- On donne l'alias "e" à Espece
INNER JOIN Animal AS a -- et l'alias "a" à Animal.
ON e.id = a.espece_id
WHERE a.nom LIKE 'Ch%';
Comme vous le voyez, le code est plus compact. Ici encore, c'est quelque chose que j'utilise souvent pour de petites requêtes ponctuelles. Par contre, pour de grosses requêtes, je préfère les noms explicites ; c'est ainsi plus facile de s'y retrouver.
Une autre utilité des alias est de renommer les colonnes pour que le résultat soit plus clair. Observez le résultat de la requête précédente. Vous avez trois colonnes : id, description et nom. Le nom de la table dont provient la colonne n'est indiqué nulle part. A priori, vous savez ce que vous avez demandé, surtout qu'il n'y a pas encore trop de colonnes, mais imaginez que vous sélectionniez une vingtaine de colonnes. Ce serait quand même mieux de savoir de quel id on parle, s'il s'agit du nom de l'animal, de son maître, du père, du fils ou du Saint-Esprit !
Il est intéressant là aussi d'utiliser les alias.
Il est intéressant là aussi d'utiliser les alias.
Exemple : on donne des alias aux colonnes (id_espece pour id de la table Espece, description_especepour Espece.description et nom_bestiole pour Animal.nom).
SELECT Espece.id AS id_espece,
Espece.description AS description_espece,
Animal.nom AS nom_bestiole
FROM Espece
INNER JOIN Animal
ON Espece.id = Animal.espece_id
WHERE Animal.nom LIKE 'Ch%';
id_espece
|
description_espece
|
nom_bestiole
|
|---|---|---|
2
|
Bestiole à quatre pattes qui saute très haut et grimpe aux arbres
|
Choupi
|
3
|
Bestiole avec une carapace très dure
|
Cheli
|
3
|
Bestiole avec une carapace très dure
|
Chicaca
|
C'est tout de suite plus clair !
Pourquoi "interne" ?
INNER JOIN permet donc de faire une jointure interne sur deux tables. Mais que signifie donc ce "interne" ?
C'est très simple ! Lorsque l'on fait une jointure interne, cela veut dire qu'on exige qu'il y ait des données de part et d'autre de la jointure. Donc, si l'on fait une jointure sur la colonne a de la table A et la colonneb de la table B :
SELECT *
FROM A
INNER JOIN B
ON A.a = B.b
Ceci retournera uniquement les lignes pour lesquelles A.a et B.b correspondent.
Exemple : on veut connaître la race des chats :
SELECT Animal.nom AS nom_animal, Race.nom AS race
FROM Animal
INNER JOIN Race
ON Animal.race_id = Race.id
WHERE Animal.espece_id = 2 -- ceci correspond aux chats
ORDER BY Race.nom, Animal.nom;
nom_animal
|
race
|
|---|---|
Callune
|
Bleu russe
|
Caribou
|
Bleu russe
|
Cawette
|
Bleu russe
|
Feta
|
Bleu russe
|
Filou
|
Bleu russe
|
Raccou
|
Bleu russe
|
Schtroumpfette
|
Bleu russe
|
Bagherra
|
Maine coon
|
Bilba
|
Maine coon
|
Capou
|
Maine coon
|
Cracotte
|
Maine coon
|
Farceur
|
Maine coon
|
Milla
|
Maine coon
|
Zara
|
Maine coon
|
Zonko
|
Maine coon
|
Boucan
|
Singapura
|
Boule
| |
Fiero
|
Singapura
|
On peut voir ici que les chats Choupi et Roucky pour lesquels je n'ai pas d'information sur la race (race_id est
NULL), ne sont pas repris dans les résultats. De même, aucun des chats n'est de la race "Sphynx", celle-ci n'est donc pas reprise. Si je veux les inclure, je dois utiliser une jointure externe.
