Join (SQL) - Join (SQL)

Une clause de jointure en SQL - correspondant à une opération de jointure en algèbre relationnelle - combine les colonnes d'une ou plusieurs tables dans une nouvelle table. ANSI -STANDARD SQL cinq types de JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTERet CROSS.

Exemples de tableaux

Pour expliquer les types de jointure, le reste de cet article utilise les tableaux suivants :

Department.DepartmentIDest la clé primaire de la Departmenttable, alors qu'il Employee.DepartmentIDs'agit d'une clé étrangère .

Notez que dans Employee, "Williams" n'a pas encore été affecté à un département. De plus, aucun employé n'a été affecté au département "Marketing".

Voici l'instruction SQL pour créer les tables ci-dessus :

CREATE TABLE department(
    DepartmentID INT PRIMARY KEY NOT NULL,
    DepartmentName VARCHAR(20)
);

CREATE TABLE employee (
    LastName VARCHAR(20),
    DepartmentID INT REFERENCES department(DepartmentID)
);

INSERT INTO department
VALUES (31, 'Sales'),
       (33, 'Engineering'),
       (34, 'Clerical'),
       (35, 'Marketing');

INSERT INTO employee
VALUES ('Rafferty', 31),
       ('Jones', 33),
       ('Heisenberg', 33),
       ('Robinson', 34),
       ('Smith', 34),
       ('Williams', NULL);

Jointure croisée

CROSS JOINrenvoie le produit cartésien des lignes des tables de la jointure. En d'autres termes, il produira des lignes qui combinent chaque ligne du premier tableau avec chaque ligne du deuxième tableau.

Exemple de jointure croisée explicite :

SELECT *
FROM employee CROSS JOIN department;

Exemple de jointure croisée implicite :

SELECT *
FROM employee, department;

La jointure croisée peut être remplacée par une jointure interne avec une condition toujours vraie :

SELECT *
FROM employee INNER JOIN department ON 1=1;

CROSS JOINn'applique lui-même aucun prédicat pour filtrer les lignes de la table jointe. Les résultats de a CROSS JOINpeuvent être filtrés à l'aide d'une WHEREclause, qui peut alors produire l'équivalent d'une jointure interne.

Dans la norme SQL:2011 , les jointures croisées font partie du package facultatif F401, "Table jointe étendue".

Les utilisations normales sont pour vérifier les performances du serveur.

Jointure interne

Une jointure interne nécessite que chaque ligne des deux tables jointes ait des valeurs de colonne correspondantes et est une opération de jointure couramment utilisée dans les applications, mais ne doit pas être considérée comme le meilleur choix dans toutes les situations. La jointure interne crée une nouvelle table de résultats en combinant les valeurs de colonne de deux tables (A et B) en fonction du prédicat de jointure. La requête compare chaque ligne de A avec chaque ligne de B pour trouver toutes les paires de lignes qui satisfont le prédicat de jointure. Lorsque le prédicat de jointure est satisfait en faisant correspondre des valeurs non NULL, les valeurs de colonne pour chaque paire de lignes correspondantes de A et B sont combinées dans une ligne de résultat.

Le résultat de la jointure peut être défini comme le résultat de la première prise du produit cartésien (ou jointure croisée ) de toutes les lignes des tables (combinaison de chaque ligne de la table A avec chaque ligne de la table B), puis du renvoi de toutes les lignes qui satisfont à la joindre le prédicat. Les implémentations SQL réelles utilisent normalement d'autres approches, telles que les jointures de hachage ou les jointures de tri-fusion , car le calcul du produit cartésien est plus lent et nécessiterait souvent une quantité de mémoire prohibitive à stocker.

SQL spécifie deux manières syntaxiques différentes d'exprimer les jointures : la "notation de jointure explicite" et la "notation de jointure implicite". La "notation de jointure implicite" n'est plus considérée comme une bonne pratique, bien que les systèmes de base de données la prennent toujours en charge.

La "notation de jointure explicite" utilise le JOINmot - clé, éventuellement précédé du INNERmot - clé, pour spécifier la table à joindre, et le ONmot - clé pour spécifier les prédicats de la jointure, comme dans l'exemple suivant :

SELECT employee.LastName, employee.DepartmentID, department.DepartmentName 
FROM employee 
INNER JOIN department ON
employee.DepartmentID = department.DepartmentID;

La "notation de jointure implicite" répertorie simplement les tables à joindre, dans la FROMclause de l' SELECTinstruction, en utilisant des virgules pour les séparer. Ainsi, il spécifie une jointure croisée et la WHEREclause peut appliquer des prédicats de filtre supplémentaires (qui fonctionnent de manière comparable aux prédicats de jointure dans la notation explicite).

L'exemple suivant est équivalent au précédent, mais cette fois en notation de jointure implicite :

SELECT employee.LastName, employee.DepartmentID, department.DepartmentName 
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;

Les requêtes données dans les exemples ci-dessus joindront les tables Employee et Department à l'aide de la colonne DepartmentID des deux tables. Lorsque le DepartmentID de ces tables correspond (c'est-à-dire que le prédicat de jointure est satisfait), la requête combinera les colonnes LastName , DepartmentID et DepartmentName des deux tables dans une ligne de résultat. Lorsque le DepartmentID ne correspond pas, aucune ligne de résultat n'est générée.

Ainsi le résultat de l' exécution de la requête ci-dessus sera :

L'employé "Williams" et le département "Marketing" n'apparaissent pas dans les résultats d'exécution de la requête. Ni l'un ni l'autre n'a de ligne correspondante dans l'autre tableau respectif : « Williams » n'a aucun service associé et aucun employé n'a l'ID de service 35 (« Marketing »). Selon les résultats souhaités, ce comportement peut être un bogue subtil, qui peut être évité en remplaçant la jointure interne par une jointure externe .

Jointure interne et valeurs NULL

Les programmeurs doivent faire particulièrement attention lorsqu'ils joignent des tables sur des colonnes pouvant contenir des valeurs NULL , car NULL ne correspondra à aucune autre valeur (pas même NULL elle-même), à ​​moins que la condition de jointure utilise explicitement un prédicat de combinaison qui vérifie d'abord que les colonnes jointes sont NOT NULLavant d'appliquer la ou les conditions de prédicat restantes. La jointure interne ne peut être utilisée en toute sécurité que dans une base de données qui applique l' intégrité référentielle ou où les colonnes de jointure sont garanties de ne pas être NULL. De nombreuses bases de données relationnelles de traitement des transactions reposent sur les normes de mise à jour des données Atomity, Cohérence, Isolation, Durabilité (ACID) pour garantir l'intégrité des données, faisant des jointures internes un choix approprié. Cependant, les bases de données de transaction ont généralement également des colonnes de jointure souhaitables autorisées à être NULL. De nombreux entrepôts de données et de bases de données relationnelles de reporting utilisent des mises à jour par lots d' extraction, de transformation, de chargement (ETL) à grand volume qui rendent l'intégrité référentielle difficile ou impossible à appliquer, ce qui entraîne des colonnes de jointure potentiellement NULL qu'un auteur de requête SQL ne peut pas modifier et qui entraînent l'omission des jointures internes données sans indication d'erreur. Le choix d'utiliser une jointure interne dépend de la conception de la base de données et des caractéristiques des données. Une jointure externe gauche peut généralement être remplacée par une jointure interne lorsque les colonnes de jointure d'une table peuvent contenir des valeurs NULL.

Toute colonne de données pouvant être NULL (vide) ne doit jamais être utilisée comme lien dans une jointure interne, sauf si le résultat souhaité est d'éliminer les lignes avec la valeur NULL. Si les colonnes de jointure NULL doivent être délibérément supprimées du jeu de résultats , une jointure interne peut être plus rapide qu'une jointure externe car la jointure de table et le filtrage sont effectués en une seule étape. À l'inverse, une jointure interne peut entraîner un ralentissement désastreux des performances ou même un blocage du serveur lorsqu'elle est utilisée dans une requête de grand volume en combinaison avec des fonctions de base de données dans une clause SQL Where. Une fonction dans une clause SQL Where peut faire en sorte que la base de données ignore les index de table relativement compacts. La base de données peut lire et joindre les colonnes sélectionnées des deux tables avant de réduire le nombre de lignes à l'aide du filtre qui dépend d'une valeur calculée, ce qui entraîne une quantité relativement énorme de traitement inefficace.

Lorsqu'un ensemble de résultats est produit en joignant plusieurs tables, y compris les tables principales utilisées pour rechercher des descriptions en texte intégral de codes d'identification numériques (une table de recherche ), une valeur NULL dans l'une des clés étrangères peut entraîner l'élimination de la ligne entière à partir du jeu de résultats, sans indication d'erreur. Une requête SQL complexe qui inclut une ou plusieurs jointures internes et plusieurs jointures externes présente le même risque de valeurs NULL dans les colonnes de lien de jointure interne.

Un engagement envers le code SQL contenant des jointures internes suppose que les colonnes de jointure NULL ne seront pas introduites par les modifications futures, y compris les mises à jour des fournisseurs, les modifications de conception et le traitement en masse en dehors des règles de validation des données de l'application telles que les conversions de données, les migrations, les importations en masse et les fusions.

On peut en outre classer les jointures internes en équi-jointures, en jointures naturelles ou en jointures croisées.

Equi-joindre

Une équi-jointure est un type spécifique de jointure basée sur un comparateur, qui utilise uniquement des comparaisons d' égalité dans le prédicat de jointure. L'utilisation d'autres opérateurs de comparaison (tels que <) disqualifie une jointure en tant qu'équi-jointure. La requête ci-dessus a déjà fourni un exemple d'équi-jointure :

SELECT *
FROM employee JOIN department
  ON employee.DepartmentID = department.DepartmentID;

Nous pouvons écrire équi-jointure comme ci-dessous,

SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;

Si les colonnes d'une équi-jointure ont le même nom, SQL-92 fournit une notation abrégée facultative pour exprimer les équi-jointures, au moyen de la USINGconstruction :

SELECT *
FROM employee INNER JOIN department USING (DepartmentID);

La USINGconstruction est plus qu'un simple sucre syntaxique , cependant, puisque l'ensemble de résultats diffère de l'ensemble de résultats de la version avec le prédicat explicite. Plus précisément, toutes les colonnes mentionnées dans la USINGliste n'apparaîtront qu'une seule fois, avec un nom non qualifié, plutôt qu'une seule fois pour chaque table de la jointure. Dans le cas ci-dessus, il y aura une seule DepartmentIDcolonne et non employee.DepartmentIDou department.DepartmentID.

La USINGclause n'est pas prise en charge par MS SQL Server et Sybase.

Jointure naturelle

La jointure naturelle est un cas particulier d'équi-jointure. La jointure naturelle (⋈) est un opérateur binaire qui s'écrit ( R ⋈ S ) où R et S sont des relations . Le résultat de la jointure naturelle est l'ensemble de toutes les combinaisons de tuples dans R et S qui sont égales sur leurs noms d'attributs communs. Par exemple, considérons les tables Employee et Dept et leur jointure naturelle :

Employé Nom IdEmp NomDépt Harry 3415 La finance Sortie 2241 Ventes George 3401 La finance Harriet 2202 Ventes

Département NomDépt Directeur La finance George Ventes Harriet Production Charles

Service des employés${\displaystyle \bowtie }$  Nom IdEmp NomDépt Directeur Harry 3415 La finance George Sortie 2241 Ventes Harriet George 3401 La finance George Harriet 2202 Ventes Harriet

Cela peut également être utilisé pour définir la composition des relations . Par exemple, la composition de Employee et Dept est leur jointure comme indiqué ci-dessus, projetée sur tout sauf l'attribut commun DeptName . Dans la théorie des catégories , la jointure est précisément le produit de la fibre .

La jointure naturelle est sans doute l'un des opérateurs les plus importants puisqu'elle est la contrepartie relationnelle du ET logique. Notez que si la même variable apparaît dans chacun des deux prédicats qui sont connectés par AND, alors cette variable représente la même chose et les deux apparences doivent toujours être remplacées par la même valeur. En particulier, la jointure naturelle permet la combinaison de relations qui sont associées par une clé étrangère . Par exemple, dans l'exemple ci-dessus, une clé étrangère contient probablement de Employee . DeptName à Dept . DeptName , puis la jointure naturelle de Employee et Dept combine tous les employés avec leurs départements. Cela fonctionne car la clé étrangère est conservée entre des attributs portant le même nom. Si ce n'est pas le cas comme dans la clé étrangère de Dept . gestionnaire à l' employé . Name puis ces colonnes doivent être renommées avant que la jointure naturelle ne soit prise. Une telle jointure est parfois également appelée équi-jointure .

Plus formellement, la sémantique de la jointure naturelle est définie comme suit :

${\displaystyle R\bowtie S=\left\{t\cup s\mid t\in R\ \land \ s\in S\ \land \ {\mathit {Fun}}(t\cup s)\right\ }}$,

où Fun est un prédicat qui est vrai pour une relation r si et seulement si r est une fonction. Il est généralement exigé que R et S aient au moins un attribut commun, mais si cette contrainte est omise et que R et S n'ont pas d'attributs communs, alors la jointure naturelle devient exactement le produit cartésien.

La jointure naturelle peut être simulée avec les primitives de Codd comme suit. Soit c ₁ , …, c _m les noms d'attributs communs à R et S , r ₁ , …, r _n les noms d'attributs uniques à R et soit s ₁ , …, s _k les attributs uniques à S . De plus, supposons que les noms d'attributs x ₁ , …, x _m ne sont ni dans R ni dans S . Dans un premier temps, les noms d'attributs communs dans S peuvent maintenant être renommés :

${\displaystyle T=\rho _{x_{1}/c_{1},\ldots ,x_{m}/c_{m}}(S)=\rho _{x_{1}/c_{1}} (\rho _{x_{2}/c_{2}}(\ldots \rho _{x_{m}/c_{m}}(S)\ldots ))}$

Ensuite, nous prenons le produit cartésien et sélectionnons les tuples à joindre :

${\displaystyle U=\pi _{r_{1},\ldots ,r_{n},c_{1},\ldots ,c_{m},s_{1},\ldots ,s_{k}}(P )}$

Une jointure naturelle est un type d'équi-jointure où le prédicat de jointure apparaît implicitement en comparant toutes les colonnes des deux tables qui ont les mêmes noms de colonnes dans les tables jointes. La table jointe résultante contient une seule colonne pour chaque paire de colonnes portant le même nom. Dans le cas où aucune colonne portant le même nom n'est trouvée, le résultat est une jointure croisée .

La plupart des experts s'accordent à dire que les NATURAL JOIN sont dangereux et déconseillent donc fortement leur utilisation. Le danger vient de l'ajout par inadvertance d'une nouvelle colonne, nommée de la même manière qu'une autre colonne dans l'autre table. Une jointure naturelle existante peut alors "naturellement" utiliser la nouvelle colonne pour les comparaisons, en effectuant des comparaisons/correspondances en utilisant des critères différents (de différentes colonnes) qu'auparavant. Ainsi, une requête existante pourrait produire des résultats différents, même si les données des tables n'ont pas été modifiées, mais seulement augmentées. L'utilisation de noms de colonnes pour déterminer automatiquement les liens de table n'est pas une option dans les grandes bases de données avec des centaines ou des milliers de tables où elle placerait une contrainte irréaliste sur les conventions de nommage. Les bases de données du monde réel sont généralement conçues avec des données de clé étrangère qui ne sont pas renseignées de manière cohérente (les valeurs NULL sont autorisées), en raison des règles métier et du contexte. Il est courant de modifier les noms de colonnes de données similaires dans différentes tables et ce manque de cohérence rigide relègue les jointures naturelles à un concept théorique de discussion.

L'exemple de requête ci-dessus pour les jointures internes peut être exprimé comme une jointure naturelle de la manière suivante :

SELECT *
FROM employee NATURAL JOIN department;

Comme pour la USINGclause explicite , une seule colonne DepartmentID apparaît dans la table jointe, sans qualificateur :

PostgreSQL, MySQL et Oracle prennent en charge les jointures naturelles ; Microsoft T-SQL et IBM DB2 ne le font pas. Les colonnes utilisées dans la jointure sont implicites, le code de jointure n'indique donc pas quelles colonnes sont attendues et une modification des noms de colonnes peut modifier les résultats. Dans la norme SQL:2011 , les jointures naturelles font partie du package facultatif F401, "Table jointe étendue".

Dans de nombreux environnements de bases de données, les noms de colonnes sont contrôlés par un fournisseur externe, et non par le développeur de requêtes. Une jointure naturelle suppose la stabilité et la cohérence des noms de colonnes qui peuvent changer lors des mises à niveau de version mandatées par le fournisseur.

Jointure externe

La table jointe conserve chaque ligne, même si aucune autre ligne correspondante n'existe. Les jointures externes se subdivisent en jointures externes gauches, jointures externes droites et jointures externes complètes, selon les lignes de la table retenues : gauche, droite ou les deux (dans ce cas, gauche et droite font référence aux deux côtés du JOINmot - clé). Comme les jointures internes , on peut en outre sous-catégoriser tous les types de jointures externes en équi-jointures , jointures naturelles , ( θ -join ), etc. ON <predicate>

Aucune notation de jointure implicite pour les jointures externes n'existe dans le SQL standard.

Un diagramme de Venn représentant l'instruction SQL Left Join entre les tables A et B.

Jointure externe gauche

Le résultat d'une jointure externe gauche (ou simplement jointure gauche ) pour les tables A et B contient toujours toutes les lignes de la table "gauche" (A), même si la condition de jointure ne trouve aucune ligne correspondante dans la table "droite" (B). Cela signifie que si la ONclause correspond à 0 (zéro) ligne dans B (pour une ligne donnée dans A), la jointure renverra toujours une ligne dans le résultat (pour cette ligne) - mais avec NULL dans chaque colonne de B. A à gauche la jointure externe renvoie toutes les valeurs d'une jointure interne plus toutes les valeurs de la table de gauche qui ne correspondent pas à la table de droite, y compris les lignes avec des valeurs NULL (vides) dans la colonne de lien.

Par exemple, cela nous permet de trouver le service d'un employé, mais affiche toujours les employés qui n'ont pas été affectés à un service (contrairement à l'exemple de jointure interne ci-dessus, où les employés non affectés ont été exclus du résultat).

Exemple de jointure externe gauche (le OUTERmot-clé est facultatif), avec la ligne de résultat supplémentaire (par rapport à la jointure interne) en italique :

SELECT *
FROM employee 
LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;

Syntaxes alternatives

Oracle prend en charge la syntaxe déconseillée :

SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID(+)

Sybase prend en charge la syntaxe ( Microsoft SQL Server a déprécié cette syntaxe depuis la version 2000) :

SELECT *
FROM employee, department
WHERE employee.DepartmentID *= department.DepartmentID

IBM Informix prend en charge la syntaxe :

SELECT *
FROM employee, OUTER department
WHERE employee.DepartmentID = department.DepartmentID

Un diagramme de Venn représentant l'instruction SQL de jointure droite entre les tables A et B.

Jointure externe droite

Une jointure externe droite (ou jointure droite ) ressemble beaucoup à une jointure externe gauche, sauf que le traitement des tables est inversé. Chaque ligne du tableau "de droite" (B) apparaîtra au moins une fois dans le tableau joint. S'il n'existe aucune ligne correspondante de la table "gauche" (A), NULL apparaîtra dans les colonnes de A pour les lignes qui n'ont aucune correspondance dans B.

Une jointure externe droite renvoie toutes les valeurs de la table de droite et les valeurs correspondantes de la table de gauche (NULL en cas d'absence de prédicat de jointure correspondant). Par exemple, cela nous permet de trouver chaque employé et son département, mais toujours d'afficher les départements qui n'ont pas d'employés.

Vous trouverez ci-dessous un exemple de jointure externe droite (le OUTERmot-clé est facultatif), avec la ligne de résultat supplémentaire en italique :

SELECT *
FROM employee RIGHT OUTER JOIN department
  ON employee.DepartmentID = department.DepartmentID;

Les jointures externes droite et gauche sont fonctionnellement équivalentes. Ni l'un ni l'autre ne fournit de fonctionnalité que l'autre n'offre, de sorte que les jointures externes droite et gauche peuvent se remplacer tant que l'ordre des tables est inversé.

Un diagramme de Venn représentant l'instruction SQL de jointure complète entre les tables A et B.

Jointure externe complète

Conceptuellement, une jointure externe complète combine l'effet de l'application de jointures externes gauche et droite. Lorsque les lignes des tables FULL OUTER JOINed ne correspondent pas, le jeu de résultats aura des valeurs NULL pour chaque colonne de la table qui n'a pas de ligne correspondante. Pour les lignes qui correspondent, une seule ligne sera produite dans l'ensemble de résultats (contenant des colonnes renseignées à partir des deux tables).

Par exemple, cela nous permet de voir chaque employé qui est dans un département et chaque département qui a un employé, mais aussi de voir chaque employé qui ne fait pas partie d'un département et chaque département qui n'a pas d'employé.

Exemple de jointure externe complète (le OUTERmot-clé est facultatif) :

SELECT *
FROM employee FULL OUTER JOIN department
  ON employee.DepartmentID = department.DepartmentID;

Certains systèmes de base de données ne prennent pas directement en charge la fonctionnalité de jointure externe complète, mais ils peuvent l'émuler grâce à l'utilisation d'une jointure interne et à la sélection par UNION ALL des "lignes de table unique" des tables de gauche et de droite respectivement. Le même exemple peut apparaître comme suit :

SELECT employee.LastName, employee.DepartmentID,
       department.DepartmentName, department.DepartmentID
FROM employee
INNER JOIN department ON employee.DepartmentID = department.DepartmentID

UNION ALL

SELECT employee.LastName, employee.DepartmentID,
       cast(NULL as varchar(20)), cast(NULL as integer)
FROM employee
WHERE NOT EXISTS (
    SELECT * FROM department
             WHERE employee.DepartmentID = department.DepartmentID)

UNION ALL

SELECT cast(NULL as varchar(20)), cast(NULL as integer),
       department.DepartmentName, department.DepartmentID
FROM department
WHERE NOT EXISTS (
    SELECT * FROM employee
             WHERE employee.DepartmentID = department.DepartmentID)

Une autre approche pourrait être UNION ALL de la jointure externe gauche et de la jointure externe droite MOINS la jointure interne.

Auto-adhésion

Une auto-jointure consiste à joindre une table à elle-même.

Exemple

S'il y avait deux tables séparées pour les employés et une requête qui demandait des employés dans la première table ayant le même pays que les employés dans la seconde table, une opération de jointure normale pourrait être utilisée pour trouver la table de réponses. Cependant, toutes les informations sur les employés sont contenues dans un seul grand tableau.

Considérons un Employeetableau modifié tel que le suivant :

Un exemple de requête de solution pourrait être le suivant :

SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F INNER JOIN Employee S ON F.Country = S.Country
WHERE F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;

Ce qui entraîne la génération du tableau suivant.

Pour cet exemple :

• Fet Ssont des alias pour les première et deuxième copies de la table des employés.
• La condition F.Country = S.Countryexclut les jumelages entre employés de pays différents. L'exemple de question ne voulait que des paires d'employés dans le même pays.
• La condition F.EmployeeID < S.EmployeeIDexclut les jumelages où le EmployeeIDdu premier employé est supérieur ou égal à celui EmployeeIDdu deuxième employé. En d'autres termes, cette condition a pour effet d'exclure les appariements en double et les auto-appariements. Sans cela, le tableau suivant, moins utile, serait généré (le tableau ci-dessous n'affiche que la partie "Allemagne" du résultat) :

Un seul des deux appariements du milieu est nécessaire pour répondre à la question d'origine, et le plus haut et le plus bas n'ont aucun intérêt dans cet exemple.

Alternatives

L'effet d'une jointure externe peut également être obtenu en utilisant un UNION ALL entre un INNER JOIN et un SELECT des lignes de la table "principale" qui ne remplissent pas la condition de jointure. Par exemple,

SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
FROM employee
LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;

peut aussi s'écrire comme

SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
FROM employee
INNER JOIN department ON employee.DepartmentID = department.DepartmentID

UNION ALL

SELECT employee.LastName, employee.DepartmentID, cast(NULL as varchar(20))
FROM employee
WHERE NOT EXISTS (
    SELECT * FROM department
             WHERE employee.DepartmentID = department.DepartmentID)

Mise en œuvre

De nombreux travaux dans les systèmes de bases de données ont visé à une mise en œuvre efficace des jointures, car les systèmes relationnels demandent généralement des jointures, mais rencontrent des difficultés pour optimiser leur exécution efficace. Le problème se pose parce que les jointures internes fonctionnent à la fois de manière commutative et associative . En pratique, cela signifie que l'utilisateur fournit simplement la liste des tables à joindre et les conditions de jointure à utiliser, et le système de base de données a pour tâche de déterminer la manière la plus efficace d'effectuer l'opération. Un optimiseur de requête détermine comment exécuter une requête contenant des jointures. Un optimiseur de requêtes a deux libertés fondamentales :

1. Ordre de jointure : étant donné qu'il joint les fonctions de manière commutative et associative, l'ordre dans lequel le système joint les tables ne modifie pas le jeu de résultats final de la requête. Cependant, l'ordre de jointure peut avoir un impact énorme sur le coût de l'opération de jointure, il est donc très important de choisir le meilleur ordre de jointure.
2. Méthode de jointure : Étant donné deux tables et une condition de jointure, plusieurs algorithmes peuvent produire le jeu de résultats de la jointure. L'algorithme qui s'exécute le plus efficacement dépend de la taille des tables d'entrée, du nombre de lignes de chaque table qui correspondent à la condition de jointure et des opérations requises par le reste de la requête.

De nombreux algorithmes de jointure traitent leurs entrées différemment. On peut désigner les entrées d'une jointure comme les opérandes de jointure « externe » et « intérieure », ou « gauche » et « droite », respectivement. Dans le cas de boucles imbriquées, par exemple, le système de base de données analysera l'intégralité de la relation interne pour chaque ligne de la relation externe.

On peut classer les plans de requête impliquant des jointures comme suit :

profond à gauche

en utilisant une table de base (plutôt qu'une autre jointure) comme opérande interne de chaque jointure dans le plan

droit-profond

utiliser une table de base comme opérande externe de chaque jointure dans le plan

broussailleux

ni à gauche ni à droite ; les deux entrées d'une jointure peuvent elles-mêmes résulter de jointures

Ces noms dérivent de l'apparence du plan de requête s'il est dessiné sous la forme d'un arbre , avec la relation de jointure externe à gauche et la relation interne à droite (comme l'exige la convention).

Algorithmes de jointure

Il existe trois algorithmes fondamentaux pour effectuer une opération de jointure : la jointure par boucle imbriquée , la jointure par tri-fusion et la jointure par hachage .

Joindre des index

Les join index sont des index de base de données qui facilitent le traitement des requêtes de jointure dans les entrepôts de données : ils sont actuellement (2012) disponibles dans les implémentations par Oracle et Teradata .

Dans l'implémentation Teradata, les colonnes spécifiées, les fonctions d'agrégation sur les colonnes ou les composants des colonnes de date d'une ou plusieurs tables sont spécifiés à l'aide d'une syntaxe similaire à la définition d'une vue de base de données : jusqu'à 64 colonnes/expressions de colonne peuvent être spécifiées dans un seul rejoindre l'index. Facultativement, une colonne qui définit la clé primaire des données composites peut également être spécifiée : sur du matériel parallèle, les valeurs de colonne sont utilisées pour partitionner le contenu de l'index sur plusieurs disques. Lorsque les tables source sont mises à jour de manière interactive par les utilisateurs, le contenu du join index est automatiquement mis à jour. Toute requête dont la clause WHERE spécifie une combinaison de colonnes ou d'expressions de colonnes qui sont un sous-ensemble exact de celles définies dans un join index (une "requête de couverture") entraînera le join index, plutôt que les tables d'origine et leurs index, à consulter lors de l'exécution de la requête.

L'implémentation Oracle se limite à l'utilisation d' index bitmap . Un join index bitmap est utilisé pour les colonnes à faible cardinalité (c'est-à-dire les colonnes contenant moins de 300 valeurs distinctes, selon la documentation Oracle) : il combine les colonnes à faible cardinalité de plusieurs tables liées. L'exemple utilisé par Oracle est celui d'un système d'inventaire, où différents fournisseurs fournissent différentes pièces. Le schéma comporte trois tables liées : deux « tables maîtres », Pièce et Fournisseur, et une « table de détail », Inventaire. La dernière est une table plusieurs-à-plusieurs reliant le fournisseur à la pièce et contient le plus grand nombre de lignes. Chaque pièce a un type de pièce, et chaque fournisseur est basé aux États-Unis et possède une colonne État. Il n'y a pas plus de 60 états+territoires aux États-Unis, et pas plus de 300 types de pièces. L'index de jointure bitmap est défini à l'aide d'une jointure standard à trois tables sur les trois tables ci-dessus et en spécifiant les colonnes Part_Type et Supplier_State pour l'index. Cependant, il est défini sur la table d'inventaire, même si les colonnes Part_Type et Supplier_State sont "empruntées" à Supplier et Part respectivement.

Comme pour Teradata, un join index bitmap Oracle n'est utilisé pour répondre à une requête que lorsque la clause WHERE de la requête spécifie des colonnes limitées à celles qui sont incluses dans le join index.

Joindre directement

Certains systèmes de bases de données permettent à l'utilisateur de forcer le système à lire les tables d'une jointure dans un ordre particulier. Ceci est utilisé lorsque l'optimiseur de jointure choisit de lire les tables dans un ordre inefficace. Par exemple, dans MySQL, la commande STRAIGHT_JOINlit les tables exactement dans l'ordre indiqué dans la requête.

Voir également

• Join (algèbre relationnelle)
• Anti-jointure
• Opérations sur les ensembles (SQL)

Les références

Citations

Sources

Liens externes

• Spécifique aux produits
  • Jointures Sybase ASE 15
  • Jointures MySQL 5.7
  • Jointures PostgreSQL 9.3
  • Jointures dans Microsoft SQL Server
  • Jointures dans MaxDB 7.6
  • Se joint à Oracle 12c R1
  • Jointures Oracle SQL