Comment joindre deux fois la même table

JOIN est l'une des instructions les plus courantes en SQL. Comme vous le savez peut-être, elle est utilisée pour joindre et combiner les données de deux ou plusieurs tables en un seul ensemble de données commun. Dans cet article, je vais aborder des types particuliers de jointures ? dans lesquels vous combinez deux fois la même table, y compris la jointure d'une table à elle-même, également connue sous le nom de jointure automatique. Quand et pourquoi avez-vous besoin de faire cela ? Comment l'écrire en SQL ? C'est ce que nous allons découvrir.

Jointures : Un examen rapide

Vous êtes probablement familier avec les jointures en SQL. Vous avez deux tables, A et B, et vous les combinez en utilisant une colonne commune aux deux. Voici un exemple :

Nous avons deux tables : customer et cityavec une colonne commune nommée city_id.

Maintenant, si vous voulez les joindre pour obtenir les noms de ville respectifs des clients, vous pouvez le faire avec une jointure comme celle-ci :

select customer.customer_id,
     customer.firstname,
     customer.lastname,
     customer.birthdate,
     customer.spouse_id,
     customer.city_id,
     city.name as city_name
from customer 
join city 
on customer.city_id = city.city_id;

Dans cette instruction JOIN, nous faisons correspondre les enregistrements de customer et city par une clé (city_id). Nous récupérons les 6 colonnes de la table customer et une colonne, name, de la table city de la table. Il existe plusieurs types de jointures en SQL ; cet exemple fait une INNER JOIN.

Je ne vais pas me plonger ici dans la syntaxe de JOIN. Pour en savoir plus, consultez notre cours interactif sur les jointures SQL que vous pouvez trouver sur LearnSQL.fr.

Le résultat de cette jointure sera une table avec les 6 champs de la table customer plus un champ supplémentaire de la table city table :

Joindre la même table plusieurs fois

Maintenant que nous avons fait un rapide tour d'horizon, examinons des jointures plus complexes.

Parfois, vous devez joindre la même table plusieurs fois. En général, il s'agit d'ajouter une ou plusieurs colonnes à un ensemble de résultats provenant de la même table, mais à des enregistrements différents ou par des colonnes différentes. Nous allons examiner deux scénarios de ce type : la jointure d'une table à elle-même et la jointure de tables avec des relations multiples.

Auto-jonction : Joindre une table à elle-même

Une auto-jonction est un cas particulier de la jointure. Au lieu de joindre deux tables différentes, vous joignez une table à elle-même. Pourquoi faire cela ?

Dans notre exemple ci-dessus, nous voulions ajouter une colonne de la table city le nom de la ville, à la table customer de la table. Nous avons donc joint deux tables différentes l'une à l'autre. Faire une auto-jonction signifierait, par exemple, joindre la table à elle-même. customer à elle-même.

Voici la table customer à titre d'aide-mémoire :

La colonne spouse_id stocke le customer_id du conjoint du client. Par exemple, les clients 1 et 2 (John et Mary) sont conjoints l'un de l'autre, les clients 3 et 5 (Lisa et Tim) sont conjoints l'un de l'autre, et ainsi de suite. Nous pouvons ajouter le prénom et le nom de famille du conjoint à chaque enregistrement de la table customer table. Pour ce faire, nous devons effectuer une jointure automatique, c'est-à-dire joindre la table customer à elle-même :

select cust.customer_id,
      cust.firstname,
      cust.lastname,
      cust.birthdate,
      cust.spouse_id,
      spouse.firstname as spouse_firstname,
      spouse.lastname as spouse_lastname
from customer cust 
join customer spouse
on cust.spouse_id = spouse.customer_id;

Lorsque vous exécutez ce code, le résultat est le suivant :

Maintenant que vous avez vu un exemple d'utilisation de la jointure automatique, passons en revue sa syntaxe SQL.

Syntaxe de la jointure automatique

La syntaxe de la jointure automatique est très similaire à celle de tout autre type de jointure. Voici le code de notre exemple de jointure automatique :

select cust.customer_id,
      cust.firstname,
      cust.lastname,
      cust.birthdate,
      cust.spouse_id,
      spouse.firstname as spouse_firstname,
      spouse.lastname as spouse_lastname
from customer cust
join customer spouse
on cust.spouse_id = spouse.customer_id;

Il s'agit d'une instruction JOIN dans laquelle la customer est utilisée deux fois.

Les informations relatives au conjoint, telles que son nom, sont stockées dans la même table en tant que client distinct, avec son propre customer_id. Puisque spouse_id contient le customer_id du conjoint, nous devons joindre la table avec elle-même pour obtenir le nom du conjoint.

Vous pouvez considérer une jointure automatique comme une jointure entre deux copies de la même table. Pour chaque enregistrement contenant une valeur non nulle dans spouse_id, nous recherchons la valeur de customer_id qui correspond à cette valeur. Lorsque nous trouvons une correspondance, les colonnes firstname et lastname sont ajoutées à la table résultante.

Les alias de table sont nécessaires dans une auto-jonction. Le code ne fonctionne pas sans eux, car il ne saurait pas à quelle copie de la table vous faites référence. Ici, j'utilise les alias cust et spouse.

Juste avant le mot-clé FROM, nous choisissons les colonnes que nous voulons conserver dans la table résultante. Nous devons utiliser les alias de la table pour récupérer les colonnes (cust.firstname, cust.lastname, spouse.firstname, etc.). Nous conservons cinq colonnes de la table customer et ajoutons, à partir de la même table, deux colonnes contenant le nom du conjoint.

Il s'agit d'une jointure interne, mais vous pouvez utiliser n'importe quel type de jointure : LEFT JOIN, RIGHT JOIN, CROSS JOIN, etc. Dans notre exemple, nous avons une jointure interne qui renvoie uniquement les enregistrements correspondants, c'est-à-dire que seuls les clients ayant un conjoint sont renvoyés. Donna n'a pas de conjoint dans la table, elle n'est donc pas incluse dans l'ensemble de données résultant.

Si vous voulez en savoir plus sur les jointures, je vous recommande notre article "Comment apprendre Les jointures en SQL."Il existe également un cours interactif sur les jointures SQL disponible sur notre LearnSQL.fr plateforme.

Exemples de jointure automatique

L'exemple ci-dessus n'est qu'un exemple. Lorsque vous commencez à utiliser SQL au quotidien, vous êtes souvent confronté à la nécessité d'utiliser des jointures automatiques.

Un cas d'utilisation courant de l'auto-jonction est celui où il existe une hiérarchie entre les enregistrements d'une table. Ce type de structure de données est appelé une structure arborescente, et vous avez souvent besoin de joindre la table avec elle-même en SQL. Voici quelques exemples.

Exemple 1 : hiérarchie des employés

Chaque employé a un responsable, et un responsable a à son tour son responsable, le tout dans la même table. Si vous souhaitez ajouter les informations relatives au responsable correspondant à chaque enregistrement, vous devez effectuer une auto-jonction. Nous traitons cet exemple dans l'article "An Illustrated Guide to the SQL Self Join", alors jetez-y un coup d'œil pour voir à quoi cela ressemble.

Exemple 2 : Hiérarchie des départements

Chaque service d'une organisation a un parent : par exemple, le service des sciences des données dépend du service informatique, le service informatique dépend de l'assistance commerciale et l'assistance commerciale dépend du conseil d'administration.

Considérons la table suivante, departmentqui ressemble à ceci :

Maintenant, si vous voulez ajouter le nom du parent à chaque département, vous devez écrire une jointure automatique :

select c.*,
   p.name as parent_name
from department c 
left join department p 
on c.parent_department_id=p.department_id;

Dans cette SELECT, nous joignons la table department avec elle-même pour obtenir le nom du département parent comme champ supplémentaire. Notez que l'enregistrement dont department_id est 1 n'a pas de parent (parent_department_id est NULL; il n'est pas renseigné). Cela s'explique par le fait que le conseil d'administration se trouve au sommet de l'arborescence. Nous voulons afficher cet enregistrement dans le résultat, donc nous utilisons un LEFT JOIN et non un INNER JOIN.

Lorsque vous exécutez ce code, la table résultante ressemble à ceci :

Vous pouvez facilement voir le parent auquel chaque service appartient : IT est sous Administration, Administration est sous le Conseil, etc.

Exemple 3 : Hiérarchie des catégories

Prenons l'exemple des petites annonces : ces pages Web populaires où vous pouvez louer, acheter ou vendre n'importe quoi, de l'immobilier aux produits et services divers. Pour placer une annonce, vous choisissez une catégorie et une sous-catégorie pour votre annonce. Par exemple, si vous vendez des biens immobiliers, vous choisirez parmi des sous-catégories telles que maison, appartement ou terrain.

Nous avons une table nommée category qui contient des informations sur ces catégories et sous-catégories ainsi que sur leurs relations. Les relations entre les catégories et sous-catégories de cette table sont stockées dans une structure parent-enfant comme celle-ci :

Toutes les catégories et sous-catégories se trouvent dans ce seul tableau. Maintenant, si vous voulez ajouter des informations sur le parent à chaque enregistrement, vous devrez faire une auto-jonction - joindre cette table à elle-même :

select subcategory.*,
    main.category_name as parent_name
from category subcategory 
left join category main 
on subcategory.parent_category_id = main.category_id;

Voici le résultat de l'exécution de cette instruction SQL :

Relations multiples entre deux tables

Outre l'auto-jonction, il existe des situations dans lesquelles vous devez joindre la même table plusieurs fois. C'est le cas lorsque vous avez des relations multiples entre deux tables différentes. Dans ce cas, vous joignez la même table deux fois, mais généralement à une autre table et pas nécessairement à elle-même.

Supposons que la table customer possède deux champs qui contiennent les identifiants des villes. C'est courant si vous avez deux villes différentes pour chaque client... par exemple, la ville de résidence (residence_city_id) et la ville de l'adresse postale où les avis doivent être envoyés (notice_city_id) :

Nous avons également city qui contient l'identifiant de la ville (city_id) et le nom de la ville (name), comme nous l'avons vu précédemment et comme nous le rappelons ci-dessous :

Maintenant, si vous voulez afficher les noms des villes, vous devrez joindre la table city deux fois :

select cust.customer_id,
      cust.firstname,
      cust.lastname,
      cust.birthdate,
      cust.residence_city_id,
      cust.notice_city_id,
      residence_city.name as residence_city_name,
      notice_city.name as notice_city_name
from customer cust 
join city residence_city 
on cust.residence_city_id=residence_city.city_id
join city notice_city 
on cust.notice_city_id=notice_city.city_id;

Décomposons ce qui se passe dans ce code. Tout d'abord, nous joignons customer et city avec residence_city_id comme clé. Nous obtenons residence_city_name en le faisant correspondre à city_id dans la table. city table. Une deuxième jointure est effectuée entre customer et city pour obtenir notice_city_name. La clé utilisée ici est notice_city_id, qui correspond également à city_id dans la table. city table.

Nous utilisons des alias de table cust pour customer, residence_city pour la première copie de city pour obtenir le nom de la ville de résidence, et notice_city pour la deuxième copie de city pour obtenir le nom de la ville de notification. Nous utilisons les alias pour définir les colonnes de la table résultante. Les alias sont également utilisés pendant la jointure pour définir les colonnes clés. Encore une fois, les alias sont nécessaires pour distinguer les deux copies de city.

Lorsque vous exécutez ce code, vous obtenez le résultat suivant :

Nous avons maintenant deux colonnes supplémentaires avec les noms de ville correspondants.

Nous utilisons ici des jointures simples (c'est-à-dire internes), mais vous pouvez utiliser n'importe quel type de jointure selon vos besoins. Si vous êtes novice en matière de jointures SQL et que vous souhaitez en savoir plus sur leurs différents types, je vous recommande les articles "Comment apprendre les jointures" et "Comment pratiquer les jointures" qui traitent de ces sujets. Si vous préférez apprendre en regardant des vidéos. Je vous recommande vivement l'épisode qui traite des jointures.

Joindre deux fois la même table

Dans cet article, nous avons abordé les cas où il est nécessaire de joindre deux fois la même table en SQL, ainsi que certains cas d'utilisation courante. Nous avons expliqué comment procéder et à quoi ressemble la syntaxe SQL. Les auto-jointures avec des données hiérarchiques et les relations multiples entre deux tables ne sont que deux des situations pour lesquelles vous devez joindre deux fois la même table. Il en existe d'autres ; en général, elles consistent à ajouter une ou plusieurs colonnes à un ensemble de résultats provenant de la même table dans la même colonne.

Si vous souhaitez en savoir plus sur les jointures, y compris les auto-jointures, je vous recommande notre cours interactif Joints SQL disponible sur notre LearnSQL.fr plateforme. Lorsqu'il s'agit de SQL, il est important de s'exercer ; notre cours est conçu à cet effet !