SQL OUTER JOIN
Un SQL JOIN est une requête qui relie plusieurs tables d’une base de données relationnelle et émet leurs données (les tuples) de manière filtrée selon une condition de sélection définie par l’utilisateur.
- Domaine .eu ou .fr + éditeur de site gratuit pendant 6 mois
- 1 certificat SSL Wildcard par contrat
- Boîte email de 2 Go
Le type de JOIN le plus courant du modèle de base de données relationnelle est le SQL INNER JOIN. Dans la pratique, les utilisateurs utilisent INNER JOINs, par exemple, si deux tables de base de données doivent être reliées en utilisant les mêmes colonnes. Chaque enregistrement de données d’une table est fusionné avec un enregistrement de données correspondant de l’autre table. Les enregistrements de données pour lesquels le système de gestion de base de données (SGBD) ne trouve pas de correspondance dans l’autre table sont masqués.
Un SQL OUTER JOIN, d’autre part, édite non seulement les enregistrements de données des deux tables qui remplissent la condition de sélection (par exemple, l’égalité des valeurs de deux colonnes), mais aussi tous les autres tuples de l’une ou l’autre table.
En fonction du sens de lecture de la syntaxe SQL, on parle de table gauche ou table droite. Les opérations respectives sont donc appelées LEFT OUTER JOIN et RIGHT OUTER JOIN. Si, en plus des enregistrements de données qui remplissent la condition de sélection, vous voulez également éditer tous les enregistrements de données de la table gauche et droite dans les requêtes de base de données, il s’agit d’un FULL OUTER JOIN.
Le principe des différents types de JOIN peut être très bien illustré par les schémas suivants :
Sous-types de OUTER JOINs
Chaque OUTER JOIN est réalisé comme LEFT OUTER JOIN, RIGHT OUTER JOIN ou FULL OUTER JOIN.
Le mot-clé OUTER est facultatif dans la syntaxe SQL. En règle générale, les utilisateurs écrivent directement LEFT JOIN, RIGHT JOIN et FULL JOIN.
Nous illustrons la fonctionnalité des OUTER JOINs à l’aide des tables de base de données "salaries" et "car".
Table : salaries
| e_id | nom | prenom | car_id |
| 1 | Dupond | Jacques | 3 |
| 2 | Martin | Jean | 1 |
| 3 | Petit | Hélène | 1 |
| 4 | Lefevre | Lisa | 2 |
| 5 | Barthes | Baptiste | NULL |
Le tableau "salaries" contient les nom et prénom des employés d’une entreprise fictive ainsi que l’ID de la voiture de fonction affectée (car_id). La clé primaire de la table est un identifiant pour l’employé (e_id). Le salarié avec l’ID 5 (Baptiste Barthes) n’a pas encore été affecté à un véhicule de fonction. La cellule de la colonne correspondante contient donc une valeur nulle.
La valeur NULL est une valeur représentant l’absence de valeur. Elle ne correspond pas à la valeur numérique 0.
Table : car
| car_id | marque | modele | immatriculation | construction | region |
| 1 | VW | Caddy | B KH 778 | 2016 | 75 |
| 2 | Opel | Astra | B PO 654 | 2010 | 41 |
| 3 | BMW | X6 | B MW 780 | 2017 | 37 |
| 4 | Porsche | Boxster | B AA 123 | 2018 | 36 |
Les informations sur le parc de véhicules de l’entreprise ont été enregistrées dans le tableau "car" : la marque de la voiture, le modèle, le numéro d’immatriculation, l’année de construction et le numéro de département. Un ID cohérent (car_id) est attribué à chaque voiture de fonction, ce qui sert de clé primaire à la table.
Les deux tables sont liées par une relation de clé étrangère. La clé primaire de la table "car" (car_id) a été intégrée comme clé étrangère dans la table "salaries". Cela nous permet de relier les deux tables via une colonne commune.
Même si les clés primaires valides ne doivent pas contenir de valeurs nulles, les valeurs nulles dans les clés étrangères ne violent pas l’intégrité d’un ensemble de données.
SQL LEFT OUTER JOIN
Avec le LEFT OUTER JOIN, la table du côté gauche de l’opérateur JOIN est la table dominante. Dans l’algèbre relationnelle, les LEFT OUTER JOINs sont notés avec l’opérateur suivant : ⟕.
Pour relier les tables "salaries" et "car" dans un LEFT OUTER JOINs, vous pouvez utiliser l’opération suivante :
salaries ⟕ car_id=car_idcar
L’interaction avec le SGBD se fait dans le langage SQL de la base de données. La formule ci-dessus correspond à l’instruction SQL suivante :
SELECT * FROM salaries LEFT JOIN car ON salaries.car_id = car.car_id;La table "salaries" se trouve sur le côté gauche de l’opérateur JOIN, la table "car" sur le côté droit. Nous définissons salaries.car_id = car.car_id comme condition de sélection. L’ensemble de résultats d’une LEFT OUTER JOIN comprend toutes les entrées de la table de gauche ainsi que les enregistrements de données de la table de droite qui remplissent la condition JOIN. Seules les données de la table "car" qui contiennent une valeur dans la colonne car_id et pour laquelle le SGBD trouve également une valeur correspondante dans la table "salaries" sont donc incluses dans l’ensemble de résultats du JOIN.
Les valeurs manquantes dans le tableau de résultats sont éditées comme valeurs nulles.
Contrairement aux INNER JOIN, l’ordre des tables dans l’instruction SQL doit être respecté pour les OUTER JOINs. Avec un LEFT JOIN, tous les enregistrements de données de la table de gauche de l’opérateur JOIN sont complètement édités, avec un RIGHT JOIN, ce sont tous les enregistrements de données de la table de droite de l’opérateur JOIN.
Voici le résultat du LEFT OUTER JOINs :
Table : LEFT OUTER JOIN sur les tables « salaries » et « car »
| e_id | nom | prenom | salaries.car_id | car.car_id | marque | modele | immatriculation | construction | region |
| 1 | Dupond | Jacques | 3 | 3 | BMW | X6 | B MW 780 | 2017 | 37 |
| 2 | Martin | Jean | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 75 |
| 3 | Petit | Hélène | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 75 |
| 4 | Lefevre | Lisa | 2 | 2 | Opel | Astra | B PO 654 | 2010 | 41 |
| 5 | Barthes | Baptiste | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
Le tableau des résultats présente deux particularités :
L’entrée de la table "car" car_id 4 (la Porsche Boxster) n’apparaît pas dans les résultats. Il n’y a en effet pas de clé étrangère appropriée dans le tableau "salaries" pour une clé primaire ayant la valeur 4 ; la condition de sélection n’est pas remplie. L’entrée provient de la table de droite et est donc masquée.
La clé étrangère car_id de la table "salaries" contient une valeur NULL pour l’enregistrement du salarié Baptiste Barthes. Par conséquent, aucune clé primaire correspondante ne se trouve dans le tableau "car". La condition de sélection n’est pas non plus remplie ici. Toutefois, comme l’enregistrement de données provient de la table d’édition de gauche, il est gardé dans la table des résultats du LEFT JOIN. Les valeurs manquantes du tuple sont intégrées comme NULL dans la table des résultats.
SQL RIGHT OUTER JOIN
Le RIGHT OUTER JOIN suit le même principe que le LEFT OUTER JOIN, mais la table dominante ici n’est pas celle de gauche, mais celle de droite.
L’ensemble de résultats du RIGHT OUTER JOIN comprend tous les tuples de la table du côté droit de l’opérateur JOIN ainsi que les tuples de la table gauche qui remplissent la condition JOIN. Le symbole suivant est utilisé comme opérateur : ⟖.
Nous recommençons à partir des tables initiales « salaries » et « car » et définissons la même condition de sélection pour le RIGHT JOIN qu’avec l’exemple précédent.
L’algèbre relationnel :
salaries ⟖ car_id=car_idcar
Instruction SQL :
SELECT * FROM salaries RIGHT JOIN car ON salaries.car_id = car.car_id;Voici l’ensemble de résultats du RIGHT JOIN. On remarque qu’il différe considérablement du LEFT JOIN.
Table : RIGHT OUTER JOIN pour les tables « salaries » et « car »
| e_id | nom | prenom | salaries.car_id | car.car_id | marque | modele | immatriculation | construction | region |
| 1 | Dupond | Jacques | 3 | 3 | BMW | X6 | B MW 780 | 2017 | 37 |
| 2 | Martin | Jean | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 75 |
| 3 | Petit | Hélène | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 75 |
| 4 | Lefevre | Lisa | 2 | 2 | Opel | Astra | B PO 654 | 2010 | 41 |
| NULL | NULL | NULL | 4 | 4 | Porsche | Boxster | B AA 123 | 2018 | 36 |
L’enregistrement de données pour le salarié Baptiste Barthes n’est pas contenu dans la table de résultats. En effet, le car_id qui lui correspond est NULL et ne peut donc être affecté à un enregistrement de données dans la table de droite.
Grâce au RIGHT JOIN, nous obtenons tous les enregistrements de données de la table "car" – y compris les données pour le car_id 4, auquel aucun tuple de la table "salaries" n’a été affecté. Les valeurs manquantes sont également éditées ici comme valeurs NULL.
SQL FULL OUTER JOIN
Un FULL OUTER JOIN est une combinaison de LEFT OUTER JOIN et RIGHT OUTER JOIN. L’opérateur défini pour l’opération en algèbre relationnelle est le suivant : ⟗.
Voici de nouveau une illustration du FULL JOIN avec les tables "salaries" et "car" en donnant la même condition de sélection que précédemment.
Algèbre relationnel :
salaries ⟗ car_id=car_idcar
Instruction SQL :
SELECT * FROM salaries FULL JOIN car ON salaries.car_id = car.car_id;Le résultat correspond à la table ci-dessous.
Table : FULL OUTER JOIN pour les tables « salaries » et « car »
| e_id | nom | prenom | salaries.car_id | car.car_id | marque | modele | immatriculation | construction | region |
| 1 | Dupond | Jacques | 3 | 3 | BMW | X6 | B MW 780 | 2017 | 37 |
| 2 | Martin | Jean | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 75 |
| 3 | Petit | Hélène | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 75 |
| 4 | Lefevre | Lisa | 2 | 2 | Opel | Astra | B PO 654 | 2010 | 41 |
| 5 | Barthes | Baptiste | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | 4 | 4 | Porsche | Boxster | B AA 123 | 2018 | 36 |
Le FULL JOIN relie les données des tables de sortie en fonction de la condition de sélection. Toutefois, il va lister également les données des deux tableaux qui ne remplissent pas la condition.
Les valeurs manquantes dans FULL JOIN sont également définies sur NULL.
Dans la pratique, les FULL OUTER JOINs ne sont pas très significatifs et ne sont en réalité pas supportés par les systèmes de gestion de bases de données leaders du marché tels que MySQL et MariaDB.
NATURAL OUTER JOIN
Comme les INNER JOINs, les OUTER JOINs peuvent également être implémentés en tant que NATURAL JOINs. Les opérateurs correspondants sont :
LEFT/RIGHT JOIN … USINGOu :
NATURAL LEFT/RIGHT JOINLes NATURAL OUTER JOINs relient les tables à l’aide de colonnes du même nom. Les colonnes sélectionnées peuvent être explicitement définies à l’aide du mot-clé USING :
SELECT * FROM salaries LEFT JOIN car USING(car_id);Vous pouvez écrire votre code de manière simplifiée également, le SGBD recherche alors automatiquement les colonnes portant le même nom et relie ainsi les tables listées :
SELECT * FROM salaries NATURAL LEFT JOIN car;Par rapport aux tables d’exemple listées ci-dessus, les deux instructions SQL mènent au même résultat.
| e_id | nom | prenom | car_id | marque | modele | immatriculation | construction | region |
| 1 | Dupond | Jacques | 3 | BMW | X6 | B MW 780 | 2017 | 37 |
| 2 | Martin | Jean | 1 | VW | Caddy | B KH 778 | 2016 | 75 |
| 3 | Petit | Hélène | 1 | VW | Caddy | B KH 778 | 2016 | 75 |
| 4 | Lefevre | Lisa | 2 | Opel | Astra | B PO 654 | 2010 | 41 |
| 5 | Barthes | Baptiste | NULL | NULL | NULL | NULL | NULL | NULL |
Le NATURL LEFT JOIN combine les colonnes salaries.car_id et car.car_id pour former la colonne commune car_id.
Les OUTER JOINs dans la pratique
Les OUTER JOINs conduisent généralement à des regroupements de tables avec des valeurs Null. Il est donc important de vous demander si vous avez besoin de ces valeurs. Dans notre exemple, la Porsche Boxster n’a pas encore été affectée à un employé. Cela ne ressort pas du tableau "car". Le tableau "salaries" montre d’autre part que Baptiste Barthès n’utilise pas encore de voiture de société mais ne montre pas pour autant qu’une voiture est disponible. Après un FULL JOIN sur les deux tables, il est clair d’un coup d’œil que Baptiste pourrait conduire la Porsche à l’avenir.