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.

Nom de domaine
Votre domaine en un clic
  • 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 :

Schéma représentatif : INNER JOIN vs. OUTER JOIN
Représentation des INNER JOIN et OUTER JOINs : les zones colorées correspondent aux résultats des JOINs pour les ensembles A et B. Le résultat de l’ensemble INNER JOIN est l’intersection de A et B (bleu foncé). L’ensemble de résultats d’un OUTER JOIN comprend les intersections entre A et B (bleu foncé) et l’ensemble restant (bleu clair) de A (LEFT JOIN), B (RIGHT JOIN) ou A et B (FULL JOIN).

Sous-types de OUTER JOINs

Chaque OUTER JOIN est réalisé comme LEFT OUTER JOIN, RIGHT OUTER JOIN ou FULL OUTER JOIN.

Conseil

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.

Note

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.

Note

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.

Note

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.

Note

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 … USING

Ou :

NATURAL LEFT/RIGHT JOIN

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

Cet article vous a-t-il été utile ?