3 cas d'utilisation des Common Table Expressions (CTEs) sur SQL

Comment simplifier des requêtes complexes

Une CTE (Common Table Expression) est une vue temporaire définie par une requête commençant par la clause WITH. Elle peut être référencée dans la requête principale.

WITH cte AS (
 -- Requête définissant la CTE
 SELECT colonne1 FROM my_table
)

-- Requête principale
SELECT * FROM cte 

L’un des intérêts des CTEs est de rendre le code plus lisible.

Dans cette article, nous allons voir 3 cas d’utilisation des CTEs.

1. S’affranchir des sous-requêtes grâce aux CTEs

En règle général, les CTEs sont à privilégier par rapport aux sous-requêtes.

Prenons cette exemple :

Si on cherche à avoir le prénom des managers qui encadrent au moins 4 employés, on peut l’obtenir de plusieurs façons différentes.

1. Avec une sous-requête

SELECT name
FROM Employee AS e1
JOIN
(SELECT managerId
FROM Employee
GROUP BY managerId
HAVING count(*) >= 4) AS e2
ON e1.id = e2.managerId

2. Avec une CTE

WITH Manager AS (
 SELECT managerId
 FROM Employee
 GROUP BY managerId
 HAVING count(*) >= 4
)

SELECT name 
FROM Employee 
JOIN Manager 
ON Employee.id = Manager.managerId

La CTE est plus lisible, elle peut être renommée et elle est facilement réutilisable dans la requête principale.

2. Créer de multiples CTEs

Dans le cas de requêtes complexes, on peut avoir besoin de plusieurs CTEs. Il est alors possible de définir ces CTEs à la chaine.

WITH
 -- Requête définissant la 1ère CTE
 cte1 AS (SELECT * FROM my_table1),
 -- Requête définissant la 2e CTE
 cte2 AS (SELECT * FROM my_table2)

-- Requête principale
SELECT * FROM cte1 UNION SELECT * FROM cte2

En reprenant l’exemple vu plus haut, on aurait :

WITH 
 Manager AS (
 SELECT managerId
 FROM Employee
 GROUP BY managerId
 HAVING count(*) >= 4),

 Employee1 AS (
 SELECT id, name
 FROM Employee)

SELECT name 
FROM Employee1 
JOIN Manager 
ON Employee1.id = Manager.managerId

3. Traiter des données hiérarchiques

Imagine que tu as un organigramme et que pour chaque employé tu souhaites savoir quel est son manager et le manager de son manager.

Pour ce faire, tu peux utiliser les CTEs récursives.

Une CTE récursive est une CTE qui se référence elle-même. On utilise alors le mot clé RECURSIVE. La syntaxe est la suivante :

WITH RECURSIVE nom_cte (colonne1, colonne2, ...) AS (
 -- Partie initiale de la CTE (ancre)
 SELECT colonne1, colonne2, ...
 FROM table
 WHERE condition
    
 UNION ALL
    
 -- Partie récursive de la CTE
 SELECT colonne1, colonne2, ...
 FROM nom_cte
 WHERE condition_récurrente
)

-- Requête principale
SELECT * FROM nom_cte;

Pour l’exemple, nous allons chercher dans la table Employee définie plus haut les managers directs et indirects de chaque employé.

WITH RECURSIVE employee_hierarchy AS (
 SELECT id, 
 name, 
 managerId,
 'Owner' AS path
 FROM Employee
 WHERE managerId IS NULL

 UNION ALL
    
 SELECT e.id, 
 e.name, 
 e.managerId,
 employee_hierarchy.path || '->' || e.name AS path
 FROM Employee AS e
 JOIN employee_hierarchy ON e.managerId = employee_hierarchy.id
)
 
SELECT *
FROM employee_hierarchy;

On obtient :

Les CTEs sont des indispensables pour travailler sur SQL. Elles permettent de venir à bout de requêtes complexes tout en les rendant lisibles aux yeux des autres. C’est une bonne pratique que je te recommande d’adopter dans tes requêtes.

Ressources

https://learnsql.com/blog/how-to-query-hierarchical-data/