Qu'est-ce qu'une auto-jonction en SQL ? Une explication avec sept exemples

La jointure automatique est un cas particulier populaire de la jointure SQL. Alors que la plupart des JOIN relient deux ou plusieurs tables entre elles pour présenter leurs données ensemble, une auto-jonction relie une table à elle-même. Cela se fait généralement en joignant une table à elle-même une seule fois dans une requête SQL, mais il est possible de le faire plusieurs fois dans la même requête.

En général, chaque table d'une base de données stocke un type d'information spécifique. Ainsi, il y a souvent des centaines de tables liées les unes aux autres dans une base de données. Cela implique la nécessité d'utiliser des jointures. Vous pouvez joindre différentes tables par leurs colonnes communes à l'aide du mot-clé JOIN. Il est également possible de joindre une table à elle-même, ce que l'on appelle une auto-jonction. Dans cet article, nous allons voir ce qu'est une auto-jonction, comment elle fonctionne et quand vous en avez besoin dans vos requêtes SQL.

Pour pratiquer SQL JOIN, y compris les jointures automatiques, je recommande notre cours interactif Les jointures en SQL. Il contient plus de 90 exercices sur différents types de JOIN, y compris des sections consacrées uniquement aux auto-jonctions.

Qu'est-ce qu'une auto-jonction en SQL ?

L'auto-jonction, comme son nom l'indique, joint une table à elle-même. Pour utiliser une auto-jonction, la table doit contenir une colonne (appelée X) qui sert de clé primaire et une autre colonne (appelée Y) qui stocke des valeurs pouvant être comparées aux valeurs de la colonne X. Les valeurs des colonnes X et Y ne doivent pas nécessairement être identiques pour une ligne donnée, et la valeur de la colonne Y peut même être null.

Prenons un exemple. Considérons la table Employes:

Chaque employé a sa propre ID, qui est notre " Colonne X ". Pour un employé donné (c'est-à-dire une ligne), la colonne ManagerID contient le ID de son responsable ; c'est notre " colonne Y ". Si nous recherchons les paires employé-manager dans cette table en utilisant ces colonnes :

• Le responsable de l'employé John Smith est l'employé avec ID 3, c'est-à-dire Tom Lanon.
• Le responsable de l'employé Jane Anderson est l'employé avec ID 3, c'est-à-dire Tom Lanon.
• Le responsable de l'employé Tom Lanon est l'employé avec ID 4, c'est-à-dire Anne Connor.
• L'employée Anne Connor n'a pas de responsable ; son site ManagerID est nul.
• Le responsable de l'employé Jeremy York est l'employé dont l'adresse ID est 1, c'est-à-dire John Smith.

Ce type de structure de table est très courant dans les hiérarchies. Maintenant, pour afficher le nom du manager de chaque employé dans la même ligne, nous pouvons exécuter la requête suivante :

SELECT
	Employe.ID,
   	Employe.NomComplet,
   	Employe.ManagerID,
   	Manager.NomComplet as CoequipierNom
FROM Employes Employe
JOIN Employes Manager
  ON Employe.ManagerID = Manager.ID

qui renvoie le résultat suivant :

La requête sélectionne les colonnes ID, NomComplet, et ManagerID dans la table avec l'alias Employe. Elle sélectionne également la colonne NomComplet de la table avec l'alias Manager et désigne cette colonne comme CoequipierNom. En conséquence, chaque employé qui a un manager est édité avec l'ID et le nom de son manager.

Dans cette requête, la table Employes est jointe à elle-même et a deux rôles différents :

• Rôle 1 : elle stocke les données des employés (alias. Employe).
• Rôle 2 : elle stocke les données du manager (alias Manager).

En procédant ainsi, nous considérons essentiellement les deux copies de la table Employes comme s'il s'agissait de deux tables distinctes, l'une pour les employés et l'autre pour les managers.

Vous pouvez trouver plus d'informations sur le concept de l'auto-jonction dans notre article Guide illustré de l'auto-jonction SQL.

Alias de table dans l'auto-jonction

Lorsque l'on fait référence à la même table plus d'une fois dans une requête SQL, nous avons besoin d'un moyen de distinguer chaque référence des autres. Pour cette raison, il est important d'utiliser des alias pour identifier de manière unique chaque référence à la même table dans une requête SQL. Comme bonne pratique, les alias doivent indiquer le rôle de la table pour chaque référence spécifique dans une requête.

Les alias sont en rouge dans la requête suivante. Vous pouvez voir leur déclaration dans les clauses FROM et JOIN.

SELECT
	Employe.ID,
   	Employe.NomComplet,
   	Employe.ManagerID,
   	Manager.NomComplet as CoequipierNom
FROM Employes Employe
JOIN Employes Manager
ON Employe.ManagerID = Manager.ID

Le mot-clé JOIN relie deux tables et est généralement suivi d'une clause ON ou USING qui spécifie les colonnes communes utilisées pour relier les deux tables. Ici, nous voyons que les deux références à la table Employes sont liées en conditionnant la correspondance entre le ManagerID de l'employé et l'ID de l'employé du manager.

Exemples

Passons en revue quelques scénarios courants qui utilisent la jointure automatique.

Scénario 1 : traitement d'une hiérarchie en SQL

La jointure automatique est couramment utilisée pour traiter une hiérarchie. Comme nous l'avons vu précédemment, une hiérarchie attribue une ligne d'une table à une autre ligne de la même table. Vous pouvez vous imaginer qu'il s'agit de rangs parents et enfants.

Revenons à l'exemple des employés et de leurs responsables. Voici à nouveau la table Employes Voici à nouveau la table :

Et le code pour lister chaque employé qui a un manager avec le nom de son manager :

SELECT
	Employe.ID,
   	Employe.NomComplet,
   	Employe.ManagerID,
   	Manager.NomComplet as CoequipierNom
FROM Employes Employe
JOIN Employes Manager
ON Employe.ManagerID = Manager.ID

Voici le résultat lorsque vous exécutez le code :

Cette requête utilise la jointure standard, également connue sous le nom de INNER JOIN. Pour en savoir plus sur le site INNER JOIN, consultez notre article Guide illustré du INNER JOIN SQL.

Si nous voulons lister tous les employés, qu'ils aient ou non des managers, nous pouvons utiliser une LEFT OUTER JOIN à la place. C'est ce que fait la requête ci-dessous :

SELECT
	Employe.ID,
   	Employe.NomComplet,
   	Employe.ManagerID,
   	Manager.NomComplet as CoequipierNom
FROM Employes Employe
LEFT OUTER JOIN Employes Manager
ON Employe.ManagerID = Manager.ID

Lorsque vous exécutez cette requête, vous obtenez le résultat suivant :

La différence entre JOIN et LEFT OUTER JOIN devient claire lorsque nous la comparons avec le résultat de la requête SQL précédente. Dans le résultat de la requête (interne) JOIN, seuls les employés ayant des managers sont inclus. En revanche, la requête LEFT OUTER JOIN renvoie tous les employés, avec ou sans managers. Pour en savoir plus sur OUTER JOIN, veuillez lire notre article intitulé Guide illustré du OUTER JOIN en SQL.

Un autre exemple de hiérarchie est la relation entre les parents et leurs enfants. Considérons la table Humain présenté ici :

Dans la requête ci-dessous, les enfants sont assignés à leurs parents respectifs en joignant la table Humain à elle-même :

SELECT
	Enfant.ID as EnfantID,
   	Enfant.Prenom as EnfantPrenom,
   	Enfant.Age as EnfantAge,
   	Enfant.ParentID,
   	parent.Prenom as ParentPrenom,
   	parent.age as ParentAge
FROM Humain Enfant
INNER JOIN Humain parent
ON Enfant.ParentID = parent.ID

Voici le résultat de cette requête :

Le résultat de la requête ne comprend que les enfants qui ont des parents. Comme c'était le cas dans l'exemple de la hiérarchie employé-manager, nous pourrions utiliser un LEFT OUTER JOIN pour inclure toutes les lignes de la table avec l'alias Enfant.

Voici encore un autre exemple de hiérarchie. Considérons la table Categorie présentée ci-dessous :

Affectons une catégorie mère à chaque catégorie lorsque cela est possible. Voici une requête pour le faire :

SELECT
  Categorie.ID,
  Categorie.Quantite,
  Categorie.Categorie,
  Categorie.ParentCategorieID,
  ParentCategorie.Categorie as ParentCategorie
FROM Categorie
JOIN Categorie ParentCategorie
  ON Categorie.ParentCategorieID = ParentCategorie.ID

Et voici le résultat :

Les quatre premières colonnes du résultat ci-dessus proviennent de la référence à la table avec l'alias Categorie. La dernière colonne provient de la table avec l'alias ParentCategorie et contient le nom de la catégorie parent à laquelle correspond le ID respectif.

La table Categorie a deux rôles différents, comme l'indiquent les deux références distinctes. La colonne ParentCategorieID de la table avec l'alias Categorie est mise en correspondance avec ID de la table avec l'alias ParentCategorie. La clause ON spécifie que ParentCategorieID de Categorie doit être égale à ID de ParentCategorie pour relier les lignes correspondantes.

Scénario 2 : lister les paires dans une table

Vous pouvez utiliser une jointure automatique pour générer des paires de lignes en fonction de la condition de la clause ON. Commençons par un exemple simple qui génère toutes les paires possibles parmi les collègues. Considérons la table suivante, Coequipiers:

Supposons que nous devions générer toutes les paires possibles parmi les collègues afin que chacun ait une chance de parler avec tous les autres lors de la soirée d'introduction de l'entreprise. Voici le code SQL :

SELECT
	Coequipier1.NomComplet as Coequipier1NomComplet,
	Coequipier1.Age as Coequipier1Age,
   	Coequipier2.NomComplet as Coequipier2NomComplet,
	Coequipier2.Age as Coequipier2Age
FROM Coequipiers Coequipier1
CROSS JOIN Coequipiers Coequipier2
ON Coequipier1.NomComplet <> Coequipier2.NomComplet

Et voici le résultat :

Le résultat fait correspondre chaque personne avec chaque personne de la table. Comme nous ne voulons pas que quelqu'un soit jumelé avec lui-même, nous avons la condition de la clause ON Coequipier1.NomComplet <> Coequipier2.NomComplet . Cela signifie que chaque personne sera jumelée avec trois autres collègues, car il y a quatre collègues à cet événement.

Maintenant, examinons un exemple légèrement plus compliqué. Considérons la table Humain ci-dessous. Nous voulons faire correspondre tous les ancêtres à chaque personne lorsque les données le permettent, où une personne est un ancêtre si elle a une valeur supérieure à ID.

La table ci-dessous est la table Humain utilisée dans cet exemple.

Trouvons toutes les paires descendant-ancêtre dans la table ci-dessus. Voici le code SQL :

SELECT
	descendant.ID,
   	descendant.Prenom,
   	descendant.Age,
   	descendant.ParentID,
   	Ancetre.ID as AncetreID,
   	Ancetre.Prenom as AncetrePrenom,
   	Ancetre.Age as AncetreAge
FROM Humain descendant
LEFT JOIN Humain Ancetre
  ON descendant.ParentID <= Ancetre.ID

Et le résultat :

En spécifiant la condition de la clause ON descendant.ParentID <= Ancetre.ID , nous trouvons tous les ancêtres de chaque personne dans la table où ils existent ; sinon, la requête renvoie null pour les informations sur les ancêtres.

Les quatre premières colonnes sont extraites de la table avec l'alias descendant qui contient les informations sur la personne pour laquelle les ancêtres sont recherchés. Les trois dernières colonnes sont extraites de la table avec l'alias Ancetre et contiennent des détails sur chaque ancêtre.

Scénario 3 : auto-jonction en combinaison avec une autre table

En SQL, il est possible d'avoir une auto-jonction en combinaison avec une ou plusieurs tables différentes. Bien qu'il ne s'agisse pas d'une auto-jonction propre, cette situation est très courante dans la pratique.

Un exemple concret est celui des informations sur les vols dans les aéroports, avec une énorme quantité de données chaque heure. Supposons que nous voulions rechercher le numéro d'identification d'un vol ainsi que les détails concernant les aéroports de départ et de destination. Considérons les tables suivantes :

Table Aeroport:

Table Vol:

Ici, notez que la colonne AeroportID de la table Aeroport est la clé étrangère des colonnes DepartAeroportID et ArriveeAeroportID de la table. Vol table. Nous allons joindre la table Aeroport à la table Vol deux fois distinctes comme suit :

• Dans le premier JOIN, Aeroport prend le rôle de la table avec les aéroports de départ.
• Dans le deuxième JOIN, Aeroport prend le rôle de la table avec les aéroports de destination.

La requête ressemble à ceci :

SELECT
	Vol.VolID,
   	Vol.AirplaneID,
   	Vol.DepartAeroportID,
   	DepartAeroport.Pays as DepartAeroportPays,
   	DepartAeroport.Ville as DepartAeroportVille,
   	Vol.ArriveeAeroportID,
   	ArriveeAeroport.Pays as ArriveeAeroportPays,
   	ArriveeAeroport.Ville as ArriveeAeroportVille
FROM Vol
JOIN Aeroport DepartAeroport
  ON Vol.DepartAeroportID = DepartAeroport.AeroportID
JOIN Aeroport ArriveeAeroport
  ON Vol.ArriveeAeroportID = ArriveeAeroport.AeroportID

Et le résultat de la requête ressemble à ceci :

Analysons le résultat. Les trois premières colonnes proviennent d'un simple SELECT de la table Vol. Les deux colonnes suivantes proviennent de Aeroport dans le rôle de la table de l'aéroport de départ ; les lignes sont appariées sur la base d'AeroportID et de DepartAeroportID de la table de départ et d'arrivée respectivement Aeroport et Vol. Cette colonne est suivie d'une colonne provenant de la table Vol. Les deux dernières colonnes proviennent d'Aeroport dans le rôle de la table de l'aéroport de destination cette fois ; les lignes sont appariées sur la base de AeroportID et ArriveeAeroportID à partir des tables Aeroport et Vol respectivement.

Vous êtes toujours un peu perdu à propos de tous les JOINs ? Il existe de nombreux autres articles que vous pouvez parcourir pour obtenir de l'aide. Je vous recommande tout particulièrement l'article intitulé " Comment apprendre Les jointures en SQL ". Et si vous avez besoin de commencer à pratiquer, veuillez consulter notre article sur la façon de pratiquer Les jointures en SQL.

L'auto-jointure : un cas particulier de jointure

Comme nous l'avons vu, l'auto-jonction est un cas particulier, important de la jonction. Nous avons vu des exemples de diverses applications de l'auto-jonction, notamment le traitement d'une hiérarchie dans une table et l'appariement des lignes dans une table. Nous pouvons joindre la même table plusieurs fois, mais il est important de donner à chaque référence un alias qui indique son rôle. Ces alias de table sont utilisés pour extraire des colonnes de cette table unique en fonction du rôle pour lequel elle est référencée.

Les jointures sont une partie essentielle de SQL et une fonctionnalité très utile et fréquemment utilisée pour combiner différentes tables. Elles sont omniprésentes - ne manquez pas de consulter notre formation sur Les jointures en SQL pour maîtriser cet outil puissant !