Guide SQL : CTE ou sous-requête

Une sous-requête est une requête imbriquée dans une autre requête. Une CTE (common table expression) est cette même requête interne déplacée en tête de l’instruction avec WITH et dotée d’un nom, pour qu’une longue requête se lise comme une suite d’étapes. Les exemples fonctionnent sous PostgreSQL, MySQL, SQLite et SQL Server.

Comment utiliser les CTE et les sous-requêtes

Une sous-requête se place là où on l’utilise : dans FROM, elle agit comme une table, et dans WHERE ou SELECT, elle fournit une valeur ou une liste. Une CTE est définie d’abord, puis utilisée comme une table par la requête qui suit :

-- Subquery: written where it's used
SELECT columns FROM (SELECT ...) AS name;

-- CTE: named first, then used like a table
WITH name AS (SELECT ...)
SELECT columns FROM name;

SELECT n * 2 AS doubled FROM (SELECT 21 AS n) AS numbers;
-- Output: 42

WITH numbers AS (SELECT 21 AS n)
SELECT n * 2 AS doubled FROM numbers;
-- Output: 42

Sur une vraie table, les deux formes renvoient les mêmes lignes. Voici un même filtre écrit des deux façons :

-- Input: people
-- first_name | last_name | job      | favorite_food
-- -----------+-----------+----------+--------------
-- Matthew    | Ortiz     | Chef     | cheese
-- Matt       | Novak     | Pilot    | pizza
-- Mattie     | Brooks    | Engineer | cheese
-- Priya      | Nair      | Teacher  | cheese

-- Subquery
SELECT *
FROM (SELECT first_name, last_name, job FROM people WHERE favorite_food = 'cheese') AS cheese_lovers
WHERE first_name LIKE 'Matt%';

-- CTE
WITH cheese_lovers AS (
  SELECT first_name, last_name, job FROM people WHERE favorite_food = 'cheese'
)
SELECT * FROM cheese_lovers
WHERE first_name LIKE 'Matt%';

-- Output of both:
-- first_name | last_name | job
-- -----------+-----------+---------
-- Matthew    | Ortiz     | Chef
-- Mattie     | Brooks    | Engineer

Règles à connaître

• Une CTE ne dure que le temps d’une instruction. La requête juste après WITH peut l’utiliser ; l’instruction suivante, non. Pour conserver un résultat sur plusieurs requêtes, utilise une table temporaire ou une vue.
• Donne un nom à une sous-requête dans FROM, comme AS numbers ci-dessus. MySQL et SQL Server la refusent sans nom.
• Une sous-requête utilisée comme valeur unique doit renvoyer une seule ligne :

SELECT (SELECT 1 UNION ALL SELECT 2) AS n;
-- ERROR: more than one row returned by a subquery used as an expression
-- (SQLite doesn't complain: it quietly uses the first row, 1)

• La vitesse est généralement la même. Les bases de données planifient en général une CTE utilisée une seule fois comme la sous-requête équivalente : choisis donc la forme la plus lisible.

Syntaxe alternative

Une CTE qui fait référence à elle-même (voir plus bas) s’écrit WITH RECURSIVE :

-- In SQL Server, leave out RECURSIVE
WITH RECURSIVE counter AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 3
)
SELECT n FROM counter;

-- Output:
-- n
-- -
-- 1
-- 2
-- 3

Sous SQL Server, l’instruction qui précède un WITH doit se terminer par un point-virgule, c’est pourquoi le code T-SQL commence souvent une CTE par ;WITH.

Quand utiliser une CTE plutôt qu’une sous-requête ?

Une sous-requête suffit pour une petite étape. Passe à une CTE quand la requête comporte plusieurs étapes, a besoin du même résultat deux fois ou doit faire référence à elle-même.

Filtrer par rapport à une valeur calculée

Quand la requête interne ne produit qu’une valeur ou une liste de comparaison, comme le montant moyen des commandes, une sous-requête dans WHERE est la façon la plus courte de l’écrire. Une CTE marcherait aussi, mais elle ajoute un nom dont personne n’a besoin.

Découper une longue requête en étapes nommées

Quand une requête comporte des étapes (total par client, puis les meilleurs, puis la liste finale), donne un nom à chacune. Enchaîne plusieurs CTE après un seul WITH, séparées par des virgules : chacune peut utiliser les précédentes, et la requête se lit de haut en bas plutôt que de l’intérieur vers l’extérieur.

Réutiliser le même résultat plusieurs fois

Une instruction peut faire référence à une CTE autant de fois qu’elle veut : un résultat dont tu as besoin deux fois (une liste de totaux et leur moyenne, ou chaque total et le total général) ne s’écrit qu’une fois. Avec des sous-requêtes, il faudrait recopier toute la requête interne.

Parcourir une hiérarchie

Seule une CTE peut faire référence à elle-même. Une CTE récursive part de quelques lignes et refait des jointures sur la table jusqu’à ce qu’aucune nouvelle ligne n’apparaisse : c’est ainsi qu’on parcourt un organigramme ou une arborescence de catégories, ou qu’on génère une série de nombres ou de dates.

Exemples concrets de CTE et de sous-requêtes

Produits plus chers que la moyenne de leur catégorie

La sous-requête fait référence à p.category de la requête externe, elle est donc calculée pour la catégorie de chaque produit (une sous-requête corrélée) :

-- Input: products
-- name          | category  | price
-- --------------+-----------+-------
-- Desk Lamp     | Lighting  | 24.99
-- Floor Lamp    | Lighting  | 89.00
-- Office Chair  | Furniture | 149.00
-- Standing Desk | Furniture | 399.00
-- Bookshelf     | Furniture | 120.00

SELECT name, category, price
FROM products p
WHERE price > (
  SELECT AVG(price) FROM products WHERE category = p.category
)
ORDER BY category, name;

-- Output:
-- name          | category  | price
-- --------------+-----------+-------
-- Standing Desk | Furniture | 399.00
-- Floor Lamp    | Lighting  | 89.00

Le produit le plus vendu de chaque catégorie

Trois étapes, chacune nommée : totaliser les unités par produit, classer les produits dans chaque catégorie, puis garder le premier :

-- Input: sales
-- id | product      | category  | units
-- ---+--------------+-----------+------
-- 1  | Desk Lamp    | Lighting  | 3
-- 2  | Floor Lamp   | Lighting  | 5
-- 3  | Desk Lamp    | Lighting  | 4
-- 4  | Office Chair | Furniture | 2
-- 5  | Bookshelf    | Furniture | 6

WITH product_totals AS (
  SELECT category, product, SUM(units) AS units
  FROM sales
  GROUP BY category, product
),
ranked AS (
  SELECT category, product, units,
         RANK() OVER (PARTITION BY category ORDER BY units DESC) AS place
  FROM product_totals
)
SELECT category, product, units
FROM ranked
WHERE place = 1
ORDER BY category;

-- Output:
-- category  | product   | units
-- ----------+-----------+------
-- Furniture | Bookshelf | 6
-- Lighting  | Desk Lamp | 7

La part des ventes de chaque région

region_totals est utilisée deux fois : une fois pour les lignes, et une fois dans une sous-requête pour le total général :

-- Input: orders
-- id | region | amount
-- ---+--------+--------
-- 1  | North  | 1200.00
-- 2  | South  | 450.00
-- 3  | North  | 300.00
-- 4  | West   | 800.00
-- 5  | South  | 250.00

WITH region_totals AS (
  SELECT region, SUM(amount) AS total
  FROM orders
  GROUP BY region
)
SELECT
  region,
  total,
  ROUND(100.0 * total / (SELECT SUM(total) FROM region_totals), 1) AS pct_of_sales
FROM region_totals
ORDER BY total DESC;

-- Output:
-- region | total   | pct_of_sales
-- -------+---------+-------------
-- North  | 1500.00 | 50.0
-- West   | 800.00  | 26.7
-- South  | 700.00  | 23.3

Avec des sous-requêtes, la requête GROUP BY apparaîtrait deux fois.

La chaîne de managers d’un employé

Pars d’un employé et remonte manager_id jusqu’au bout :

-- Input: employees
-- id | name      | manager_id
-- ---+-----------+-----------
-- 1  | Maya Chen | NULL
-- 2  | Sam Ortiz | 1
-- 3  | Jo Kim    | 2
-- 4  | Lee Ross  | 2
-- 5  | Ana Diaz  | 1

-- In SQL Server, leave out RECURSIVE
WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 0 AS level
  FROM employees
  WHERE name = 'Jo Kim'
  UNION ALL
  SELECT e.id, e.name, e.manager_id, chain.level + 1
  FROM employees e
  JOIN chain ON e.id = chain.manager_id
)
SELECT level, name FROM chain ORDER BY level;

-- Output:
-- level | name
-- ------+----------
-- 0     | Jo Kim
-- 1     | Sam Ortiz
-- 2     | Maya Chen

Pour aller plus loin sur les CTE et les sous-requêtes

Sur ce blog :

• Guide des tables temporaires SQL : conserver un résultat intermédiaire sur plusieurs instructions
• Guide pour trouver les doublons en SQL : des CTE avec ROW_NUMBER() pour repérer les lignes répétées
• Guide pour supprimer les doublons en SQL : supprimer les doublons via une CTE
• Guide SQL GROUP BY : les agrégats sur lesquels reposent la plupart des CTE
• Guide des JOIN SQL en schémas : joindre une CTE à d’autres tables

Documentation officielle :

• PostgreSQL : requêtes WITH et expressions de sous-requête
• MySQL : WITH (common table expressions) et sous-requêtes
• SQLite : la clause WITH
• SQL Server : WITH common_table_expression et sous-requêtes