Création de jointures SQL

Nous allons pouvoir effectuer différents types de jointures SQL qui vont nous permettre de récupérer plus ou moins de données dans une table ou dans une autre.

Dans cette deuxième leçon consacrée aux jointures, nous allons découvrir les différents types de jointures à notre disposition en MySQL et comprendre comment et quand utiliser une jointure plutôt qu’une autre.

 

Les différents types de jointures

Il existe différents types de jointures en SQL mais tous ne sont pas supportés par le MySQL. Nous allons donc simplement nous concentrer sur les types de jointures suivants dans ce cours :

  • L’INNER JOIN ;
  • Le LEFT (OUTER) JOIN ;
  • Le RIGHT (OUTER) JOIN ;
  • Le CROSS JOIN ;
  • Le SELF JOIN ;

Nous discuterons également d’un dernier type de jointures SQL qu’est le FULL (OUTER) JOIN qui n’est toujours pas utilisable en l’état en MySQL mais qu’on va pouvoir émuler en utilisant d’autres ressources.

Selon l’opération que nous voulons effectuer ou les données que nous voulons récupérer, nous utiliserons un type de jointure plutôt qu’un autre.

Les jointures de type interne, par exemple, ne vont nous retourner des résultats que lorsqu’il y aura une correspondance entre les deux tables, tandis que les jointures de type externe vont nous retourner des données même lorsqu’il n’y aura pas de correspondance dans la seconde table.

 

Préparation à l’utilisation des jointures : création d’une deuxième table

Avant tout, pour pouvoir tester de manière concrète les différents types de jointures et en apprécier tout l’intérêt, nous allons déjà créer une deuxième table dans notre base de données « cours » : la table « comments ».

Cette table va être composée de 4 colonnes :

  • Une colonne « id » INT UNSIGNED AUTO_INCREMENT PRIMARY KEY ;
  • Une colonne « userId » INT DEFAULT 0 ;
  • Une colonne « contenu » TEXT NOT NULL ;
  • Une colonne dateComment TIMESTAMP ;

Vous pouvez trouver le script de création de la table et de l’insertion de quelques entrées ci-dessous. Notez que j’ai choisi de placer des valeurs au hasard dans la colonne « dateComment » pour plus de réalisme et pour pouvoir exploiter ces valeurs plus tard dans le script. Le format de date est ici « année-mois-jour heures:minutes:secondes ».

<!DOCTYPE html>
<html>
    <head>
        <title>Cours PHP / MySQL</title>
        <meta charset='utf-8'>
    </head>
    <body>
        <h1>Bases de données MySQL</h1>  
        <?php
            $servname = "localhost"; $dbname = "cours"; $user = "root"; $pass = "root";
            
            try{
                $dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
                $dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                
                //Crée la table comments
                $createTb = "CREATE TABLE comments(
                  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                  userId INT DEFAULT 0,
                  contenu TEXT NOT NULL,
                  dateComment TIMESTAMP
                )";
                $dbco->exec($createTb);
                
                $sth = $dbco->prepare("
                  INSERT INTO comments(userId, contenu, dateComment)
                  VALUES (:userId, :contenu, :dateC)
                  ");
                $sth->bindParam(':userId', $userId);
                $sth->bindParam(':contenu', $contenu);
                $sth->bindParam(':dateC', $dateC);
                
                //Insère des entrées avec des valeurs personnalisées
                $userId = 1; $contenu = "Super site, merci !"; $dateC = "2018-05-08 18:29:03";
                $sth->execute();
                $userId = 3; $contenu = "Bon cours"; $dateC = "2018-05-12 13:29:06";
                $sth->execute();
                $userId = 1; $contenu = "Ce cours est dur..."; $dateC = "2018-05-19 15:17:38";
                $sth->execute();
                $userId = ''; $contenu = "Bon prof !"; $dateC = "2018-05-24 08:31:03";
                $sth->execute();
                $userId = 2; $contenu = "Super contenu !"; $dateC = "2018-06-04 10:49:17";
                $sth->execute();
                $userId = 1; $contenu = "J'ai appris à dév"; $dateC = "2018-06-07 17:29:33";
                $sth->execute();
            }
                  
            catch(PDOException $e){
                echo "Erreur : " . $e->getMessage();
            }
        ?>
    </body>
</html>

 

Et voici donc les entrées qui devraient être créées dans votre table (vous pouvez aller vérifier cela dans phpMyAdmin) :

Création d'une table pour exemple d'utilisation des jointures SQL avec PDO PHP

Nous avons donc maintenant deux tables dans notre base de données « cours » : la table « users » et la table « comments ».

Notez que chacune de ces deux tables possède une colonne avec des Id utilisateurs. Comme vous vous en doutez, nous allons nous servir de ces valeurs pour effectuer nos jointures.

 

L’INNER JOIN

Nous allons pouvoir créer une jointure interne à l’aide du mot clef INNER JOIN en SQL.

Une jointure interne est un type de jointures qui va nous permettre de ne sélectionner que les donnes relatives aux entrées qui ont des valeurs identiques dans les deux tables.

Pour appliquer un INNER JOIN, nous allons avoir besoin d’une colonne de référence dans chacune des deux tables, c’est-à-dire de deux colonnes qui ont des valeurs qui représentent les mêmes choses.

Nous allons par exemple pouvoir récupérer tous les commentaires de tous les utilisateurs connus d’un coup :

<!DOCTYPE html>
<html>
    <head>
        <title>Cours PHP / MySQL</title>
        <meta charset='utf-8'>
    </head>
    <body>
        <h1>Bases de données MySQL</h1>  
        <?php
            $servname = "localhost"; $dbname = "cours"; $user = "root"; $pass = "root";
            
            try{
                $dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
                $dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                
                /*Sélectionne tous les users qui ont commenté et tous les
                 *commentaires liés à un user connu*/
                $sth = $dbco->prepare("
                  SELECT users.prenom, comments.contenu
                  FROM users
                  INNER JOIN comments ON users.id = comments.userId
                ");
                
                $sth-> execute();
                
                $resultat = $sth->fetchAll(PDO::FETCH_ASSOC);
                
                echo '<pre>';
                print_r($resultat);
                echo '</pre>';
            }
                  
            catch(PDOException $e){
                echo "Erreur : " . $e->getMessage();
            }
        ?>
    </body>
</html>

 

Exemple de création d'un inner join SQL avec PDO PHP

Ici, notre requête sélectionne toutes les données des colonnes « prenom » de la table « users » et « contenu » de la table « comments » pour les entrées où la valeur dans la colonne « id » de « users » trouve un équivalent dans la colonne « userId » de « comments ».

Les commentaires sans utilisateurs ET les utilisateurs qui n’ont pas commenté seront donc exclus des résultats renvoyés.

Notez bien la nouvelle syntaxe employée dans cet exemple : maintenant que nous travaillons avec plusieurs tables, nous ne pouvons plus nous permettre de simplement indiquer le nom d’une colonne seul car il pourrait y avoir ambiguïté dans le cas où les deux tables sur lesquelles nous travaillons aient une colonne portant le même nom. Nous écrirons donc désormais « nom_de_la_table.nom_de_la_colonne » pour éviter toute ambiguïté.

Note : Dans ce cours, nous allons nous contenter d’effectuer des jointures sur deux tables. Cependant, rien ne vous empêche d’aller récupérer des données dans trois tables à la fois par exemple. Pour cela, il suffira d’effectuer deux INNER JOIN dans la même requête : 1 premier sur une colonne commune aux tables 1 et 2 et un second sur une colonne commune aux tables 2 et 3.

 

Le LEFT (OUTER) JOIN

Le LEFT JOIN (également appelé LEFT OUTER JOIN) est un type de jointures externes.

Ce type de requête va nous permettre de récupérer toutes les données (relatives aux colonnes spécifiées) de la table de gauche c’est-à-dire de notre table de départ ainsi que les données de la table de droite (table sur laquelle on fait la jointure) qui ont une correspondance dans la table de gauche.

Avec ce type de requête, nous allons par exemple pouvoir récupérer tous les noms et prénoms de nos utilisateurs dans notre table « users » et SEULEMENT les commentaires liés à un utilisateur de notre table « comments ».

<!DOCTYPE html>
<html>
    <head>
        <title>Cours PHP / MySQL</title>
        <meta charset='utf-8'>
    </head>
    <body>
        <h1>Bases de données MySQL</h1>  
        <?php
            $servname = "localhost"; $dbname = "cours"; $user = "root"; $pass = "root";
            
            try{
                $dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
                $dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                
                /*Sélectionne tous les prenoms et noms dans la table users
                 *ainsi que les contenus des commentaires liés à un utilisateur
                 *de la table comments*/
                $sth = $dbco->prepare("
                  SELECT users.prenom, users.nom, comments.contenu
                  FROM users
                  LEFT OUTER JOIN comments ON users.id = comments.userId
                ");
                
                $sth-> execute();
                
                $resultat = $sth->fetchAll(PDO::FETCH_ASSOC);
                
                echo '<pre>';
                print_r($resultat);
                echo '</pre>';
            }
                  
            catch(PDOException $e){
                echo "Erreur : " . $e->getMessage();
            }
        ?>
    </body>
</html>

 

Exemple de création d'un left join SQL avec PDO PHP

Notez ici qu’il suffit « d’inverser » notre requête (c’est-à-dire utiliser notre table « comments » comme table de base et la table « users » comme table sur laquelle on fait la jointure) pour au contraire récupérer les contenus de tous les commentaires et UNIQUEMENT les noms et prénoms des utilisateurs qui ont commenté.

<!DOCTYPE html>
<html>
    <head>
        <title>Cours PHP / MySQL</title>
        <meta charset='utf-8'>
    </head>
    <body>
        <h1>Bases de données MySQL</h1>  
        <?php
            $servname = "localhost"; $dbname = "cours"; $user = "root"; $pass = "root";
            
            try{
                $dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
                $dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                
                /*Comments est désormais notre table de base et on effectue notre
                 *jointure sur la table users*/
                $sth = $dbco->prepare("
                  SELECT users.prenom, users.nom, comments.contenu
                  FROM comments
                  LEFT OUTER JOIN users ON users.id = comments.userId
                ");
                
                $sth-> execute();
                
                $resultat = $sth->fetchAll(PDO::FETCH_ASSOC);
                
                echo '<pre>';
                print_r($resultat);
                echo '</pre>';
            }
                  
            catch(PDOException $e){
                echo "Erreur : " . $e->getMessage();
            }
        ?>
    </body>
</html>

 

Exemple de création d'un left outer join SQL avec PDO PHP

Notez également qu’une petite astuce simple va nous permettre d’exclure les données qui ont une référence commune dans les deux tables et donc de ne récupérer que les données relatives à notre table de départ.

Pour cela, il va suffire d’utiliser une clause WHERE en demandant une valeur nulle sur la colonne de la table de droite (table sur laquelle on fait la jointure).

<!DOCTYPE html>
<html>
    <head>
        <title>Cours PHP / MySQL</title>
        <meta charset='utf-8'>
    </head>
    <body>
        <h1>Bases de données MySQL</h1>  
        <?php
            $servname = "localhost"; $dbname = "cours"; $user = "root"; $pass = "root";
            
            try{
                $dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
                $dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                
                /*Récupère les noms et prénoms des utilisateurs n'ayant pas
                 *commenté*/
                $sth = $dbco->prepare("
                  SELECT users.prenom, users.nom, comments.contenu
                  FROM users
                  LEFT OUTER JOIN comments ON users.id = comments.userId
                  WHERE comments.userId IS NULL
                ");
                
                $sth-> execute();
                
                $resultat = $sth->fetchAll(PDO::FETCH_ASSOC);
                
                echo '<pre>';
                print_r($resultat);
                echo '</pre>';
            }
                  
            catch(PDOException $e){
                echo "Erreur : " . $e->getMessage();
            }
        ?>
    </body>
</html>

 

Exemple de création d'un left outer join SQL avec clause where en PDO PHP

Ici, par exemple, nous récupérons seulement les noms et prénoms des utilisateurs n’ayant pas posté de commentaires.

 

Le RIGHT (OUTER) JOIN

Le RIGHT JOIN, encore appelé RIGHT OUTER JOIN est un autre type de jointures externes qui va fonctionner de la même façon qu’un LEFT JOIN à la différence que cette fois-ci ce sont toutes les données de la table de droite (table sur laquelle on effectue la jointure qui vont être récupérées tandis que seules les entrées de la table de gauche ou table de départ qui vont satisfaire à la condition de jointure seront sélectionnées.

La requête ci-dessous par exemple va bien récupérer le contenu de tous les commentaires dans la table « comments » sans exception et SEULEMENT les noms et prénoms des personnes qui ont posté un commentaire.

<!DOCTYPE html>
<html>
    <head>
        <title>Cours PHP / MySQL</title>
        <meta charset='utf-8'>
    </head>
    <body>
        <h1>Bases de données MySQL</h1>  
        <?php
            $servname = "localhost"; $dbname = "cours"; $user = "root"; $pass = "root";
            
            try{
                $dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
                $dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                
                /*Récupère le contenu de tous les commentaires et seulement les
                 *noms et prénoms des utilisateurs qui ont commenté*/
                $sth = $dbco->prepare("
                  SELECT users.prenom, users.nom, comments.contenu
                  FROM users
                  RIGHT OUTER JOIN comments ON users.id = comments.userId
                ");
                
                $sth-> execute();
                
                $resultat = $sth->fetchAll(PDO::FETCH_ASSOC);
                
                echo '<pre>';
                print_r($resultat);
                echo '</pre>';
            }
                  
            catch(PDOException $e){
                echo "Erreur : " . $e->getMessage();
            }
        ?>
    </body>
</html>

 

Exemple de création d'un right outer join SQL avec PDO PHP

Nous allons bien évidemment pouvoir effectuer un RIGHT JOIN qui ne va pas récupérer les données présentes dans les deux colonnes mais seulement celles dans la colonne de droite en procédant exactement de la même façon qu’avec notre LEFT JOIN c’est-à-dire en utilisant une clause WHERE.

 

Le FULL (OUTER) JOIN

Le FULL JOIN ou FULL OUTER JOIN est un type de jointures externes qui va récupérer toutes les données pour les colonnes sélectionnées de chacune des deux tables.

Attention cependant : ce type de jointures n’est pas supporté en par MySQL.

Nous allons toutefois pouvoir simuler le comportement d’un FULL JOIN en utilisant intelligemment une combinaison de LEFT JOIN et de RIGHT JOIN avec une clause WHERE et en utilisant l’opérateur UNION.

Nous verrons comment faire cela lors de la prochaine leçon quand nous apprendrons à utiliser ce nouvel opérateur.

 

Le CROSS JOIN

Le CROSS JOIN en MySQL va retourner une liste de résultat qui va être le produit des entrées des deux tables jointes lorsqu’aucune clause WHERE n’est utilisée.

Dans une requête SQL de type SELECT, par exemple, chacune des lignes de la première table va être accouplée à chacune des lignes de la seconde table pour former à chaque fois une nouvelle ligne qui va être renvoyée.

En pratique, ce type de jointure est peu utilisé car souvent peu pertinent. Voyons tout de même comment il fonctionne :

<!DOCTYPE html>
<html>
    <head>
        <title>Cours PHP / MySQL</title>
        <meta charset='utf-8'>
    </head>
    <body>
        <h1>Bases de données MySQL</h1>  
        <?php
            $servname = "localhost"; $dbname = "cours"; $user = "root"; $pass = "root";
            
            try{
                $dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
                $dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                
                /*RAffiche le produit cartésien des résultats des deux tables*/
                $sth = $dbco->prepare("
                  SELECT users.prenom, users.nom, comments.contenu
                  FROM users
                  CROSS JOIN comments 
                ");
                
                $sth-> execute();
                
                $resultat = $sth->fetchAll(PDO::FETCH_ASSOC);
                
                echo '<pre>';
                print_r($resultat);
                echo '</pre>';
            }
                  
            catch(PDOException $e){
                echo "Erreur : " . $e->getMessage();
            }
        ?>
    </body>
</html>

 

Exemple de création d'un cross join SQL avec PDO PHP

Comme vous pouvez le voir dans cet exemple, le contenu de chaque commentaire va être joint aux noms et prénoms trouvés dans notre table « users » pour venir former un résultat qui va être affiché.

Note : Je n’ai affiché qu’un échantillon des résultats retournés en photo ici.

 

Le SELF JOIN

Un SELF JOIN représente une jointure d’une table avec elle-même. Le mot clef SELF JOIN n’existe pas, nous allons simplement écrire notre jointure comme on a pu le faire précédemment mais à la différence que nos deux tables seront la même.

Les SELF JOIN vont être utiles pour des tables dans lesquelles il y a un lien hiérarchique entre les données de deux colonnes (employé / patron, enfant / parent) et qui se référencent elles-mêmes.

Pour illustrer l’utilité de faire des SELF JOIN, je vous propose l’exemple suivant : imaginons une table « employes » qui contient des informations sur les employés ainsi que sur leur manager.

Chaque employé va avoir un manager qui va lui-même être l’employé d’un autre manager de niveau hiérarchique supérieur jusqu’à arriver au grand patron.

Notre table va donc contenir 4 colonnes :

  • Une colonne id ;
  • Une colonne nom ;
  • Une colonne prenom ;
  • Une colonne id_manager ;

La colonne « id_manager » va faire référence à la colonne « id » puisqu’encore une fois chaque manager est également un employé.

Voici à quoi ressemble la structure de notre table que vous pouvez créer comme moi directement avec phpMyAdmin :

Création d'une table MySQL avec phpMyAdmin et insertion de données dedans

Création d'une table MySQL avec phpMyAdmin et insertion de données dedans 2

Nous allons insérer trois entrées dans cette table pour les besoins de notre exemple :

Création d'une table MySQL avec phpMyAdmin et insertion de données dedans 3

Création d'une table MySQL avec phpMyAdmin et insertion de données dedans 4

Dans notre table, ici, « Pierre Giraud » est simplement un employé mais n’est le manager d’aucune autre personne. Son manager est l’employé portant l’id = 3, c’est-à-dire « Julia Joly ».

« Julia Joly » est donc manager de « Pierre Giraud » mais est également une employée de la société. Son manager est l’employé portant l’id = 2, c’est-à-dire « Victor Durand ».

« Victor Durand » est ici le Président de l’entreprise : il n’a pas de manager. Nous avons donc mis « 0 » en manager_id afin qu’il n’y ait pas de référence dans la colonne id de notre table. Cependant, même en étant Président, « Victor Durand » reste tout de même un employé (particulier, je vous l’accorde) au sens propre de la société.

Notre but va donc être maintenant d’afficher en même temps le nom d’un employé et le nom de son manager. Pour faire cela, nous allons avoir besoin d’utiliser un SELF JOIN.

<!DOCTYPE html>
<html>
    <head>
        <title>Cours PHP / MySQL</title>
        <meta charset='utf-8'>
    </head>
    <body>
        <h1>Bases de données MySQL</h1>  
        <?php
            $servname = "localhost"; $dbname = "cours"; $user = "root"; $pass = "root";
            
            try{
                $dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
                $dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                
                /*Jointure d'une table avec elle même*/
                $sth = $dbco->prepare("
                  SELECT e.nom AS nom_employe, m.nom AS nom_manager
                  FROM employes e
                  LEFT OUTER JOIN employes m
                  ON e.manager_id = m.id
                ");
                
                $sth-> execute();
                
                $resultat = $sth->fetchAll(PDO::FETCH_ASSOC);
                
                echo '<pre>';
                print_r($resultat);
                echo '</pre>';
            }
                  
            catch(PDOException $e){
                echo "Erreur : " . $e->getMessage();
            }
        ?>
    </body>
</html>

 

Exemple de création d'un self join SQL avec PDO PHP

Ici, on commence par sélectionner le nom de nos employés dans la table ainsi que le nom de nos managers. Rappelez-vous bien qu’on va chercher ces noms dans la même colonne puisque les managers des uns sont également les employés des autres.

Lorsqu’on fait une jointure d’une table avec elle-même, nous sommes obligés d’utiliser des alias afin d’éviter qu’il y ait ambiguïté sur le nom de la table.

Ici, nous utilisons donc deux alias pour notre table « employes » : « e » et « m » qui représentent une nouvelle fois la même table.

Dans cet exercice, nous allons ensuite utiliser un LEFT OUTER JOIN. Rappelez-vous que le mot clef SELF JOIN n’existe pas mais sert simplement à représenter un concept. On va pouvoir faire des SELF JOIN en utilisant des INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, etc.

Ici, nous allons donc récupérer les noms de tous les employés ainsi que les noms de leur manager associé. Pour cela, on effectue notre jointure avec la condition de la similitude des valeurs dans la colonne « manager_id » et de celle de la colonne « id ».

Lorsqu’une valeur dans la colonne « manager_id » trouvera un équivalent dans la colonne « id » de notre table, on renverra la valeur dans la colonne « nom » de l’entrée associée avec la clef « nom_manager » ainsi que la valeur de la colonne nom de notre table de départ avec la clef « nom_employe ».

Laisser un commentaire