04 - Sous-requêtes, CTEs et WITH RECURSIVE

Ce que tu vas apprendre

• Sous-requêtes dans WHERE, FROM et SELECT
• CTEs (WITH) pour décomposer les requêtes complexes
• WITH RECURSIVE pour parcourir des hierarchies
• MATERIALIZED vs NOT MATERIALIZED

Prerequisites

Avoir lu les articles 01 a 03.

Les donnees

On reprend les tables customers et orders de l'article 02 et on ajoute une table de categories hierarchiques :

sqlCREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  parent_id INT REFERENCES categories(id)
);

INSERT INTO categories (name, parent_id) VALUES
  ('Informatique', NULL),        -- 1
  ('Peripheriques', 1),          -- 2
  ('Composants', 1),             -- 3
  ('Claviers', 2),               -- 4
  ('Souris', 2),                 -- 5
  ('Claviers mecaniques', 4),    -- 6
  ('Claviers membrane', 4);      -- 7

Ca donne un arbre :

Informatique
  Peripheriques
    Claviers
      Claviers mecaniques
      Claviers membrane
    Souris
  Composants

Sous-requêtes dans WHERE

Sous-requête scalaire

Retourne une seule valeur :

sql-- Commandes dont le montant est superieur a la moyenne
SELECT * FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

Si la sous-requête retourne plus d'une valeur, PostgreSQL plante. Utilise IN dans ce cas.

IN avec sous-requête

sql-- Clients qui ont au moins une commande completee
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'completed');

EXISTS (plus performant que IN)

sql-- Meme resultat, mais avec EXISTS
SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.status = 'completed'
);

EXISTS s'arrêté des qu'il trouve une ligne correspondante. IN doit materialiser toute la liste. Sur de grosses tables, EXISTS est souvent plus rapide. Le SELECT 1 est une convention, tu pourrais mettre SELECT * ou SELECT 42, ca ne change rien a la performance.

NOT EXISTS

sql-- Clients sans aucune commande
SELECT * FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

C'est l'alternative au pattern LEFT JOIN ... WHERE id IS NULL vu dans l'article 02. Les deux sont équivalents en performance sur PostgreSQL (le planificateur optimise).

Sous-requêtes dans FROM (tables derivees)

sql-- Statistiques par client, puis filtrage
SELECT stats.name, stats.nb_commandes, stats.total
FROM (
  SELECT c.name, COUNT(o.id) AS nb_commandes, COALESCE(SUM(o.total), 0) AS total
  FROM customers c
  LEFT JOIN orders o ON o.customer_id = c.id
  GROUP BY c.name
) AS stats
WHERE stats.nb_commandes > 1;

Ca marche, mais c'est assez penible a lire quand les sous-requêtes s'imbriquent. C'est la que les CTEs entrent en jeu.

Sous-requêtes correlees (attention)

Une sous-requête correlee référencé la requête externe. Elle est reexecutee pour chaque ligne :

sql-- Pour chaque commande, le nombre total de commandes du meme client
SELECT o.id, o.total,
  (SELECT COUNT(*) FROM orders o2 WHERE o2.customer_id = o.customer_id) AS nb_commandes_client
FROM orders o;

Ca fonctionne, mais c'est potentiellement tres lent. Pour 1000 commandes, la sous-requête s'exécuté 1000 fois. Souvent, un JOIN ou une window function fait le meme travail en une seule passe.

CTEs avec WITH

Les CTEs (Common Table Expressions) decomposent une requête complexe en blocs nommes et lisibles :

sqlWITH active_users AS (
  SELECT * FROM customers WHERE email IS NOT NULL
),
recent_orders AS (
  SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '90 days'
),
user_stats AS (
  SELECT
    u.id,
    u.name,
    COUNT(o.id) AS nb_commandes,
    COALESCE(SUM(o.total), 0) AS total
  FROM active_users u
  LEFT JOIN recent_orders o ON o.customer_id = u.id
  GROUP BY u.id, u.name
)
SELECT * FROM user_stats ORDER BY total DESC;

C'est la meme logique qu'une grosse requête imbriquee, mais en lisible. Chaque CTE est un bloc independant que tu peux tester séparément. Tu copies SELECT * FROM customers WHERE email IS NOT NULL dans ton DBeaver, tu verifies que ca retourne les bonnes lignes, puis tu passes au bloc suivant.

J'utilise les CTEs tout le temps. Des qu'une requête dépassé 10 lignes ou a plus d'un JOIN, je décomposé en CTEs. C'est plus facile a relire, a debugger, et a modifier.

WITH RECURSIVE : parcourir un arbre

C'est ici que ca devient puissant. Pour parcourir notre arbre de categories :

sqlWITH RECURSIVE tree AS (
  -- Cas de base : les racines (pas de parent)
  SELECT id, name, parent_id, 0 AS depth, name AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Cas recursif : les enfants
  SELECT c.id, c.name, c.parent_id, t.depth + 1, t.path || ' > ' || c.name
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT depth, path FROM tree ORDER BY path;

 depth |                    path
-------+--------------------------------------------
     0 | Informatique
     1 | Informatique > Composants
     1 | Informatique > Peripheriques
     2 | Informatique > Peripheriques > Claviers
     3 | Informatique > Peripheriques > Claviers > Claviers mecaniques
     3 | Informatique > Peripheriques > Claviers > Claviers membrane
     2 | Informatique > Peripheriques > Souris

Le fonctionnement : PostgreSQL exécuté d'abord le cas de base (les racines). Puis il boucle : pour chaque ligne trouvee, il cherche les enfants via le JOIN. Il s'arrêté quand une itération ne produit plus de nouvelles lignes.

Un autre cas d'usage classique, les fils de commentaires :

sqlCREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  content TEXT NOT NULL,
  parent_id INT REFERENCES comments(id),
  created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO comments (content, parent_id) VALUES
  ('Super article !', NULL),
  ('Merci !', 1),
  ('Tu pourrais approfondir le point 3', NULL),
  ('Oui, je suis d''accord avec le point 3', 3),
  ('Pas du tout d''accord', 3),
  ('+1', 2);

WITH RECURSIVE thread AS (
  SELECT id, content, parent_id, 0 AS depth
  FROM comments WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.content, c.parent_id, t.depth + 1
  FROM comments c JOIN thread t ON c.parent_id = t.id
)
SELECT repeat('  ', depth) || content AS discussion FROM thread;

           discussion
----------------------------------
 Super article !
   Merci !
     +1
 Tu pourrais approfondir le point 3
   Oui, je suis d'accord avec le point 3
   Pas du tout d'accord

J'ai utilise ce pattern sur un projet de forum interne. Avant, le front faisait des appels recursifs a l'API pour charger chaque niveau de réponse. Avec un seul WITH RECURSIVE, tout arrive en une requête.

MATERIALIZED et NOT MATERIALIZED

Depuis PostgreSQL 12, tu peux donner un indice au planificateur :

sqlWITH stats AS MATERIALIZED (
  SELECT customer_id, COUNT(*) AS cnt
  FROM orders
  GROUP BY customer_id
)
SELECT * FROM stats WHERE cnt > 1;

MATERIALIZED force PostgreSQL a exécuter la CTE et stocker le résultat en mémoire avant de l'utiliser. NOT MATERIALIZED permet au planificateur d'inliner la CTE dans la requête principale (ce qui peut etre plus rapide si tu n'utilises qu'une partie du résultat).

Par défaut, PostgreSQL décidé tout seul (depuis la version 12, il inline les CTEs utilisees une seule fois). En pratique, tu n'as presque jamais besoin de spécifier le hint. Mais si tu vois dans un EXPLAIN ANALYZE que ta CTE est exécutée entièrement alors que tu n'en utilises que 10 lignes, NOT MATERIALIZED peut aider.

Résumé

• EXISTS est souvent plus performant que IN pour les sous-requêtes
• Les sous-requêtes correlees sont reexecutees pour chaque ligne (potentiellement lent)
• Les CTEs (WITH) rendent les requêtes complexes lisibles et testables bloc par bloc
• WITH RECURSIVE parcourt les arbres et hierarchies en une seule requête
• MATERIALIZED/NOT MATERIALIZED contrôle si la CTE est stockee ou inlinee

Article précédent : 03 - GROUP BY et aggregation Article suivant : 05 - Les index

Sources

• PostgreSQL Documentation - WITH Queries (CTEs)
• PostgreSQL Documentation - Recursive Queries
• Citus Data Blog - PostgreSQL CTEs
• pganalyze - Understanding CTEs in PostgreSQL