La clause WITH en Oracle SQL : maîtriser les CTE et sous-requêtes nommées

La clause WITH en Oracle SQL, également appelée CTE (Common Table Expression) ou factorisation de sous-requête, est un outil puissant qui permet de définir des jeux de résultats temporaires nommés, réutilisables au sein d’une même requête. Introduite dès Oracle 9i, la clause WITH améliore considérablement la lisibilité des requêtes complexes et optimise les performances en évitant les répétitions de code SQL.

Définition et utilisation de la clause WITH en Oracle

La clause WITH (aussi appelée subquery factoring dans la terminologie Oracle) permet de déclarer une ou plusieurs sous-requêtes nommées avant le corps principal d’un SELECT. Ces sous-requêtes sont définies une seule fois et peuvent être référencées plusieurs fois dans la requête principale, comme si elles étaient des tables temporaires.

Principaux cas d’usage en entreprise

• Simplification des requêtes imbriquées : remplacer des sous-requêtes profondes et illisibles par des blocs nommés et structurés.
• Calculs intermédiaires réutilisables : calculer un total ou une moyenne une fois, puis l’utiliser plusieurs fois dans la même requête.
• Requêtes récursives : Oracle supporte les CTE récursives via WITH ... CONNECT BY ou avec la syntaxe récursive standard pour parcourir des hiérarchies (organigrammes, nomenclatures).
• Reporting et tableaux de bord : structurer des requêtes analytiques complexes sur de grands volumes de données (ventes, RH, finance).

Oracle peut matérialiser les CTE (les stocker temporairement en mémoire ou sur disque) ou les traiter comme de simples substitutions syntaxiques selon le contexte et l’optimiseur. Le mot-clé MATERIALIZE ou INLINE permet de forcer ce comportement.

Syntaxe complète de la clause WITH Oracle SQL

WITH nom_cte1 AS (
    -- Première sous-requête nommée
    SELECT colonne1, colonne2
    FROM table1
    WHERE condition
),
nom_cte2 AS (
    -- Deuxième sous-requête nommée (peut référencer nom_cte1)
    SELECT colonne1, colonne3
    FROM nom_cte1
    JOIN table2 ON nom_cte1.colonne1 = table2.colonne1
)
-- Requête principale utilisant les CTE
SELECT *
FROM nom_cte2
WHERE colonne3 > 100;

Explication des paramètres essentiels

Remarque Oracle : les hints /*+ MATERIALIZE */ et /*+ INLINE */ peuvent être placés à l’intérieur de la CTE pour contrôler si Oracle matérialise le résultat ou l’intègre en ligne à la requête principale.

Exemples pratiques de la clause WITH en Oracle SQL

Exemple 1 – Analyse des ventes par région avec calcul de moyenne

Contexte métier : une entreprise de distribution souhaite identifier les régions dont le chiffre d’affaires mensuel dépasse la moyenne nationale.

-- Étape 1 : calcul du CA mensuel par région
WITH ventes_region AS (
    SELECT
        region,
        TRUNC(date_vente, 'MM')    AS mois,
        SUM(montant_ht)            AS ca_mensuel
    FROM commandes
    WHERE EXTRACT(YEAR FROM date_vente) = 2024
    GROUP BY region, TRUNC(date_vente, 'MM')
),
-- Étape 2 : calcul de la moyenne nationale sur les mêmes données
moyenne_nationale AS (
    SELECT AVG(ca_mensuel) AS ca_moyen
    FROM ventes_region
)
-- Requête principale : régions au-dessus de la moyenne
SELECT
    vr.region,
    vr.mois,
    vr.ca_mensuel,
    ROUND(mn.ca_moyen, 2) AS ca_moyen_national
FROM ventes_region  vr
CROSS JOIN moyenne_nationale mn
WHERE vr.ca_mensuel > mn.ca_moyen
ORDER BY vr.ca_mensuel DESC;

Dans cet exemple, la CTE ventes_region est calculée une seule fois et réutilisée à la fois dans moyenne_nationale et dans la requête finale. Sans la clause WITH, il aurait fallu dupliquer la sous-requête ou utiliser une vue temporaire.

Exemple 2 – Hiérarchie d’employés avec CTE récursive

Contexte métier : la DRH souhaite afficher l’ensemble de la chaîne hiérarchique d’un manager, de son niveau jusqu’aux employés de base.

-- CTE récursive pour parcourir l'organigramme
WITH RECURSIVE hierarchie (emp_id, nom, manager_id, niveau) AS (
    -- Cas de base : le manager racine (sans supérieur)
    SELECT
        emp_id,
        nom,
        manager_id,
        1 AS niveau
    FROM employes
    WHERE manager_id IS NULL

    UNION ALL

    -- Cas récursif : chaque employé rattaché à un manager déjà trouvé
    SELECT
        e.emp_id,
        e.nom,
        e.manager_id,
        h.niveau + 1
    FROM employes e
    JOIN hierarchie h ON e.manager_id = h.emp_id
)
-- Affichage de la hiérarchie avec indentation visuelle
SELECT
    LPAD(' ', (niveau - 1) * 4) || nom AS organigramme,
    niveau
FROM hierarchie
ORDER BY niveau, nom;

Note Oracle : Oracle supporte officiellement les CTE récursives à partir de la version 11g Release 2 avec la syntaxe UNION ALL dans le bloc WITH. La clause CONNECT BY reste également disponible comme alternative Oracle native pour les requêtes hiérarchiques.

Erreurs courantes avec la clause WITH en Oracle SQL

Erreur : référencer une CTE en dehors de la requête principale

Description : une CTE définie dans un bloc WITH n’existe que le temps d’exécution de la requête. Certains développeurs tentent de la réutiliser dans une requête suivante ou de l’imbriquer dans une instruction DML sans la redéclarer.

-- ❌ INCORRECT : tentative d'utiliser la CTE dans deux requêtes séparées
WITH clients_vip AS (
    SELECT client_id, nom FROM clients WHERE statut = 'VIP'
)
SELECT * FROM clients_vip; -- OK

-- Cette ligne génère une erreur : clients_vip n'existe plus
SELECT COUNT(*) FROM clients_vip; -- ORA-00942: table or view does not exist

-- ✅ CORRECT : tout dans une seule requête, ou redéclarer la CTE
WITH clients_vip AS (
    SELECT client_id, nom FROM clients WHERE statut = 'VIP'
)
SELECT * FROM clients_vip
UNION ALL
SELECT TO_CHAR(COUNT(*)), NULL FROM clients_vip;

Solution : réunissez toutes les utilisations d’une CTE au sein d’une seule et même instruction SQL. Si vous avez besoin de persister les données entre plusieurs requêtes, utilisez une table temporaire Oracle (CREATE GLOBAL TEMPORARY TABLE) ou une vue.

Résumé de la clause WITH en Oracle SQL

2 bonnes pratiques Oracle

1. Nommez vos CTE de manière explicite : utilisez des noms métier clairs (ventes_mensuelles, clients_actifs) plutôt que des alias génériques (cte1, tmp). Cela facilite la relecture et la maintenance du code SQL.
2. Utilisez le hint MATERIALIZE pour les CTE coûteuses et réutilisées : si une CTE est appelée plusieurs fois dans la requête et que son calcul est lourd, forcer la matérialisation (/*+ MATERIALIZE */) peut éviter qu’Oracle la recalcule à chaque référence, améliorant ainsi les performances globales.

Aller plus loin avec Oracle SQL

Pour approfondir votre maîtrise des requêtes avancées en Oracle SQL, nous vous recommandons les sujets suivants :

• Les fonctions analytiques Oracle SQL : apprenez à combiner OVER(), PARTITION BY et ORDER BY avec vos CTE pour des analyses encore plus puissantes.
• CONNECT BY et START WITH en Oracle : découvrez l’alternative Oracle native pour les requêtes hiérarchiques et comparez-la aux CTE récursives.
• Créer et utiliser des vues en Oracle SQL : comprenez quand préférer une vue persistante à une clause WITH pour partager des sous-requêtes entre plusieurs requêtes ou applications.

Sur le même thème

• ROLLBACK Oracle : annuler une transaction SQL facilement
• DELETE en SQL Oracle : Syntaxe et Exemples Pratiques
• OFFSET en SQL Oracle : Pagination et Exemples Pratiques
• ROWNUM Oracle : Guide complet avec exemples SQL
• COMMIT Oracle SQL : valider vos transactions facilement
• FETCH FIRST Oracle : limiter les résultats SQL facilement

Veuillez laisser ce champ vide

Un nouveau tutoriel SQL par semaine

Nous ne spammons pas ! Consultez notre politique de confidentialité pour plus d’informations.

Vérifiez votre boite de réception ou votre répertoire d’indésirables pour confirmer votre abonnement.