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.

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

Nous illustrons la fonctionnalité des OUTER JOINs à l’aide des tables de base de données "salaries" et "car".

Table : salaries

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.

Table : car

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.

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_id}car

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 :

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.

Voici le résultat du LEFT OUTER JOINs :

Table : LEFT OUTER JOIN sur les tables « salaries » et « car »

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.

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_id}car

Instruction SQL :

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 »

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.

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_id}car

Instruction SQL :

Le résultat correspond à la table ci-dessous.

Table : FULL OUTER JOIN pour les tables « salaries » et « car »

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.

Comme les INNER JOINs, les OUTER JOINs peuvent également être implémentés en tant que NATURAL JOINs. Les opérateurs correspondants sont :

Ou :

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 :

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 :

Par rapport aux tables d’exemple listées ci-dessus, les deux instructions SQL mènent au même résultat.

Le NATURL LEFT JOIN combine les colonnes salaries.car_id et car.car_id pour former la colonne commune car_id.

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.