Un guide illustré de l'auto-jonction SQL

Qu'est-ce qu'une auto-jonction SQL et comment fonctionne-t-elle ? Quand faut-il l'utiliser ? Nous allons apporter des réponses à ces questions !

En SQL, nous pouvons combiner les données de plusieurs tables à l'aide d'un opérateur JOIN. JOIN comporte plusieurs variantes ; nous avons déjà parlé de CROSS JOIN, INNER JOIN et OUTER JOIN. La plupart du temps, ces opérateurs joignent des données provenant de deux ou plusieurs tables différentes. Vous pouvez pratiquer tous les différents types de JOINs dans notre cours interactif. Les jointures en SQL cours interactif. Dans cet article, cependant, nous allons expliquer comment joindre des enregistrements d'une même table.

Une jointure automatique SQL joint les données d'une même table. En d'autres termes, elle joint une table avec elle-même. Les enregistrements pris dans la table sont mis en correspondance avec d'autres enregistrements de la même table. Pourquoi faire cela ? Vous pouvez avoir besoin de comparer une valeur avec une autre valeur de la même ligne. Vous ne pouvez pas le faire à moins de joindre la table à elle-même et de comparer les valeurs comme si elles se trouvaient dans deux enregistrements distincts.

Il n'existe pas d'opérateur spécifique pour ce type de requête. Au lieu de cela, l'auto-jonction SQL utilise l'opérateur INNER JOIN, l'un des opérateurs OUTER JOIN ou un CROSS JOIN. La différence est qu'une seule table est répertoriée à la fois comme table de gauche et de droite dans la jointure.

Examinons un exemple de jointure automatique SQL basé sur la table employee, illustré ci-dessous :

La table employee stocke les numéros d'identification, les prénoms, les noms de famille, les salaires et le numéro d'identification du patron des employés. À l'exception du patron (dont le nom est ID = 1), chacun a un superviseur. Seul le patron peut avoir une valeur NULL dans la colonne manager_id.

Essayons une requête qui renvoie le supérieur immédiat de chaque employé :

SELECT e.id, e.first_name, e.last_name,  e.salary, 
  m.first_name AS fname_boss, m.last_name AS lname_boss
FROM employee e
JOIN employee m ON  e.manager_id = m.id ;

Notez que nous avons utilisé la table employee à la fois comme table de gauche et de droite dans la jointure. Pour joindre des données provenant de la même table, nous avons dû attribuer deux alias au nom de la table.

Vous devez utiliser des alias lorsque vous effectuez des jointures automatiques. Comme vous joignez des colonnes de la même table, elles auront les mêmes noms. L'alias renomme les colonnes pour que le moteur de base de données puisse exécuter votre requête. Dans la requête ci-dessus, nous avons utilisé la lettre e comme alias pour la table de gauche. Dans ce cas, nous utilisons la table employee pour ses enregistrements d'employés. Nous avons utilisé la lettre m comme alias pour la table de droite, qui utilise la table employee pour ses enregistrements de managers. Même si nous utilisons une seule table, SQL la traite comme deux tables différentes.

Nous avons sélectionné les données relatives aux employés en sélectionnant les colonnes id, first_name et last_name dans la table "table". e "table". Les données des managers ont été sélectionnées en se référant à la m "table". Remarquez que les enregistrements des employés sont préfixés par un "e" et ceux des managers par un "m". Nous pouvons maintenant les joindre car nous avons utilisé une condition de jointure appropriée : la colonne manager_id de la table e a été comparée à la colonne id de la table m. Les enregistrements des deux tables ont été mis en correspondance à l'aide d'un opérateur JOIN (ou INNER JOIN; n'oubliez pas que le "INNER" est implicite).

Avez-vous remarqué que le patron n'apparaît pas dans les résultats ? Il n'y a pas de correspondance dans la table de droite "manager", donc aucune donnée sur le supérieur du patron n'a pu être trouvée.

L'image ci-dessous montre comment les enregistrements sont mis en correspondance pendant l'exécution de cette requête.

Analysons les données dans la table de sortie :

Dans notre table, le site JOIN n'a pas retourné d'enregistrement pour John Watson. Il n'a pas d'identifiant pointant vers l'id de son supérieur, donc il est le patron. Nous devrions utiliser un LEFT JOIN au lieu d'un INNER JOIN si nous voulions retourner des données pour tous les employés, y compris le patron. Dans ce cas, les enregistrements de la table de gauche qui ne correspondent pas à ceux de la table de droite seraient également affichés :

SELECT e.id, e.first_name, e.last_name,  e.salary, m.first_name 
   AS fname_boss  , m.last_name  AS lname_boss
FROM employee e
LEFT JOIN employee m ON  e.manager_id = m.id ;

Notre cours interactif Les jointures en SQL contient une section entière consacrée à la pratique des jointures automatiques.

Quand utiliser l'auto-jonction en SQL ?

Les jointures automatiques sont couramment utilisées dans les domaines suivants :

• Relations hiérarchiques
• Relations séquentielles
• Données graphiques

Nous allons examiner chacun de ces domaines individuellement.

Données hiérarchiques

Le traitement des données hiérarchiques est l'une des applications les plus fréquentes de la jointure automatique SQL. Cela se produit lorsqu'il existe une colonne supplémentaire pointant vers un identifiant dans la même table, comme dans notre table employee table. Dans notre cas, la colonne manager_id fait référence à (a la même valeur que) la colonne id.

L'exemple donné ci-dessus (la relation employé-manager) n'est pas la seule situation où des données hiérarchiques sont utilisées. A nomenclature pour une voiture partage une structure similaire. Chaque voiture est constituée de plusieurs composants, tels que le moteur, le système de freinage et le système électrique. Chacun de ces composants est constitué de parties plus petites. Le système électrique d'une voiture peut être décomposé en ses composants, tels que la batterie et l'alternateur (qui peuvent être décomposés en encore plus de pièces automobiles). Cela signifie que les pièces automobiles constituent un groupe de données hiérarchiques. Le tableau car tableau ci-dessous présente quelques données sur les pièces automobiles.

La relation parent-enfant est un autre exemple de données hiérarchiques. En stockant cette relation, nous pouvons utiliser une seule table pour héberger un arbre généalogique entier. Nous pouvons ensuite utiliser une auto-jonction SQL pour récupérer facilement les données relatives aux ancêtres d'une personne donnée.

Le tableau ci-dessous peut nous aider à identifier rapidement le ou les ancêtres les plus âgés d'une famille. Ces personnes n'ont pas de données sur les ancêtres dans leurs enregistrements, ce qui signifie qu'elles constituent la racine de l'arbre généalogique.

Le tableau person tableau présenté ci-dessous l'illustre :

La requête ci-dessous récupère le prénom et le nom de chaque personne ainsi que le prénom et le nom de leur mère et de leur père.

SELECT c.first_name, c.last_name, m.first_name AS fname_mother, m.last_name  AS lname_mother
FROM family c
LEFT JOIN person m ON  c.mather_id = m.id 
LEFT JOIN person f ON  c.father_id = f.id ;

Notez que nous avons dû utiliser une jointure automatique trois fois (c'est-à-dire sur trois "tables" ou alias) pour obtenir les données sur le père et la mère. John Watson, Anne Brown et Jacob Miller n'ont pas de données pointant vers leurs ancêtres.

Les exemples de structures hiérarchiques évoqués ci-dessus stockent les données selon une approche d'enregistrement supérieur-inférieur. Cela nous permet de présenter les données sous forme d'une structure arborescente. Vous en apprendrez davantage sur le traitement des structures arborescentes en SQL au fur et à mesure de votre apprentissage des bases de données relationnelles.

Données séquentielles

Les données séquentielles peuvent également bénéficier de l'utilisation d'une jointure automatique SQL. Par exemple, supposons que vous ayez des enregistrements décrivant les étapes consécutives nécessaires à la préparation d'un plat. Toutes les étapes peuvent être placées dans une seule table. Leur ordre est déterminé en fonction des colonnes qui pointent vers les ID des enregistrements précédents et suivants dans la même table.

Illustrons cela à l'aide du instruction tableau :

Comme vous le voyez, dans l'ordre actuel, ces instructions n'ont pas de sens. Mais lorsque nous utilisons une jointure automatique SQL pour montrer la relation séquentielle pour chaque étape de la recette, nous obtenons :

1. Préchauffez un four à 220 degrés C.
2. Épluchez quatre pommes de terre.
3. Coupez les pommes de terre en tranches.
4. Mélanger les tranches de pommes de terre avec de l'huile.
5. Faites-les cuire dans le four préchauffé pendant 20 minutes.
6. Assaisonnez les tranches chaudes avec du sel et du poivre.

On obtient aussi de savoureuses frites au four !

Graphiques

LeSQL auto-joint peut également être utilisé pour montrer les relations nécessaires aux graphiques. Un graphe est une structure constituée de nœuds reliés entre eux par des arêtes (relations). Un exemple de graphe est le réseau routier entre plusieurs villes.

Regardez le dessin ci-dessous.

Ce graphique représente cinq villes qui sont reliées entre elles. Chaque flèche indique une route reliant une ville à une autre. Dans ce cas, les villes sont les nœuds et les routes qui les relient sont les arêtes. Nous utilisons deux tables pour stocker ces données. La table city stocke le numéro d'identification et le nom de chaque ville. La table route contient le numéro d'identification de la route, la ville de départ (la colonne from_city_id ) et la ville cible (la colonne to_city_id ).

Voici la table "ville" :

Et voici la table "route" :

Nous pouvons utiliser une jointure automatique SQL sur la table city ainsi que l'adresse INNER JOIN des deux tables, pour trouver les itinéraires existant entre les villes.

Jetez un coup d'œil à la requête.

SELECT c1.name AS from_city, c2.name AS to_city 
FROM city c1
JOIN route r ON c1.id = r.from_city_id
JOIN city c2  ON c2.id = r.to_city_id ;

Les colonnes city et route ont été jointes en utilisant la colonne id de city et la colonne from_city_id de route. À ce stade, nous ne pouvons récupérer que le nom de la ville de départ. Afin de récupérer le nom de la ville cible, nous avons utilisé une auto-jonction sur la table city pour retrouver le nom de la ville cible. Cette fois, nous avons comparé la colonne id de la table aliasée city avec la colonne to_city_id de la table route de la table.

Voici le résultat :

Vous pouvez également utiliser une auto-jonction SQL dans les sous-requêtes récursives qui stockent des graphiques. Vous trouverez plus d'informations sur ce sujet dans notre nouveau cours, "Requêtes récursives".

Utilisation de l'auto-jonction SQL pour trouver les valeurs dupliquées

Les jointures automatiques peuvent également être utilisées pour identifier les valeurs en double dans une table. Présentons un exemple de table appelée color:

Chaque enregistrement de la table est différent grâce à la colonne id, qui doit toujours être unique. Mais cela n'empêche pas deux lignes de stocker le même nom de couleur. Nous voulons identifier de tels cas et trouver les ID des noms de couleur en double. Essayons cela :

SELECT c1.id AS id1, c1.name  AS color1, c2.id AS id2, c2.name AS color2
FROM color c1
JOIN color c2 ON c1.name = c2.name AND c1.id < c2.id   ; 

Nous avons pu trouver les noms de couleur en double parce que nous avons auto-joint des enregistrements basés sur le nom de la couleur. La deuxième condition est utilisée pour ignorer les enregistrements identiques des deux tables ainsi que les mêmes paires d'enregistrements en ordre inverse.

Regardez le résultat de la requête :

Il est maintenant facile de voir qu'il y a des valeurs en double pour le bleu et le jaune.

En savoir plus

Si vous souhaitez en savoir plus sur les JOIN que ce que nous avons abordé dans ce guide illustré des auto-joints SQL, consultez notre cours Les jointures en SQL . N'oubliez pas que la meilleure façon d'apprendre Les jointures en SQL - ou toute autre chose - est d'apprendre par la pratique !