SQLite : les CTE et la clause WITH expliquées

Une CTE, c'est une sous-requête nommée

Une CTE (Common Table Expression) en SQLite, c'est tout simplement une sous-requête que l'on extrait pour lui donner un nom. Plutôt que d'imbriquer un SELECT dans un autre SELECT, on la définit en tête de requête avec WITH, on lui attribue un nom, puis on l'utilise dans la requête principale comme s'il s'agissait d'une table.

La structure reste toujours la même :

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

Lisez-le de haut en bas : on construit d'abord un résultat nommé customer_totals, puis on l'interroge. La CTE se comporte comme une vue temporaire qui n'existe que le temps de cette unique requête.

La même requête sans CTE

Voici la même logique écrite sous forme de sous-requête, histoire de voir ce que la CTE vient remplacer :

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

SELECT customer, total
FROM (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
WHERE total > 100;

Même résultat. Mais regardez bien l'ordre de lecture : votre œil doit plonger dans les parenthèses, comprendre ce qui est calculé, puis remonter. La version avec CTE se lit dans l'ordre où le travail se fait — on définit le résultat intermédiaire, puis on s'en sert. Sur une petite requête, ça revient au même. Sur une requête à trois ou quatre étapes, c'est la différence entre un code qu'on parcourt d'un coup d'œil et un code qu'il faut déchiffrer.

Plusieurs CTE dans une même requête

Vous pouvez enchaîner plusieurs CTE en les séparant par des virgules. Chacune peut référencer celles définies avant elle, ce qui permet de construire un pipeline d'étapes nommées :

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH
    customer_totals AS (
        SELECT customer, SUM(amount) AS total
        FROM orders
        GROUP BY customer
    ),
    big_spenders AS (
        SELECT customer, total
        FROM customer_totals
        WHERE total > 100
    )
SELECT customer, total
FROM big_spenders
ORDER BY total DESC;

Un seul WITH, puis les définitions de CTE séparées par des virgules. Le deuxième CTE (big_spenders) lit le premier (customer_totals) exactement comme s'il s'agissait d'une table. Le SELECT principal vient après la dernière définition de CTE.

Petit piège classique : remettre un WITH devant le deuxième CTE. Surtout pas — c'est une erreur de syntaxe. Un seul WITH suffit pour tous.

Réutiliser un CTE plusieurs fois

C'est précisément là que les CTE prennent l'avantage sur les sous-requêtes. Si le même résultat intermédiaire vous sert à deux endroits, un CTE vous permet de le calculer une seule fois et d'y faire référence autant de fois que nécessaire :

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT
    customer,
    total,
    total - (SELECT AVG(total) FROM customer_totals) AS diff_from_avg
FROM customer_totals
ORDER BY diff_from_avg DESC;

La CTE est utilisée deux fois : une première fois pour calculer la moyenne, puis une seconde comme source principale. Sans la CTE, il faudrait dupliquer la requête GROUP BY, et la moindre modification devrait être répercutée à deux endroits.

Utiliser une CTE avec INSERT, UPDATE et DELETE

Les CTE ne se limitent pas au SELECT. Tu peux placer une clause WITH devant un INSERT, un UPDATE ou un DELETE pour exploiter une sous-requête nommée dans une opération d'écriture :

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
CREATE TABLE flagged_customers (customer TEXT, total REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
INSERT INTO flagged_customers (customer, total)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

SELECT * FROM flagged_customers;

La CTE indique quelles lignes marquer. Le INSERT ... SELECT s'en sert comme source. La même astuce marche aussi avec DELETE FROM ... WHERE id IN (SELECT id FROM cte) pour des suppressions par étapes lorsque la logique de ciblage devient touffue.

Quand sortir une CTE

Quelques repères :

• La requête comporte plusieurs étapes logiques. Agréger, puis filtrer sur l'agrégat, puis joindre le résultat — c'est un pipeline, et une CTE par étape rend le tout bien plus lisible.
• Vous alliez répéter la même sous-requête. Définissez-la une fois, référencez-la deux fois.
• La sous-requête mérite un nom. Si vous étiez sur le point d'écrire un commentaire au-dessus de la sous-requête pour expliquer ce qu'elle représente, le nom de la CTE est ce commentaire — et la syntaxe vous oblige à le poser.
• Vous vous apprêtez à écrire une requête récursive. Ce n'est possible qu'avec WITH RECURSIVE, sujet de la page suivante.

Quand ne pas s'embêter :

• Une seule sous-requête simple utilisée à un seul endroit. WHERE id IN (SELECT id FROM ...) fait très bien le boulot tel quel.
• Des requêtes critiques en performance où vous avez déjà vérifié qu'inliner la logique aide. SQLite traite généralement une CTE comme une barrière d'optimisation moins stricte que certaines autres bases, mais sur les chemins chauds, mieux vaut vérifier avec EXPLAIN QUERY PLAN.

Un exemple concret

Mettons tout cela bout à bout — un petit rapport qui trouve, pour chaque client, sa plus grosse commande et la compare à sa moyenne :

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50),
    ('Carla', 75), ('Boris', 90);

WITH
    per_customer AS (
        SELECT customer, MAX(amount) AS biggest, AVG(amount) AS average
        FROM orders
        GROUP BY customer
    ),
    with_ratio AS (
        SELECT customer, biggest, average, biggest / average AS ratio
        FROM per_customer
    )
SELECT customer, biggest, ROUND(average, 2) AS average, ROUND(ratio, 2) AS ratio
FROM with_ratio
ORDER BY ratio DESC;

Deux CTE, chacune avec un rôle bien précis. Le SELECT final se charge de mettre en forme le résultat. La requête se lit de haut en bas, étape par étape — et c'est exactement ce qui fait tout l'intérêt des CTE.

La suite : les CTE récursives

Jusqu'ici, on est restés sur des CTE classiques — une sous-requête nommée, évaluée une seule fois. Mais SQLite propose aussi WITH RECURSIVE, où une CTE s'appelle elle-même pour parcourir des hiérarchies, générer des séries ou explorer des graphes. C'est ce qu'on verra sur la page suivante.