Comment trouver tous les employés sous chaque responsable en SQL

Avez-vous déjà été chargé de créer un organigramme, c'est-à-dire de trouver les employés de chaque responsable dans une entreprise ? En SQL, vous pouvez le faire plus facilement que vous ne le pensez ! Cet article vous montrera comment obtenir la réponse en utilisant une requête récursive.

Dans le monde de l'entreprise, il est très courant de répertorier tous les employés subordonnés à un responsable donné. Cette tâche est-elle suffisamment difficile pour justifier un article entier ? Si vous vous représentez la situation sous la forme d'un organigramme, cela semble assez simple : trouvez le responsable, dressez la liste de tous ses subordonnés, et c'est tout. Quel est le problème ?

Le problème est que SQL fonctionne avec des tableaux, pas avec des organigrammes. Lorsqu'un organigramme est traduit en tableau, il peut devenir un peu difficile d'obtenir ce que vous voulez.

La technique que je vais utiliser dans cet article s'appuie principalement sur les jointures SQL et les requêtes récursives. Si vous avez besoin d'un peu de pratique (ou d'explications plus complètes), consultez nos cours sur Les jointures en SQL et les Requêtes récursives.

Examinons d'abord comment un organigramme est généralement représenté dans une table adaptée aux requêtes SQL.

Table des employés

Nous allons utiliser une table nommée employee qui comporte les colonnes suivantes :

• employee_id: L'identifiant de l'employé.
• first_name: Le prénom de l'employé.
• last_name: Le nom de famille de l'employé.
• manager_id: L'identifiant du responsable de l'employé.

Dans cet exemple, il y a dix employés. Examinons les enregistrements :

Rien de compliqué ici, juste une liste d'employés. La caractéristique principale est la colonne manager_id, qui contient l'identifiant du responsable de chaque employé. Par exemple :

Nancy Young (employee_id 4529) a un responsable. L'identifiant de son responsable est 4125, indiqué dans la colonne manager_id. Si vous recherchez cette valeur dans la colonne employee_id, vous découvrirez qu'il s'agit de l'identifiant de Mafalda Ranieri.

Maintenant, jetons un coup d'oeil à Mme Mafalda Ranieri :

Il y a une valeur NULL dans la colonne manager_id, ce qui signifie que Mafalda Ranieri n'a pas de responsable au-dessus d'elle - c'est-à-dire qu'elle est la présidente de la société.

La logique veut que chaque fois qu'il y a une valeur NOT NULL dans la colonne manager_id, l'employé a un responsable. Bien entendu, il est possible qu'un employé soit un responsable et qu'il ait également un supérieur dans la hiérarchie.

Compter tous les employés sous chaque responsable

Faisons cet exercice d'échauffement. En utilisant uniquement la table employee, comment comptez-vous tous les employés de chaque responsable ? Voici comment vous pourriez faire, l'auto-jointure de la table étant l'astuce magique :

SELECT
sup.employee_id,
	sup.first_name,
	sup.last_name,
	COUNT (sub.employee_id) AS number_of_employees
FROM employee sub
JOIN employee sup
ON sub.manager_id = sup.employee_id
GROUP BY sup.employee_id, sup.first_name, sup.last_name;

Si vous n'êtes pas trop familier avec les auto-jointures, consultez cet article qui explique les auto-jointures avec des exemples.

Lors de l'auto-jointure de la table employee, j'ai créé deux alias pour qu'il soit plus facile de suivre ce que je fais. Une table aura l'alias sub (pour l'employé subalterne) ; l'alias de l'autre sera sup (pour l'employé supérieur). Comme je cherche des données analytiques sur les responsables, j'ai demandé à la requête de retourner les colonnes employee_id, first_name, et last_name de la table sup.

Le code compte ensuite le nombre d'employés en utilisant la fonction COUNT() sur la colonne sub.employee_id. Vous obtiendriez le même résultat si vous utilisiez sup.employee_id à la place. Je voulais simplement utiliser la table sub pour expliquer cette étape de la manière suivante : "Aha, la table sub est pour les employés subalternes, donc, logiquement, je compte le nombre de subalternes dans cette table".

Comme je l'ai dit plus tôt, j'ai fait une auto-jointure sur la table employee en utilisant deux alias. La condition de jointure est sub.manager_id = sup.employee_id. Cela semble logique, car la valeur de la colonne manager_id est l'identifiant du responsable en tant qu'employé et sera naturellement dans la colonne employee_id.

J'ai utilisé la fonction d'agrégation COUNT(), et je dois donc regrouper le résultat par les colonnes employee_id, first_name et last_name.

Lorsque vous exécuterez le code, vous obtiendrez ce résultat :

Le tableau montre quatre responsables et le nombre de leurs subordonnés (number_of_employees).

Recherche de tous les subordonnés directs sous chaque responsable

La recherche des subordonnés directs est similaire au problème précédent. Il est logique de penser que si j'ai trouvé le nombre d'employés subordonnés, je peux aussi trouver leurs noms. Pour tout vous dire, la solution de cet exercice n'est qu'une variation du code précédent :

SELECT
sub.employee_id AS subordinate_id,
	sub.first_name AS subordinate_first_name,
	sub.last_name AS subordinate_last_name,
	sup.employee_id AS superior_id,
	sup.first_name AS superior_first_name,
	sup.last_name AS superior_last_name
FROM employee sub
JOIN employee sup
ON sub.manager_id = sup.employee_id
ORDER BY superior_id;

Le principe est le même : une auto-jointure est faite sur la table employee à la colonne sub.manager_id = sup.employee_id. Pour ce faire, j'utilise à nouveau les alias sub et sup.

D'abord, je prends les colonnes sub.employee_id, sub.first_name, et sub.last_name. Je les renomme pour qu'elles reflètent le fait que les données concernent les employés subalternes. Je fais la même chose avec ces colonnes pour la table sup, mais cette fois le nom reflète la position de l'employé en tant que supérieur.

Enfin, le résultat est ordonné par la colonne superior_id:

Il semble que le tableau montre ce que j'avais prévu ; nous avons les noms des employés et ceux de leurs supérieurs. Mais mon œil de lynx d'ex-auditeur remarque qu'il n'y a que neuf employés, alors que je sais qu'il y a dix employés dans l'entreprise. Comment cela se fait-il ? La raison en est Mafalda Ranieri ; elle est la présidente de l'entreprise, donc elle n'a pas de supérieur et n'est subordonnée à personne. Elle est représentée par une valeur NULL dans la colonne manager_id:

Elle est absente du résultat de la requête en raison de la nature de l'auto-jointure ; j'ai utilisé JOIN (c'est-à-dire INNER JOIN). Ce type de jointure ne renvoie pas les rangées contenant des valeurs NULL. Si vous souhaitez que ces lignes apparaissent également dans le résultat de votre requête, vous devrez utiliser LEFT JOIN au lieu de JOIN; tout le reste de la requête reste inchangé. Cette partie du code ressemblera à ceci :

...
FROM employee sub LEFT JOIN employee sup ON sub.manager_id = sup.employee_id
...

L'exécution du code modifié retournera tous les employés, même ceux qui n'ont pas de supérieur :

Cependant, ce résultat peut encore être insatisfaisant car il ne renvoie que les subordonnés directs. Dans les organisations plus complexes, certains responsables ont des subordonnés directs qui gèrent d'autres employés. Ces employés ont un responsable direct, mais ils répondent également au responsable de leur responsable. Est-il possible de choisir un responsable et d'obtenir la liste de tous ses subordonnés directs et indirects en SQL ? Bien sûr que oui, avec un peu d'aide de notre ami, la requête récursive.

Si vous n'êtes pas très familier avec le concept des requêtes récursives, il est conseillé de lire cet article expliquant le fonctionnement des requêtes récursives avant de continuer.

Trouver les subordonnés directs et indirects sous chaque responsable

Dans la table employee, nous avons un employé nommé Nancy Young. Elle n'a qu'un seul supérieur, le président de la société. Nancy est un cadre supérieur ; naturellement, elle a des subordonnés. Ses subordonnés ont également leurs propres subordonnés. Ce que je voudrais vous montrer, c'est comment obtenir tous les subordonnés, directs et indirects, d'un responsable - Nancy Young dans ce cas. Pour ce faire, je vais utiliser un CTE récursif.

Le code qui me permettra d'obtenir ce que je veux est le suivant :

WITH RECURSIVE subordinate AS (
	SELECT	employee_id,
			first_name,
			last_name,
			manager_id,
			0 AS level
	FROM employee
	WHERE employee_id = 4529

	UNION ALL

	SELECT	e.employee_id,
			e.first_name,
			e.last_name,
			e.manager_id,
			level + 1
	FROM employee e
JOIN subordinate s
ON e.manager_id = s.employee_id
)

SELECT
s.employee_id,
	s.first_name AS subordinate_first_name,
	s.last_name AS subordinate_last_name,
	m.employee_id AS direct_superior_id,
	m.first_name AS direct_superior_first_name,
	m.last_name AS direct_superior_last_name,
	s.level
FROM subordinate s
JOIN employee m
ON s.manager_id = m.employee_id
ORDER BY level;

En syntaxe SQL standard, si votre intention est d'avoir un CTE récursif, vous commencez à écrire le code en écrivant WITH RECURSIVE. Remarque importante ! Si vous utilisez SQL Server, cela ne fonctionnera pas ; vous devrez écrire la requête ci-dessus sans RECURSIVE. En d'autres termes, la première ligne du code doit ressembler à ceci :

WITH subordinate AS (
...

Maintenant, revenons à l'explication de ce que fait le code ci-dessus. Il crée un CTE récursif nommé subordinate. La première instruction SELECT de ce CTE renvoie les colonnes de la table employee. J'ai également ajouté une nouvelle colonne, level. Nancy Young sera le responsable de niveau 0 ; vous verrez plus tard l'utilité de cette colonne. Comme l'identifiant de l'employé de Nancy Young est 4529, j'ai ajouté cet identifiant dans la clause WHERE.

Je veux que le résultat de cette déclaration SELECT soit "fusionné" avec le résultat de la deuxième déclaration SELECT. Pour ce faire, les deux instructions SELECT doivent avoir le même nombre de colonnes dans le résultat. Pour que l'opération UNION ALL ait un sens, je placerai les colonnes employee_id, first_name, last_name, et manager_id dans le second SELECT.

La dernière colonne de la déclaration sera la valeur de level de la première déclaration SELECT (qui est 0). Nous ajouterons un 1 à cette valeur à chaque récursion, ce qui renverra les niveaux de hiérarchie. Cela sera utile pour trier les données et pour suivre facilement qui est le responsable de qui. J'ai relié la table employee avec l'ETC lui-même. Je traite l'ETC comme une table (ce qu'elle est), je lui donne un alias et je relie les deux tables sur e.manager_id = s.employee_id.

Enfin, j'arrive à l'instruction SELECT à l'extérieur du CTE. Dans cette partie du code, j'ai relié l'ETC lui-même avec la table employee. Elle sélectionne d'abord les colonnes employee_id, first_name, et last_name dans le CTE, car j'utilise le CTE comme source pour les données des subordonnés. J'ai également renommé ces colonnes en conséquence, pour éviter toute confusion.

L'étape suivante consiste à sélectionner les mêmes colonnes dans la table employee ; ces colonnes contiendront les données des responsables directs des employés.

Enfin, le résultat est ordonné par la colonne level, ce qui permet de trier les employés de manière hiérarchique. Une requête aussi longue ne pouvait renvoyer qu'un beau résultat ; jetez-y un coup d'œil par vous-même :

Le tableau nous montre tous les subordonnés directs et indirects de Nancy Young ainsi que leurs supérieurs directs. Examinez le tableau et vous verrez que Nancy Young a deux subordonnés directs : Jakub Hrabal et Sandra Rajkovic. Jakub n'a pas de subordonnés, mais Sandra en a deux, Moira Areas et Jon Nilssen.

Vous voyez ? Il n'est pas si facile d'obtenir quelque chose que l'on pourrait penser être simple. Heureusement, les requêtes récursives sont très pratiques dans des moments comme celui-ci.