Comment obtenir les descendants d'un parent en SQL

Vous voulez apprendre à manipuler les arbres généalogiques et à trouver les descendants d'un parent ? En lisant cet article, vous apprendrez à manipuler des données hiérarchiques.

La recherche des descendants d'un parent est un problème courant en SQL. Si vous imaginez un arbre généalogique, l'élément de base qui forme les relations à l'intérieur de celui-ci est la relation parent-enfant. La relation parent-enfant est précisément ce qui définit toutes les données hiérarchiques.

Un autre exemple de données hiérarchiques est la relation manager-employé. Vous pouvez trouver des données hiérarchiques dans de nombreuses autres situations, comme des données détaillées sur un ou plusieurs projets, leurs sous-projets et chaque tâche pouvant être décomposée en sous-tâches. Un autre exemple de données hiérarchiques est celui de la construction d'une classification des êtres vivants dans laquelle vous classez les créatures en fonction du royaume, de l'embranchement, des classes, de l'ordre, des familles, du genre et de l'espèce. Ou bien vous étudiez un genre musical et ses sous-genres. Par exemple, le blues peut être divisé en sous-genres tels que le Delta blues, le country blues, le jump blues et le Chicago blues. Ce dernier a influencé un nouveau style, la musique soul. La musique soul a ses sous-genres, comme la Southern soul, la Motown soul, la Philly soul, la progressive soul, etc., qui ont à leur tour influencé la création d'autres genres, comme le funk ou le disco.

Cet article couvre les sujets que vous pouvez apprendre plus en détail dans le cours Requêtes récursives . Un guide décrit comment ces connaissances peuvent vous être utiles; sa lecture peut vous aider à décider d'apprendre les requêtes récursives.

Conception d'une table hiérarchique

Vous remarquerez une caractéristique spécifique des tables contenant des données avec des relations parent-enfant : deux colonnes contenant des valeurs d'identification. Une colonne est l'ID utilisé pour l'identification unique des données dans la table. L'autre colonne contient l'ID utilisé pour faire référence à une autre ligne.

Laissez-moi vous montrer ce que j'entends par là. Le tableau que vous utiliserez s'appelle family_treeavec les colonnes suivantes :

• id: L'ID de la personne et la clé primaire (PK) de la table.
• first_name: Le prénom de la personne.
• last_name: Le nom de famille de la personne.
• parent_id: L'identifiant du parent de la personne.

Voici quelques données de la table :

Vous voyez que l'ID de John McArthur est 2. L'ID de Sam McArthur est 5, tandis que son parent_id = 2. Cela signifie que son parent a l'ID 2 ; dans ce cas, il s'agit de John McArthur.

Cette structure de données est caractéristique des données hiérarchiques, qui sont assez courantes dans les bases de données. Pour obtenir des données d'une telle table, vous devrez utiliser des requêtes hiérarchiques ou récursives. Si vous voulez savoir ce que les requêtes récursives peuvent faire, passez à l'article révélant leur puissance. Je vais être plus précis et vous montrer comment structurer une requête récursive pour obtenir tous les descendants d'un parent dans le tableau ci-dessus.

Requête récursive : Récupération des descendants

Tout d'abord, laissez-moi vous montrer à quoi ressemble la requête. Ensuite, je vais la décomposer pour vous, analyser toutes les parties de la requête et expliquer leur but.

C'est parti ! La requête qui vous donnera tous les descendants d'un parent est celle-ci :

WITH RECURSIVE descendant AS (
	SELECT	id,
			first_name,
			last_name,
			parent_id,
			0 AS level
	FROM family_tree
	WHERE id = 1

	UNION ALL

	SELECT	ft.id,
			ft.first_name,
			ft.last_name,
			ft.parent_id,
			level + 1
	FROM family_tree ft
JOIN descendant d
ON ft.parent_id = d.id
)

SELECT	d.id AS descendant_id,
		d.first_name AS descendant_first_name,
		d.last_name AS descendant_last_name,
		a.id AS ancestor_id,
		a.first_name AS ancestor_first_name,
		a.last_name AS ancestor_last_name,
		d.level
FROM descendant d
JOIN family_tree a
ON d.parent_id = a.id
ORDER BY level, ancestor_id;

La requête commence par définir le CTE. Les trois lettres signifient Common Table Expression (expression de table commune) et vous reconnaissez une CTE lorsque vous voyez le mot WITH. Vous trouverez plus de détails théoriques sur les CTE et leur syntaxe dans un autre article.

Les CTE peuvent être récursifs ou non récursifs. Vous vous demandez comment faire la différence entre les deux ? C'est plus simple que vous ne le pensez ; ceux qui commencent par WITH RECURSIVE sont récursifs.

Lorsque vous écrivez un CTE, vous devez spécifier son nom. Dans mon cas, le CTE est nommé descendant par la ligne de code suivante : WITH RECURSIVE descendant AS. Ce qui suit est une instruction SELECT entre parenthèses, qui est stockée comme un CTE. Dans cette instruction SELECT, je sélectionne toutes les colonnes de la table family_tree. J'ajoute également une nouvelle colonne nommée level qui contiendra la valeur 0. Soyez indulgent avec moi ; vous verrez l'utilité de cette étape dans un instant. Enfin, j'utilise une clause WHERE pour filtrer les résultats. Ce qui m'intéresse, c'est de trouver tous les descendants d'un certain Peter McArthur, dont le nom est id = 1.

Ensuite, je "fusionne" cette déclaration SELECT avec la suivante en utilisant UNION ALL. Il s'agit d'une commande qui vous aide à combiner les résultats de deux ou plusieurs déclarations SELECT. Pour que UNION ALL fonctionne, toutes les instructions SELECT doivent avoir le même nombre de colonnes. L'instruction SELECT suivante sélectionne à nouveau toutes les colonnes de la table family_tree. De plus, elle prend la valeur de la colonne level (de l'instruction précédente SELECT, 0) et ajoute 1 à chaque récurrence. Les données de cette instruction SELECT sont extraites en joignant la table family_tree avec le CTE, traité ici comme n'importe quelle autre table. Puisque les données du CTE sont les mêmes que celles de la table family_treeà l'exception de la nouvelle colonne level, cette JOIN joint essentiellement la table family_tree avec elle-même sur les colonnes parent_id et id.

En fermant les parenthèses, vous avez terminé de définir le CTE. Vient maintenant l'instruction SELECT, qui utilise le CTE et renvoie tous les descendants de chaque parent. Dans cette instruction SELECT, je sélectionne d'abord les colonnes id, first_name et last_name dans le descendant du CTE. Le CTE est traité comme une table normale, avec d comme alias. Je la joins à la table family_tree avec l'alias a. J'ai choisi cet alias parce que je traite l'ECC comme la table pour les données des descendants et comme la table contenant les données des ancêtres. family_tree comme la table contenant les données des ancêtres. Il s'agit d'une simple astuce qui me permet de ne pas me tromper lors de l'écriture d'une requête. Les colonnes sélectionnées dans la table family_tree sont à nouveau id, first_name, et last_name. La dernière colonne sélectionnée est level à partir du CTE.

À la fin, les données sont ordonnées par les colonnes level et ancestor_id. Voici le résultat :

Ce sont tous les descendants de Peter McArthur dont l'ID est 1. Les personnes au niveau 1 sont les enfants de Peter McArthur. Ses petits-enfants sont au niveau 2, avec leurs parents indiqués dans le tableau. Le niveau 3 présente les arrière-petits-enfants de Peter McArthur, dont les parents sont également indiqués par leur nom.

En sachant comment écrire la requête ci-dessus, vous pouvez l'appliquer à tout autre scénario dans lequel il y a une structure de données hiérarchique.

Conseils supplémentaires

Écriture de CTE récursifs dans Microsoft SQL Server

Si vous essayez d'exécuter la requête ci-dessus dans SQL Server, une erreur s'affichera. Pas de panique, SQL Server prend en charge les CTE et les requêtes récursives. La seule différence est que vous n'avez pas besoin d'écrire RECURSIVE dans SQL Server pour obtenir une requête récursive. Il suffit de l'omettre, comme ceci :

WITH descendant AS...

Tout le reste est identique ; il n'est pas nécessaire de modifier une autre partie de la requête. Si vous utilisez Oracle, voici l'article expliquant l'utilisation des requêtes hiérarchiques.

Récupération des valeurs NULL dans la table

Si vous examinez de plus près la table indiquant les descendants de Peter McArthur, vous remarquerez qu'il n'y a pas de Peter McArthur lui-même. Il ne s'agit pas d'une erreur ; c'est simplement parce qu'il n'est pas son propre descendant. Cependant, vous voudrez peut-être le montrer aussi, afin d'avoir l'arbre généalogique complet du premier ancêtre au dernier descendant.

Pour ce faire, vous devez d'abord savoir pourquoi Pierre n'apparaît pas dans le tableau résultant. La raison en est que j'ai utilisé JOIN pour connecter les tables. Comme Peter McArthur est le point de départ de l'arbre généalogique, il y a une valeur NULL dans la colonne parent_id. Le site JOIN ne renvoie pas les valeurs NULL.

Il y a juste une petite chose que vous devez changer dans la requête pour obtenir les valeurs NULL. Au lieu d'utiliser JOIN, utilisez simplement LEFT JOIN. Voici la partie du code qui change ; tout le reste reste est inchangé :

...FROM descendant d LEFT JOIN family_tree a ON d.parent_id = a.id...

Le résultat ne diffère que sur une seule ligne, à savoir la première :

Vous avez compris comment trouver les descendants d'un parent ?

Dans cet article, vous avez appris à connaître la structure de données hiérarchique et la relation parent-enfant. Je vous ai donné plusieurs exemples concrets où vous pouvez trouver de telles structures de données. Ce ne sont pas les seuls, bien sûr.

Vous avez appris la structure caractéristique de telles données, en vue d'écrire une requête vous permettant d'obtenir tous les descendants d'un ancêtre. À partir de cet exemple, vous devriez être en mesure d'appliquer ces connaissances dans des scénarios similaires. Pour être encore plus compétent dans l'écriture de requêtes récursives et savoir quand les utiliser, il est fortement recommandé de s'entraîner avec le cours Requêtes récursives . L'article expliquant quand utiliser les CTE peut également vous aider à vous améliorer avec les CTE.