Sous-requêtes SQL

Cet article décrit ce qu'est une sous-requête et à quoi ressemblent ces instructions utiles. Nous aborderons des exemples de base avec les opérateurs IN, EXISTS, ANY et ALL, nous examinerons les sous-requêtes dans les clauses FROM et WHERE et nous étudierons la différence entre les sous-requêtes corrélées et imbriquées.

Tout d'abord, commençons par un exemple de base de données. Pour présenter certaines de ces déclarations, nous devons disposer d'une table d'exemple et la remplir de quelques données.

Qu'est-ce qu'une sous-requête ?

Une sous-requête est une instruction SELECT associée à une autre instruction SQL, comme dans l'exemple ci-dessous.

SELECT *
FROM product
WHERE id IN (
  SELECT product_id
  FROM provider_offer
  WHERE provider_id = 156
);

Les sous-requêtes sont classées en deux catégories : les sous-requêtes corrélées et les sous-requêtes imbriquées. Elles sont généralement construites de manière à renvoyer :

1. une table

   SELECT MAX(average.average_price)
   FROM (
     SELECT
       product_category,
       AVG(price) AS average_price
     FROM product
     GROUP BY product_category
   ) average;
   

2. ou une valeur

   SELECT id
   FROM purchase
   WHERE value > (
     SELECT AVG(value)
     FROM purchase
   );
   

Vous voulez en savoir plus sur les sous-requêtes SQL ? Consultez notre série Nous apprenons le SQL sur Youtube. N'oubliez pas de vous abonner à notre chaîne.

Sous-requêtes imbriquées

Lessous-requêtes imbriquées sont des sous-requêtes qui ne dépendent pas d'une requête externe. En d'autres termes, les deux requêtes d'une sous-requête imbriquée peuvent être exécutées comme des requêtes distinctes.

Ce type de sous-requête peut être utilisé presque partout, mais il prend généralement l'un de ces formats :

SELECT
FROM
WHERE [NOT] IN (subquery)

SELECT *
FROM client
WHERE city IN (
  SELECT city
  FROM provider
);

La sous-requête d'exemple renvoie tous les clients qui proviennent de la même ville que les fournisseurs de produits.
L'opérateur IN vérifie si la valeur se trouve dans la table et récupère les lignes correspondantes.

SELECT
FROM
WHERE expression comparison_operator [ANY| ALL] (subquery)

Sous-requête avec l'opérateur ALL

L'opérateur ALL compare une valeur à toutes les valeurs du tableau de résultats.

Par exemple, la requête suivante renvoie tous les modèles et producteurs de vélos dont le prix est supérieur au casque le plus cher.

SELECT producer, model
FROM product
WHERE product_category = 'bike'
  AND price > ALL(
    SELECT price
    FROM product
    WHERE product_category = 'headphones'
  );

Sous-requête similaire mais avec l'opérateur ANY :

Sous-requête avec l'opérateur ANY

L'opérateur ANY compare une valeur à chaque valeur d'une table et évalue si le résultat d'une requête interne contient ou non au moins une ligne.

La requête suivante renvoie tous les modèles et producteurs de vélos dont le prix est supérieur à au moins un des écouteurs.

SELECT producer, model
FROM product
WHERE product_category = 'bike'
  AND price > ANY(
    SELECT price
    FROM product
    WHERE product_category = 'headphones'
  );

Vous pouvez également imbriquer une sous-requête dans une autre aubquery. Par exemple :

Sous-requête imbriquée dans une autre sous-requête à l'aide de l'opérateur IN

Cette requête renvoie les fabricants et les modèles de vélos qui existent dans les offres du fournisseur à partir des États-Unis.

SELECT producer, model
FROM product
WHERE product_category = 'bike'
  AND id IN (
    SELECT distinct product_id
    FROM provider_offer
    WHERE provider_id IN (
      SELECT id
      FROM provider
      WHERE country = 'USA'
    )
  );

On peut faire la même chose en utilisant des jointures.

SELECT product.producer, product.model
FROM product, provider_offer, provider
WHERE provider_offer.product_id = product.id
  AND provider_offer.provider_id = provider.id
  AND product_category = 'bike'
  AND provider.country = 'USA';

Sous-requêtes corrélées

Les sous-requêtes sont corrélées lorsque les requêtes interne et externe sont interdépendantes, c'est-à-dire lorsque la requête externe est une requête qui contient une sous-requête et que la sous-requête elle-même est une requête interne. Les utilisateurs qui connaissent les concepts de programmation peuvent la comparer à une structure de boucle imbriquée.

Commençons par un exemple simple.

La requête interne calcule la valeur moyenne et la renvoie. Dans la clause WHERE de la requête externe, nous filtrons uniquement les achats dont la valeur est supérieure à la valeur renvoyée par la requête interne.

Sous-requête corrélée dans la clause WHERE

SELECT id
FROM purchase p1
WHERE date > '2013-07-15'
  AND value > (
    SELECT AVG(value)
    FROM purchase p2
    WHERE p1.date = p2.date
  );

La requête renvoie les achats postérieurs au 15/07/2014 dont le prix total est supérieur à la valeur moyenne du même jour.

L'exemple équivalent, mais avec des tables jointes.

SELECT  p1.id
FROM purchase p1, purchase p2
WHERE p1.date = p2.date
  AND p1.date > '2013-07-15'
GROUP BY p1.id
HAVING p1.value > AVG(p2.value);

Cet exemple peut également être écrit comme une instruction SELECT avec une sous-requête corrélée dans une clause FROM.

La sous-requête renvoie la table qui contient la valeur moyenne de chaque achat pour chaque jour. Nous rejoignons ce résultat avec la table Purchase sur la colonne 'date' pour vérifier la condition date > '15/07/2014′.

SELECT id
FROM
  purchase,
  (
    SELECT date, AVG(value) AS average_value
    FROM purchase
    WHERE date > '2013-07-15'
    GROUP BY date
  ) average
WHERE purchase.date  = average.date
  AND purchase.date  > '2013-07-15'
  AND purchase.value > average.average_value;

Habituellement, ce type de sous-requête est à éviter car les index ne peuvent pas être utilisés sur une table temporaire en mémoire.

Sous-requête avec EXISTS

SELECT
FROM
WHERE [NOT] EXISTS (subquery)

L'opérateur EXISTS vérifie si la ligne provenant de la sous-requête correspond à une ligne de la requête externe. Si aucune donnée ne correspond, l'opérateur EXISTS renvoie FALSE.

Cette requête renvoie tous les clients qui ont commandé après le 10/07/2013.

SELECT id, company_name
FROM client
WHERE EXISTS(
  SELECT *
  FROM purchase
  WHERE client.id = purchase.client_id
  WHERE date > '2013-07-10'
);

Lorsqu'une sous-requête est utilisée, l'optimiseur de requêtes effectue des étapes supplémentaires avant d'utiliser les résultats de la sous-requête. Si une requête qui contient une sous-requête peut être écrite à l'aide d'une jointure, il est préférable de procéder ainsi. Les jointures permettent généralement à l'optimiseur de requêtes de récupérer les données de manière plus efficace.

Limites des sous-requêtes

L'utilisation de sous-requêtes comporte certaines limites :

• Dans Oracle, vous pouvez imbriquer jusqu'à 255 niveaux de sous-requêtes dans une clause WHERE.
• Dans SQL Server, vous pouvez imbriquer jusqu'à 32 niveaux.