Les sous-requêtes SQL Oracle : Guide complet et exemples

Les sous-requêtes en SQL Oracle : Guide complet avec exemples

Les sous-requêtes en SQL Oracle sont des requêtes imbriquées dans une requête principale, permettant d’effectuer des opérations complexes en une seule instruction. Incontournables dans le développement Oracle, elles offrent une flexibilité remarquable pour filtrer, comparer ou agréger des données. Maîtriser les sous-requêtes SQL Oracle est une compétence essentielle pour tout développeur ou analyste travaillant avec des bases de données relationnelles.

Définition et utilisation des sous-requêtes SQL Oracle

Une sous-requête (ou subquery) est une instruction SELECT imbriquée à l’intérieur d’une autre requête SQL. Elle peut apparaître dans les clauses WHERE, FROM, HAVING ou même SELECT. Oracle évalue d’abord la sous-requête, puis utilise son résultat pour exécuter la requête principale.

On distingue plusieurs types de sous-requêtes :

• Sous-requête mono-ligne : retourne une seule valeur (utilisation avec =, >, <, etc.)
• Sous-requête multi-lignes : retourne plusieurs valeurs (utilisation avec IN, ANY, ALL)
• Sous-requête corrélée : référence la table de la requête externe et s’exécute ligne par ligne
• Sous-requête dans la clause FROM : appelée aussi table dérivée ou inline view

Cas d’usage en entreprise

En contexte professionnel, les sous-requêtes sont utilisées pour :

• Identifier les employés dont le salaire dépasse la moyenne de leur département
• Lister les clients n’ayant passé aucune commande sur une période donnée
• Calculer des indicateurs financiers à partir de données agrégées
• Alimenter des rapports dynamiques sans vues permanentes

Syntaxe des sous-requêtes en SQL Oracle

La structure générale d’une sous-requête Oracle suit le schéma suivant :

SELECT colonne1, colonne2
FROM   table_principale
WHERE  colonne_condition opérateur
       (SELECT colonne
        FROM   table_secondaire
        WHERE  condition);

Explication des paramètres essentiels

• opérateur : selon le type de résultat attendu :
  • Mono-ligne : =, !=, >, <, >=, <=
  • Multi-lignes : IN, NOT IN, ANY, ALL, EXISTS
• La sous-requête doit toujours être encadrée par des parenthèses
• La clause ORDER BY n’est pas autorisée dans une sous-requête (sauf dans une inline view avec ROWNUM ou FETCH)
• Une sous-requête corrélée référence un alias de la requête externe via un préfixe de table

Syntaxe d’une sous-requête corrélée :

SELECT colonne1
FROM   table_principale ext
WHERE  EXISTS
       (SELECT 1
        FROM   table_secondaire int
        WHERE  int.cle_etrangere = ext.cle_primaire);

Exemples pratiques de sous-requêtes SQL Oracle

Exemple 1 – Sous-requête mono-ligne dans la clause WHERE

Contexte métier : Dans une entreprise, le service RH souhaite afficher tous les employés dont le salaire est supérieur au salaire moyen de l’ensemble de la société.

-- Sélection des employés gagnant plus que la moyenne générale
SELECT e.employe_id,
       e.nom,
       e.prenom,
       e.salaire,
       e.departement_id
FROM   employes e
WHERE  e.salaire > (SELECT AVG(salaire)
                    FROM   employes);

Explication :

• La sous-requête SELECT AVG(salaire) FROM employes calcule le salaire moyen global et retourne une valeur unique.
• L’opérateur > est utilisé car on attend un résultat mono-ligne.
• Oracle évalue d’abord la sous-requête, obtient par exemple 3 200, puis filtre les employés dont le salaire dépasse ce seuil.

Exemple 2 – Sous-requête corrélée avec EXISTS

Contexte métier : Le service commercial veut identifier les clients qui ont passé au moins une commande au cours du premier trimestre 2024, afin de leur envoyer une offre de fidélité.

-- Liste des clients ayant passé au moins une commande au T1 2024
SELECT c.client_id,
       c.nom_client,
       c.email
FROM   clients c
WHERE  EXISTS
       (SELECT 1
        FROM   commandes cmd
        WHERE  cmd.client_id    = c.client_id        -- corrélation avec la requête externe
        AND    cmd.date_commande >= DATE '2024-01-01'
        AND    cmd.date_commande <  DATE '2024-04-01');

Explication :

• La sous-requête est corrélée : elle référence c.client_id de la requête externe à chaque itération.
• EXISTS retourne TRUE dès qu’au moins une ligne est trouvée, ce qui est plus performant que IN sur de grands volumes.
• Le SELECT 1 est une convention Oracle : on ne sélectionne pas de données réelles, juste une valeur d’existence.
• La syntaxe DATE 'YYYY-MM-DD' est le format littéral de date recommandé sous Oracle.

Erreurs courantes avec les sous-requêtes SQL Oracle

Erreur : ORA-01427 – Single-row subquery returns more than one row

Cette erreur survient lorsqu’une sous-requête censée retourner une seule valeur en retourne plusieurs, en association avec un opérateur mono-ligne (=, >, etc.).

Code erroné :

-- ERREUR : plusieurs départements peuvent avoir le même nom
SELECT nom, salaire
FROM   employes
WHERE  departement_id = (SELECT departement_id
                         FROM   departements
                         WHERE  localisation = 'PARIS');  -- retourne plusieurs lignes !

Solution : Remplacer l’opérateur = par IN lorsque la sous-requête peut retourner plusieurs lignes :

-- CORRECT : utilisation de IN pour une sous-requête multi-lignes
SELECT nom, salaire
FROM   employes
WHERE  departement_id IN (SELECT departement_id
                          FROM   departements
                          WHERE  localisation = 'PARIS');

Conseil : Avant d’écrire votre requête principale, testez toujours la sous-requête de manière isolée pour vérifier le nombre de lignes retournées.

Résumé

2 bonnes pratiques Oracle

1. Privilégier EXISTS à IN sur de grands volumes : Sous Oracle, EXISTS s’arrête dès la première correspondance trouvée, ce qui le rend plus performant que IN lorsque la sous-requête porte sur une table volumineuse.
2. Utiliser les CTE (WITH) pour la lisibilité : Pour les sous-requêtes complexes ou réutilisées, préférez les Common Table Expressions (clause WITH) afin d’améliorer la maintenabilité du code et faciliter le débogage.

Aller plus loin

Pour approfondir votre maîtrise des requêtes avancées sous Oracle, consultez ces sujets complémentaires :

• Les expressions de table communes (CTE) avec la clause WITH en Oracle : une alternative puissante aux sous-requêtes imbriquées pour structurer des requêtes complexes.
• Les fonctions analytiques et de fenêtrage en SQL Oracle : utilisez OVER(PARTITION BY...) pour remplacer certaines sous-requêtes corrélées par des calculs plus performants.
• Les jointures SQL Oracle (INNER JOIN, LEFT JOIN, FULL OUTER JOIN) : comprendre quand préférer une jointure à une sous-requête pour optimiser vos performances Oracle.