Join (SQL)

Un SQL - Join (allemand : Verbund) forme à partir des enregistrements de deux tables d'une base de données relationnelle une table de résultats dont les enregistrements contiennent les attributs des deux tables selon une condition de jointure spécifiée. C'est la mise en œuvre du concept de fédération d' algèbre relationnelle dans le langage de requête SQL. La norme ISO pour SQL décrit les types de jointures suivants :

• Le produit cartésien CROSS JOIN,
• le lien interne sous la forme du lien naturel NATURAL JOINet d'autres variantes, ainsi que
• le composé externe sous les formes LEFT OUTER JOIN, RIGHT OUTER JOINet FULL OUTER JOIN.

Dans un cas particulier, une table peut également être jointe à elle-même, c'est ce qu'on appelle une auto-jointure .

Exemples de tableaux

Les tableaux Mitarbeiteret Abteilungsont utilisés à titre d'exemples pour illustrer les différentes formes d'interconnexion . Le MIdnom d' identification d' un employé est enregistré, ainsi que celui du AbtIdservice dans lequel il est employé. Le département a les attributs AbtIdqui identifient le département et son nom. Un réseau relie les informations des deux tables.

Les exemples de tables présentent les particularités suivantes :

• L'employé nommé "Meyer" n'est affecté à aucun département. La valeur NULLcomme AbtIden SQL signifie que cette valeur est inconnue .
• Le service "Marketing" n'a pas d'employés affectés.

Ces deux tableaux permettent d'illustrer les différentes formes d'association.

Produit cartésien (CROSS JOIN)

Les CROSS JOINdeux tables forment le produit cartésien des enregistrements de données dans les deux tables. Chaque enregistrement de données de la première table est lié à chaque autre enregistrement de la deuxième table. Si les deux tables ont des attributs de même nom, ils sont complétés en plaçant le nom de la table devant.

Exemple de CROSS JOINs :

SELECT *
FROM Mitarbeiter CROSS JOIN Abteilung

La dénomination explicite du produit cartésien a été introduite avec le standard SQL SQL-92 . Dans la norme SQL de 1989, le même résultat peut être obtenu avec l'instruction suivante :

SELECT *
FROM Mitarbeiter, Abteilung

Le résultat a 4 × 3 enregistrements car il y a 4 employés et 3 départements ; il se compose de toutes les combinaisons possibles des enregistrements de données dans les deux tables. Comme cela AbtIdse produit dans les deux tables, les deux attributs de la table de résultats sont rendus uniques en plaçant le nom de la table devant.

Lien intérieur

Le résultat de la jointure interne de deux tables est la combinaison des enregistrements de données des tables impliquées qui remplissent la condition de jointure. En règle générale, la condition de jointure requiert l'égalité des valeurs de certains attributs dans les deux tables. Cependant, il peut également contenir d'autres opérateurs de comparaison que l'égalité.

JOINT NATURELLE

La forme de base du lien intérieur est le lien naturel NATURAL JOIN.

Dans le cas d'une jointure naturelle, les enregistrements de données des deux tables concernées sont liés entre eux, dont les valeurs correspondent aux attributs du même nom . Ainsi seules les combinaisons « convenables » sont formées et pas toutes les combinaisons possibles comme avec le produit cartésien .

Exemple pour le NATURAL JOIN:

SELECT *
FROM Mitarbeiter NATURAL JOIN Abteilung

Dans notre exemple, les deux tables ont un attribut commun, le AbtID. Dans le réseau naturel, chaque employé se voit attribuer le service dont le service est enregistré AbtIdauprès de lui. Comme la valeur de AbtIdest identique dans les deux tableaux de chaque ligne , l'attribut n'apparaît qu'une seule fois dans le tableau des résultats :

L'employé nommé « Meyer » n'étant affecté à aucun service, « M4 » n'apparaît pas dans le résultat. Le service « Marketing » n'apparaît pas non plus, car aucun salarié n'y est affecté.

REJOIGNEZ ... EN UTILISANT ...

Avec la jointure naturelle, les combinaisons sont formées en comparant les valeurs de tous les attributs du même nom dans les deux tables. Souvent, cependant, vous souhaitez spécifier explicitement lequel des attributs doit être utilisé pour lier les ensembles de données. il y a un formulaire pour çaJOIN ... USING ...

Dans notre exemple :

SELECT *
FROM Mitarbeiter JOIN Abteilung USING (AbtID)

Dans cet exemple, le résultat est identique à celui du lien naturel.

Cette forme d'association est NATURAL JOINpréférable à la variante car la spécification explicite des attributs garantit qu'aucune combinaison indésirable ne se forme lorsque les tables concernées sont développées. Dans notre exemple, si Mitarbeiterle nouvel attribut Ortpour le lieu de résidence de l'employé est introduit dans la table Abteilunget qu'un attribut nommé Ortpour l'emplacement du service est également ajouté à la table , alors NATURAL JOINles attributs AbtId et Ort la création des enregistrements de données liés sont comparés, ce qui ne correspond pas à l'intention d'affecter des salariés à leurs services.

REJOIGNEZ ... SUR ...

Il est également possible de créer une jointure entre des tables dans lesquelles les noms des attributs à comparer ne correspondent pas ou dans lesquelles un opérateur autre que =doit être utilisé.

Pour les exemples de tables, une instruction avec cette forme de jointure est

SELECT *
FROM Mitarbeiter JOIN Abteilung ON Mitarbeiter.AbtId = Abteilung.AbtId;

Résultat:

Vous pouvez mettre devant les deux formes JOIN ... USING ...et JOIN ... ON ...aussi le mot-clé facultatif INNERpour souligner qu'il s'agit d'un composé interne.

Dans la norme SQL de 1989, on formulerait l'énoncé ci-dessus comme suit :

SELECT *
FROM Mitarbeiter, Abteilung
WHERE Mitarbeiter.AbtId = Abteilung.AbtId

Dans la première forme de l'énoncé, le composé est spécifié explicitement , tandis que dans la seconde variante, un produit cartésien est formé en premier et le composé est obtenu par la restriction subséquente avec la condition Mitarbeiter.AbtId = Abteilung.AbtId.

Les formes de l'union interne dans les exemples précédents sont également appelées dans la littérature équijointure (en allemand : Gleichverbund ), car l' opérateur d' égalité est utilisé dans la condition pour l'union. Dans la variante, il est JOIN ... ON ...également possible de spécifier n'importe quelle condition pour lier les enregistrements de données des tables concernées, par ex. B. avec l'opérateur de comparaison ≤. Dans ce cas, la jointure est appelée une jointure thêta .

Jointure externe (OUTER JOIN)

L'employé avec le MId"M4" n'apparaît dans aucune des formes du regroupement interne des tables d'exemple , car aucun service ne lui est affecté. Et le service « Marketing » n'apparaît pas car il n'a pas de salariés.

Les formes de la jointure externe incluent des enregistrements de données dans la jointure pour lesquels il n'y a pas d'équivalent des valeurs dans les deux tables. Le réseau externe doit donc toujours être utilisé lorsqu'il s'agit d'informations inconnues ou manquantes.

Si, dans l'exemple des employés et des services, tous les employés et leurs services doivent être sortis, y compris ceux qui ne sont affectés à aucun service, alors un réseau externe est requis.

JOINTURE EXTERNE GAUCHE

Le résultat des tables T ₁ et T ₂ contient tous les enregistrements de données de la table T ₁ à gauche du mot clé , même s'il n'y a pas d' enregistrement de données correspondant dans la table T _{2 à} droite . Les valeurs manquantes de T ₂ sont remplies avec NULL. Le mot - clé n'est requis dans aucun des exemples, mais il peut être écrit pour attirer l'attention du lecteur sur le composé extérieur. T1 LEFT OUTER JOIN T2JOINOUTER

Dans l'exemple des employés et des services

SELECT *
FROM Mitarbeiter LEFT OUTER JOIN Abteilung USING (AbtId)

Le résultat contient désormais également l'employé avec le MId"M4" et les attributs de la table liée Department sont NULL.

JOINT EXTÉRIEUR DROIT

L'un RIGHT OUTER JOIN forme le lien interne entre les deux tables et le complète avec un enregistrement de données chacun pour les enregistrements de données dans la table de droite pour lesquels il n'y a pas de correspondance dans la table de gauche.

Dans l'exemple des employés et des services

SELECT *
FROM Mitarbeiter RIGHT OUTER JOIN Abteilung USING (AbtId)

Le résultat contient maintenant un enregistrement de données pour le département "Marketing" auquel aucun employé n'est affecté, c'est pourquoi les attributs MIdet sont NameNULL.

Un autre exemple dans lequel le réseau externe est requis : Tous les départements doivent être sortis avec le nombre de leurs employés. Étant donné AbtIdqu'aucun enregistrement de données ne serait sorti avec le lien interne pour le département avec 33, la déclaration doit être formulée avec le lien externe :

SELECT AbtName, count(MId) as Mitarbeiterzahl
FROM Mitarbeiter RIGHT OUTER JOIN Abteilung USING (AbtId)
GROUP BY AbtId, AbtName

Résultat:

JOINT EXTÉRIEUR COMPLET

L'un FULL OUTER JOINest l'union des résultats du LEFTet du RIGHT OUTER JOINs.

L'exemple pour les employés et les services :

SELECT *
FROM Mitarbeiter FULL OUTER JOIN Abteilung USING (AbtId)

D'autres variantes de la syntaxe du composé externe sont contenues dans le diagramme de syntaxe ci-dessous.

Auto-rejoindre

Une auto-jointure est une jointure d'une table avec elle-même. Cela signifie que les enregistrements de la table doivent être comparés aux autres enregistrements de la même table. Afin de pouvoir comparer les valeurs des enregistrements de données de la même table en SQL , vous devez attribuer des noms explicites à deux variables de tuple dans l'instruction, qui peuvent toutes deux parcourir les enregistrements de données de la table.

À titre d'exemple, il convient de vérifier s'il y a deux employés avec le même nom mais un MId différent dans la table Employés. Dans l' auto-jointure suivante , les variables de tuple MAet sont définies MBpour la table "Employés" afin de pouvoir effectuer la comparaison.

SELECT MA.MId, MA.Name
FROM Mitarbeiter MA CROSS JOIN Mitarbeiter MB
WHERE MA.MId <> MB.MId AND MA.Name = MB.Name

Le résultat est:

En fait, SQL crée une variable tuple pour chaque table lors du traitement d'une instruction SELECT, qui porte normalement le même nom que la table elle-même. Si l'instruction est, par exemple select * from Mitarbeiter where Mitarbeiter.MId = 'M1', une variable tuple est utilisée qui porte le même nom que la table Mitarbeiteret accepte chaque enregistrement de la table dans la mesure de son contenu. La condition de filtre MId = 'M1'est ensuite vérifiée en exécutant la variable tuple dans la table. Il est possible de nommer explicitement la variable tuple, par exemple select * from Mitarbeiter M where M.MId = 'M1'. Ceci est utilisé dans l' auto-jointure pour avoir deux variables de tuple pour une seule et même table, grâce à quoi leurs enregistrements de données peuvent être comparés les uns aux autres.

Diagramme de syntaxe

Le diagramme de syntaxe suivant montre toutes les formes de jointure en SQL.

La combinaison de deux "références de table" est représentée dans le diagramme de syntaxe. Une "référence de table" peut elle-même être une instruction pour un composé, c'est-à-dire. Cela signifie que non seulement les jointures peuvent être formées avec deux, mais avec plusieurs tables.

Dans notre exemple, imaginons qu'il existe une troisième table Adressequi utilise l'attribut pour attribuer à AdrIdl'employé son adresse. Ensuite, la déclaration suivante entraîne l'attribution de l'adresse et du service à l'employé respectif :

SELECT *
FROM Mitarbeiter JOIN Adresse using (AdrId)
                 JOIN Abteilung using (AbtId)

Le composé interne est (à part l'ordre des attributs dans le résultat) à la fois commutatif et associatif . La jointure externe n'est pas commutative, et en général pas associative non plus. Si différentes formes de composés sont utilisées dans un énoncé comportant plusieurs tableaux, il est conseillé d'utiliser des parenthèses pour plus de clarté.

Spécifique au produit

Les différents systèmes de gestion de bases de données s'écartent en partie du standard SQL ou présentent d'autres variantes pour la formulation du réseau externe. Les liens suivants pointent vers la documentation de jointure pour certains des produits populaires :

• IBM DB2
  • IBM Db2NATURAL JOIN ne prend pas en charge la variante .
• Serveur Microsoft SQL
  • Le dialecte SQL pour Microsoft SQL Server s'appelle Transact-SQL .
  • Transact SQL ne prend pas en charge les formulaires NATURAL JOINet les JOIN ... USING ...jointures, il n'y a donc que la variante ONavec laquelle, cependant, toutes les tâches peuvent être gérées.
• MySQL
  • MySQL prend en charge toutes les formes de jointure selon SQL-92.
  • MySQL a STRAIGHT JOINune variante spéciale de la jointure. Typiquement, l' optimiseur de requêtes d' un système de gestion de base de données décide comment la fédération sera réellement réalisée. Avec STRAIGHT JOINl'optimiseur MySQL, l'ordre dans lequel la jointure doit être exécutée est indiqué.
  • MySQL ne supporte FULL [OUTER] JOINpas cela . Cette forme du composé extérieur peut être créée LEFT/RIGHT OUTER JOINavec l'opérateur par une construction à partir de UNION.
• Oracle
  • Oracle a également une syntaxe propriétaire pour la jointure externe, ce n'est qu'en 2001 avec la version 9 que la syntaxe de SQL-92 pour la jointure externe a été introduite.
  • Aujourd'hui, Oracle recommande la syntaxe standard SQL pour la jointure externe.
• PostgreSQL
  • PostgreSQL prend en charge toutes les formes de jointure selon SQL-92. (La documentation de PostgreSQL comprend un tutoriel sur l'adhésion à .)
• SQLite
  • SQLite ne prend en charge que le LEFT OUTER JOIN. Les deux autres formes du composé externe peuvent être générées en LEFT OUTER JOINutilisant le avec l'opérateur UNION.

Voir également

• Participez à l'algèbre relationnelle
• Relation (base de données)
• Relation (mathématiques)

Littérature

• CJ Date avec Hugh Darwen : Un guide de la norme SQL . 4e édition. Addison-Wesley, 1997. 
• Wolfgang Panny avec Alfred Taudes : Introduction au langage de base de SQL-99 . Springer, 2000. 
• Gunter Saake, Kai-Uwe Sattler et Andreas Heuer : Bases de données : concepts et langages . 4e édition. mitp, 2010, p. 221 f. et 339 f . 
• Alfons Kemper et André Eckler : Systèmes de bases de données : une introduction . 6e édition. Oldenbourg, 2006, p. 125 f . 

Remarques

1. ↑ La syntaxe de SQL92 utilise les mots - clés pour mettre l' accent sur CROSS JOINl'intention que le produit cartésien doit être formé.
2. ↑ Il y a beaucoup d' articles sur Internet sur le réseau, dans lequel il est illustré par les diagrammes de Venn pour les opérations de réglage. Dans ces articles, la jointure interne est souvent formée de deux tables qui ont le même schéma et ce n'est que dans ce cas particulier que la jointure naturelle est réellement identique à l'intersection des deux tables. En général, cependant, ce n'est pas le cas. Dans notre exemple, vous pouvez admirer l'intersection des deux tables Mitarbeiteret Abteilungne pas former car les enregistrements des deux tables ont des structures différentes et ne sont pas comparables. La visualisation des opérateurs composés à l'aide des diagrammes de Venn est généralement inexacte et donc trompeuse.

Preuve individuelle

1. ↑ CJDate avec Hugh Darwen : A Guide to the SQL Standard, quatrième édition, Addison-Wesley, 1997, pp. 135ff
2. ↑ ^{a b} Les différents types de composés sont décrits dans le standard SQL SQL : 2011 dans la partie 2 Fondation Section 7.7.
3. ^ Gunther Saake, Kai-Uwe Sattler et Andreas Heuer : Bases de données : Concepts et langues . mitp, S. 339 . 
4. ^ Gunther Saake, Kai-Uwe Sattler et Andreas Heuer : Bases de données : Concepts et langues . mitp, S. 220 f .