Apprendre les jointures avec des exemples réels de SQL

L'instruction JOIN vous permet de travailler avec des données stockées dans plusieurs tables. Dans cet article, je vais vous présenter les clauses JOIN à l'aide d'exemples SQL réels.

Imaginez que vous ne puissiez travailler qu'avec une seule table de base de données à la fois. Heureusement, nous n'avons pas à nous inquiéter de ce problème. Une fois que vous avez appris l'instruction JOIN, vous pouvez commencer à relier les données entre elles. Dans cet article, j'utiliserai des exemples SQL réels pour illustrer la manière dont nous utilisons JOINs, le fonctionnement de chaque type de JOIN et le moment où il convient d'utiliser chaque type. De plus, je vous donnerai quelques conseils qui vous permettront d'éviter de commettre des erreurs courantes lorsque vous utilisez JOINs.

Qu'est-ce que la clause SQL JOIN ?

La clause JOIN nous permet de combiner les colonnes de deux ou plusieurs tables sur la base de valeurs de colonnes partagées. Je vais l'expliquer à l'aide d'exemples SQL en temps réel. Voici le premier : disons que nous avons une table students qui contient les noms des étudiants, leurs noms d'utilisateur respectifs et un numéro d'identification. Nous avons également une table "commentaires" qui stocke tous les comments étudiants ont posté dans un forum. Voici les deux tables.

Ajoutons quelques données de test :

INSERT INTO `students` VALUES
(1,'Jhon','jj2005'),(2,'Albert','salbert'),(3,'Mathew','powermath'),
(4,'Lisa','lisabbc'),(5,'Sandy','imsandyw'),(6,'Tamara','tamy21');

INSERT INTO 'comments' VALUES 
(1,'jj2005','Awesome!'),(2,'jj2005','This is great :)'),
(3,'powermath','Hmmm...'),(4,'imsandyw','Let\'s wait a moment'),
(5,'lisabbc','Sure thing'),(6,'lisabbc','wow!'),
(7,'lisabbc','lol :) :) :)');

Comme vous pouvez le constater, les deux tables ont en commun la colonne forum_username. Par conséquent, cette colonne peut être utilisée dans une instruction JOIN pour relier les deux tableaux.

Par exemple, si nous voulions connaître le nom réel de l'étudiant pour chacun des commentaires du forum, nous écririons cette requête JOIN :

SELECT students.name, comments.forum_username, comments.comment
FROM students 
INNER JOIN comments
  ON students.forum_username = comments.forum_username
ORDER BY students.name ASC;

Les résultats ressembleraient à ceci :

Remarque : j'ai volontairement utilisé forum_username pour illustrer le concept de JOIN, mais dans la pratique, vous utiliserez la clé primaire (dans ce cas, la colonne id de la table students ) pour relier les tables.

Apprendre à connaître les types de jointures

Il existe plusieurs types de JOINs. Passons-les rapidement en revue :

INNER JOIN : Cette JOIN renvoie les enregistrements qui ont une correspondance dans les deux tables sur la base du prédicat de jointure (qui vient après le mot-clé ON ). Il s'agit de la même JOIN que celle utilisée dans l'exemple précédent. Le mot-clé INNER est facultatif.

LEFT [OUTER] JOIN : Cette méthode renvoie tous les enregistrements de la table de gauche (c'est-à-dire la table que vous avez citée en premier dans JOIN) et uniquement les enregistrements correspondants de la table de droite (c'est-à-dire la deuxième). Le mot-clé OUTER est facultatif.

Pour en revenir à nos exemples

SELECT students.name, comments.forum_username, comments.comment
FROM students 
LEFT JOIN comments
  ON students.forum_username = comments.forum_username
ORDER BY students.name ASC;

L'adresse LEFT JOIN renverrait un enregistrement pour tous les étudiants - y compris Tamara et Albert, qui n'ont pas de commentaires et ne seraient pas répertoriés dans les résultats d'une INNER JOIN. Notez également que dans cet exemple LEFT JOIN, les résultats de la colonne comment de Tamara et Albert seront NULL.

JOINT [OUTER] DROIT : il s'agit de l'inverse de LEFT JOIN; il renvoie tous les enregistrements de la table de droite (deuxième table) et uniquement ceux qui ont une correspondance dans la table de gauche (première table).

Une requête similaire à l'exemple

SELECT 
  students.name,
  comments.forum_username,
  comments.comment
FROM students 
RIGHT JOIN comments
  ON students.forum_username = comments.forum_username
ORDER BY students.name ASC;

FULL [OUTER] JOIN : Il s'agit essentiellement de la combinaison d'un LEFT JOIN et d'un RIGHT JOIN. Le jeu de résultats comprendra toutes les lignes des deux tables, en remplissant les colonnes avec les valeurs de la table lorsque cela est possible ou avec des NULL lorsqu'il n'y a pas de correspondance dans la table de contrepartie. Ce n'est pas un JOIN que vous utiliserez très souvent dans la vie réelle. Remarque : MySQL ne dispose pas de cette instruction, mais un résultat similaire peut être obtenu en utilisant le UNION de LEFT JOIN et RIGHT JOIN.

Dans le cas de nos exemples SQL du monde réel, il est intéressant de noter que dans la clause suivante FULL JOIN nous fournissons le mot-clé ON comme nous le faisons dans LEFT ou RIGHT JOINs :

SELECT *
FROM students 
FULL OUTER JOIN comments
  ON students.forum_username = comments.forum_username
ORDER BY students.name ASC;

CROSS JOIN : Il s'agit d'un autre type de jointure que vous n'utiliserez pas si souvent - dans ce cas, parce qu'il récupère le produit cartésien des deux tables. En gros, cela vous donne la combinaison de tous les enregistrements des deux tables. CROSS JOIN n'applique pas de prédicat (il n'y a pas de mot-clé ON ), mais il est toujours possible de filtrer les lignes en utilisant WHERE. Cela peut rendre le jeu de résultats équivalent à un INNER JOIN. Dans MySQL, JOIN, CROSS JOIN et INNER JOIN sont des équivalents syntaxiques, c'est-à-dire qu'ils peuvent se remplacer les uns les autres.

Ci-dessous, il n'y a pas de clause ON pour filtrer les résultats. Toutes les combinaisons possibles des deux tables seront affichées dans le jeu de résultats :

SELECT *
FROM students 
CROSS JOIN comments 
ORDER BY students.name ASC;

Vous pouvez en savoir plus sur les types de Les jointures en SQL sur notre chaîne YouTube - We Learn SQL. N'oubliez pas de cliquer sur "subscribe".

JOINs en SQL - Exemples en temps réel

Le nombre de scénarios qui nécessitent un JOIN est infini, mais certains scénarios apparaissent plus souvent. Au lieu de passer en revue l'exemple typique table1/table2, je préfère vous présenter des exemples SQL du monde réel. Nous pouvons les utiliser pour obtenir des conseils pratiques.

La relation entre le grand-père, le père et le fils

Un scénario courant en temps réel traite de données qui suivent ce type de relation. Par exemple, un site user se trouve dans un site city qui appartient à un site state. Les tables (avec plus ou moins de colonnes) ressemblent à ceci :

Ajoutons quelques données :

INSERT INTO `states` VALUES (3,'California'),(2,'Florida'),(1,'Nevada');

INSERT INTO `cities` VALUES (1,1,'Miami'),(2,1,'Orlando'),
(3,2,'Las Vegas'),(4,2,'Coyote Springs');

INSERT INTO `users` VALUES (1,1,'Jhon','Doe'),(2,1,'Albert','Thomson'),
(3,2,'Robert','Ford'),(4,3,'Samantha','Simpson');

Pour obtenir la liste complète des utilisateurs d'une ville et d'un état donnés, nous devrons joindre la table des fils (User) avec leur père (City) et leur grand-père (State).

SELECT 
  User.first_name,
  user.last_name,
  City.cityname,
  State.statename
FROM users User
INNER JOIN cities City
  ON User.city_id = City.id
INNER JOIN states State
  ON City.state_id = State.id;

Nous disposons déjà de quelques astuces utiles :

• Les colonnes utilisées pour lier les tables doivent être indexées pour une meilleure performance.
• Lorsque les colonnes qui relient les tables (comme dans l'exemple précédent) pointent vers la clé primaire de la table liée, nous parlons alors de clés étrangères. Dans ce cas, il est préférable d'inclure cette relation dans la définition de votre table ; cela augmentera les performances. Dans MySQL, vous pouvez créer une clé étrangère users/city comme suit :

  ALTER TABLE `users` 
  ADD INDEX `fk_city_idx` (`city_id` ASC);
  ALTER TABLE `users` 
  ADD CONSTRAINT `fk_city`
    FOREIGN KEY (`city_id`)
    REFERENCES `cities` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE;
  

  Cela vous donne l'avantage supplémentaire de la vérification d'intégrité qui sera effectuée par le moteur lorsque les données de ces tables sont mises à jour ou supprimées.

Supposons que nous voulions trouver tous les utilisateurs d'un état. Vous ajoutez une condition de filtrage à la requête, comme indiqué ci-dessous :

SELECT 
  User.first_name,
  user.last_name,
  City.cityname,
  State.statename
FROM users User
INNER JOIN cities City
  ON User.city_id = City.id
INNER JOIN states State
  ON City.state_id = State.id
  AND State.statename = 'Nevada';

Ou vous pouvez même utiliser une jointure implicite (indiquée en gras), comme ceci :

SELECT 
  User.first_name,
  user.last_name,
  City.cityname,
  State.statename
FROM
   users User,
   cities City,
   states State
WHERE User.city_id = City.id
  AND City.state_id = State.id
  AND State.statename = 'Nevada';

Mais je vous suggère d'écrire explicitement le site JOIN et de garder les critères de jointure et les conditions de filtrage séparés :

SELECT 
  User.first_name,
  user.last_name,
  City.cityname,
  State.statename
FROM users User
INNER JOIN cities City
  ON User.city_id = City.id
INNER JOIN states State
  ON City.state_id = State.id
WHERE State.statename = 'Nevada';

Quelques concepts importants :

• Nous pouvons voir comment la table fils (user) est filtrée sur la base des conditions utilisées dans la table grand-père (state). En d'autres termes, les utilisateurs sont basés sur un état donné. De même, nous aurions pu filtrer les résultats sur la base de la table père (city) et obtenir une liste d'utilisateurs basée sur une ville donnée.
• En règle générale, les prédicats de JOIN (les conditions après le mot-clé ON ) doivent être utilisés uniquement pour la relation de jointure. Laissez le reste des conditions de filtrage dans la section WHERE. Cela simplifiera la lisibilité des requêtes et la maintenance future du code.

Inversement, nous pourrions renvoyer les états en fonction de certains critères. Par exemple, nous pourrions récupérer les états qui ont au moins un utilisateur appartenant à cet état :

SELECT 
  DISTINCT State.statename
FROM states State
INNER JOIN cities City
  ON City.state_id = State.id
INNER JOIN users User
  ON User.city_id = City.id

Qu'est-ce que nous apprenons ici ?

• INNER JOIN supprime tous les enregistrements "inutilisés" ou non appariés (enregistrements sans correspondance des deux côtés de JOIN).
• DISTINCT filtre les enregistrements en double. Comme il peut y avoir plusieurs utilisateurs pour un état, si nous n'utilisions pas DISTINCT, nous obtiendrions autant d'états répétés que d'utilisateurs lui appartenant. (Le même effet de filtrage pourrait également être obtenu en utilisant GROUP BY.)
• Un filtrage plus poussé pourrait être réalisé en ajoutant les conditions de WHERE.

La relation Many-to-Many

Vous voulez d'autres exemples SQL en temps réel ? Un autre scénario typique pour JOINs est lorsque les enregistrements sont liés les uns aux autres d'une manière "many-to-many" ou N-to-N. Supposons que vous ayez un système dans lequel vous créez des badges qui sont attribués aux utilisateurs. Dans ce cas, un utilisateur a des badges et, en même temps, un badge a des utilisateurs. Ces relations nécessiteront une troisième table qui reliera les clés primaires de users et badges. Elle ressemblera à ceci :

Ajoutons quelques exemples de données :

INSERT INTO `badges` VALUES 
(1,'gold heart',100),(2,'silver heart',50),(3,'metal heart',10),(4,'star',5),(5,'wood',2),(6,'dust',1);
INSERT INTO `users` VALUES (1,'Robert','Williams'),(2,'Anthony','McPeters'),(3,'Tania','Krugger'),(4,'JJ','Richards'),(5,'Katy','Thomas');
INSERT INTO `badges_users` VALUES
(1,1,1),(2,4,1),(3,4,2),(4,4,2),(5,4,2),(6,3,3),(7,3,3),(8,2,4);

Comment un JOIN peut-il nous récupérer tous les utilisateurs avec leurs badges respectifs ?

SELECT 
  User.first_name,
  User.last_name,
  BU.user_id,
  BU.badge_id,
  Badge.badge_name,
  Badge.badge_points
FROM users User
LEFT JOIN badges_users B
  ON User.id = BU.user_id
LEFT JOIN badges Badge
  ON BU.badge_id = Badge.id
ORDER BY Badge.badge_points DESC

Voici quelques éléments à retenir concernant ce type de requête :

• Nous avons volontairement utilisé LEFT JOIN ici parce qu'il montrera les utilisateurs qui n'ont pas de badge du tout. Si nous avions utilisé une INNER JOIN ou une jointure interne implicite (en définissant les égalités des ID dans une WHERE), les utilisateurs qui n'ont pas de badge ne seraient pas inclus dans les résultats. Si vous voulez exclure ces utilisateurs, vous devez utiliser une INNER JOIN.
• De même, l'utilisation d'un LEFT JOIN signifie que les badges non utilisés ne seront pas répertoriés ; nous nous concentrons sur les utilisateurs et non sur les badges.
• Enfin, n'oubliez pas d'indexer correctement la table intermédiaire (badges_users). Toutes ses clés étrangères doivent être définies.

Récupérons maintenant tous les badges qui ont au moins un utilisateur. Exprimé d'une autre manière, ce sont les badges qui ont été utilisés au moins une fois

SELECT DISTINCT Badge.badge_name
FROM badges Badge
LEFT JOIN badges_users BadgeUser
  ON Badge.id = BadgeUser.badge_id

Et si on voulait obtenir tous les badges inutilisés, la requête devient :

SELECT Badge.badge_name, Badge.badge_points
FROM badges Badge
LEFT JOIN badges_users BadgeUser
  ON Badge.id = BadgeUser.badge_id
WHERE BadgeUser.badge_id IS NULL

Remarquez que :

• Il n'est pas toujours nécessaire de joindre les trois tables. En ne joignant que la table intermédiaire (badges_users), qui contient les références aux tables user et badge nous pouvons toujours exécuter nos requêtes avec succès.
• La table intermédiaire peut également être utilisée pour enregistrer des informations supplémentaires. Par exemple, elle pourrait stocker la valeur de l'horodatage lorsqu'un utilisateur a reçu un certain badge.

Essayez vous-même

Vous serez probablement confronté quotidiennement à ce type de situations lorsque vous traiterez des tables contenant des données connexes. Je vous recommande vivement d'examiner les exemples SQL du monde réel ci-dessus et de les tester avec les données réelles. Cela vous permettra de mieux comprendre les concepts en jeu.

Si vous avez des commentaires sur nos exemples SQL réels ou vos propres idées sur d'autres clauses JOIN réelles, n'hésitez pas à les partager afin que nous puissions tous continuer à apprendre !