Une CTE (Common Table Expression), définie avec WITH, est un ensemble de résultats temporaire nommé que vous pouvez référencer dans une requête. Les CTE rendent les requêtes complexes plus lisibles en les décomposant en étapes nommées et logiques — et elles permettent les requêtes récursives (pour les hiérarchies).
active_users (
id, name users active
)
active_users
name ;
Le WITH ... AS (...) définit une CTE — une requête nommée que vous référencez plus tard. C'est comme une sous-requête, mais nommée et lisible, particulièrement pour les requêtes complexes.
WITH
monthly_sales AS (
SELECT month, SUM(amount) AS total FROM orders GROUP BY month
),
avg_sales AS (
SELECT AVG(total) AS avg_total FROM monthly_sales -- a CTE can reference another
)
SELECT m.month, m.total
FROM monthly_sales m, avg_sales a
WHERE m.total > a.avg_total; -- months above the average
Plusieurs CTE décomposent une requête complexe en étapes lisibles et séquentielles — chacune s'appuyant sur la précédente. C'est beaucoup plus clair que les sous-requêtes profondément imbriquées.
Nested subqueries → hard to read when deeply nested
CTEs → name each step, read top-to-bottom like a sequence → much clearer
→ CTEs are often preferred for COMPLEX queries purely for readability/maintainability.
-- traverse a hierarchy (e.g. all descendants of a category, an org chart)
WITH RECURSIVE subordinates AS (
SELECT id, name, manager_id FROM employees WHERE id = 1 -- base case (the top)
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN subordinates s ON e.manager_id = s.id -- recursive step
)
SELECT * FROM subordinates; -- the whole tree under employee 1
Les CTE récursives (WITH RECURSIVE) parcourent les données hiérarchiques/arborescentes (organigrammes, arbres de catégories, nomenclatures, chemins de graphe) — quelque chose qui est sinon très difficile en SQL.
Les CTE sont une fonctionnalité SQL importante pour la lisibilité et la récursion, et les comprendre est précieux pour écrire des requêtes complexes maintenables et pour gérer les données hiérarchiques.
Le bénéfice principal quotidien est la lisibilité : les requêtes complexes avec plusieurs niveaux de sous-requêtes deviennent difficiles à lire et à maintenir, tandis que les CTE vous permettent de décomposer la requête en étapes logiques nommées et séquentielles (lues de haut en bas comme une série d'opérations), améliorant dramatiquement la clarté — pour cette raison, les CTE sont souvent préférées aux sous-requêtes profondément imbriquées pour les requêtes complexes, ce qui en fait un outil important pour écrire du SQL maintenable.
Également important est la capacité CTE récursive (WITH RECURSIVE), qui permet d'interroger les données hiérarchiques et arborescentes (organigrammes, arbres de catégories, nomenclatures, traversée de graphes) — une classe de requêtes qui est autrement très difficile ou impossible en SQL standard, ce qui rend les CTE récursives la solution standard pour la traversée de données hiérarchiques.
Comprendre les deux usages — les CTE pour décomposer les requêtes complexes en étapes lisibles, et les CTE récursives pour la traversée hiérarchique — couvre la valeur pratique.
Puisque les requêtes complexes sont courantes dans les applications réelles (et la lisibilité/maintenabilité sont importantes), et puisque les données hiérarchiques (que les CTE récursives gèrent particulièrement bien) apparaissent fréquemment (structures organisationnelles, catégories imbriquées, données filetées), comprendre les CTE est une connaissance précieuse de niveau senior qui améliore la maintenabilité des requêtes et déverrouille l'interrogation hiérarchique — un outil fréquemment pertinent pour écrire du SQL propre et sophistiqué et un sujet courant qui démontre une capacité SQL avancée, particulièrement la forme récursive pour les données arborescentes.