CTE vs sous-requêtes : quand choisir quoi ?

On sait déjà que les CTE rendent le code plus lisible. Mais est-ce qu’il faut toujours les utiliser ? Parfois, une simple sous-requête fait le boulot mieux et plus vite. On va voir dans quels cas chaque outil est le plus adapté, et apprendre à faire un choix réfléchi.

Sous-requêtes : rapide et simple

Tu te souviens sûrement qu’une sous-requête, c’est du SQL dans du SQL. Elle s’insère direct dans la requête principale et s’exécute “sur place”. Parfait pour des opérations simples et ponctuelles :

-- Trouver les produits plus chers que le prix moyen
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Ici, la sous-requête calcule le prix moyen une seule fois, et basta. Pas besoin de constructions en plus.

Performance : qui va plus vite ?

Les sous-requêtes sont souvent plus rapides pour les opérations simples. PostgreSQL peut les optimiser “à la volée”, surtout quand la sous-requête s’exécute une seule fois :

-- Rapide : la sous-requête s’exécute une seule fois
SELECT customer_id, order_total
FROM orders
WHERE order_date = (SELECT MAX(order_date) FROM orders);

Les CTE sont matérialisés par défaut — PostgreSQL calcule d’abord le résultat du CTE, le stocke comme une table temporaire, puis l’utilise. Ça peut ralentir les requêtes simples :

-- Plus lent : le CTE est matérialisé dans une table temporaire
WITH latest_date AS (
    SELECT MAX(order_date) AS max_date FROM orders
)
SELECT customer_id, order_total
FROM orders, latest_date
WHERE order_date = max_date;

Mais ! Depuis PostgreSQL 12, tu peux gérer la matérialisation :

-- Forcer à NE PAS matérialiser
WITH latest_date AS NOT MATERIALIZED (
    SELECT MAX(order_date) AS max_date FROM orders
)
SELECT customer_id, order_total
FROM orders, latest_date
WHERE order_date = max_date;

Réutilisation multiple : ici les CTE sont rois

Quand tu as besoin plusieurs fois du même résultat intermédiaire, les CTE deviennent indispensables :

-- Avec une sous-requête : on répète la même logique deux fois
SELECT
    (SELECT COUNT(*) FROM orders WHERE status = 'terminée') AS commandes_terminees,
    (SELECT COUNT(*) FROM orders WHERE status = 'terminée') * 100.0 / COUNT(*) AS taux_terminaison
FROM orders;

-- Avec un CTE : on calcule une fois, on utilise deux fois
WITH commandes_terminees AS (
    SELECT COUNT(*) AS count FROM orders WHERE status = 'terminée'
)
SELECT
    co.count AS commandes_terminees,
    co.count * 100.0 / (SELECT COUNT(*) FROM orders) AS taux_terminaison
FROM commandes_terminees co;

Analytique complexe : CTE gagne aux points

Pour de l’analytique en plusieurs étapes, les CTE transforment le chaos en ordre. Compare un rapport de ventes :

Avec des sous-requêtes (c’est le bazar) :

SELECT 
    category,
    revenue,
    revenue * 100.0 / (
        SELECT SUM(p.price * oi.quantity)
        FROM order_items oi
        JOIN products p ON oi.product_id = p.product_id
        JOIN orders o ON oi.order_id = o.order_id
        WHERE EXTRACT(year FROM o.order_date) = 2024
    ) AS part_revenue
FROM (
    SELECT 
        p.category,
        SUM(p.price * oi.quantity) AS revenue
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE EXTRACT(year FROM o.order_date) = 2024
    GROUP BY p.category
) revenus_par_categorie;

Avec des CTE (tout est rangé) :

WITH ventes_annuelles AS (
    SELECT 
        p.category,
        p.price * oi.quantity AS montant_vente
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE EXTRACT(year FROM o.order_date) = 2024
),
revenus_par_categorie AS (
    SELECT 
        category,
        SUM(montant_vente) AS revenue
    FROM ventes_annuelles
    GROUP BY category
),
revenu_total AS (
    SELECT SUM(montant_vente) AS total FROM ventes_annuelles
)
SELECT 
    rc.category,
    rc.revenue,
    rc.revenue * 100.0 / rt.total AS part_revenue
FROM revenus_par_categorie rc, revenu_total rt;

Récursivité : monopole des CTE

Pour les structures hiérarchiques, les sous-requêtes sont impuissantes.

Seuls les CTE récursifs gèrent des trucs comme “trouver tous les subordonnés d’un manager” :

WITH RECURSIVE hierarchie_employes AS (
    -- On commence par le CEO
    SELECT employee_id, manager_id, name, 1 AS niveau
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- On ajoute les subordonnés de chaque niveau
    SELECT e.employee_id, e.manager_id, e.name, he.niveau + 1
    FROM employees e
    JOIN hierarchie_employes he ON e.manager_id = he.employee_id
)
SELECT * FROM hierarchie_employes ORDER BY niveau, name;

Debug et maintenance du code

Les CTE sont faciles à debugger étape par étape :

-- On vérifie la première étape
WITH clients_actifs AS (
    SELECT customer_id FROM customers WHERE status = 'actif'
)
SELECT COUNT(*) FROM clients_actifs; -- On s’assure que la logique est bonne

-- On ajoute la deuxième étape
WITH clients_actifs AS (...),
commandes_recentes AS (
    SELECT customer_id, COUNT(*) as nb_commandes
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY customer_id
)
SELECT COUNT(*) FROM commandes_recentes; -- On vérifie aussi cette étape

Les sous-requêtes sont plus galères à debugger — il faut les sortir de leur contexte.

Conseils pratiques

Utilise les sous-requêtes quand :

• La logique est simple et tient sur une ligne
• Tu veux les meilleures perfs pour des opérations simples
• Le résultat intermédiaire ne sert qu’une fois
• Tu bosses sur des petits volumes de données

Utilise les CTE quand :

• La requête est complexe et se découpe en étapes logiques
• Tu dois réutiliser plusieurs fois des résultats intermédiaires
• La lisibilité et la maintenabilité du code sont importantes
• Tu bosses avec des hiérarchies (CTE récursifs)
• Tu débugges une logique complexe étape par étape

La règle d’or

Commence par une sous-requête. Si ça devient illisible ou que la logique se répète — passe aux CTE. Ton futur collègue (ou toi-même dans six mois) te dira merci !