Explique ce que sont les expressions de table communes récursives (CTE) et comment elles sont utilisées dans les requêtes SQL

Les expressions de table communes récursives (CTE) sont un type particulier d'ensemble de résultats temporaires en SQL, qui peuvent faire référence à eux-mêmes pour exécuter des requêtes récursives. Elles sont utiles pour travailler avec des structures de données hiérarchiques ou graphiques, par exemple pour parcourir un arbre de catégories, organiser des employés par subordination, etc.

Un CTE récursif se compose de deux parties:

1. Requête de base — ensemble initial de lignes.
2. Requête récursive — utilise les résultats de l'itération précédente pour obtenir le niveau suivant de données.

Exemple : obtenir tous les employés subordonnés avec l'id = 1 dans une hiérarchie d'employés.

WITH RECURSIVE Subordinates AS (
    SELECT id, manager_id, name
    FROM employees
    WHERE id = 1  -- cas de base
  
    UNION ALL
  
    SELECT e.id, e.manager_id, e.name
    FROM employees e
    INNER JOIN Subordinates s ON e.manager_id = s.id  -- cas récursif
)
SELECT * FROM Subordinates;

Cette requête sélectionne d'abord l'employé avec l'id=1, puis ajoute récursivement tous les employés dont le manager_id correspond à l'id des employés déjà trouvés, jusqu'à parcourir toute la hiérarchie.