Sous-requêtes (SQL Server)

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Une sous-requête est une requête qui est imbriquée dans une instruction SELECT, INSERT, UPDATE ou DELETE, ou dans une autre sous-requête.

Les exemples de code Transact-SQL de cet article sont fondés sur l’échantillon de base de données AdventureWorks2022 fourni, que vous pouvez télécharger à partir de la page d’accueil Échantillons et projets communautaires Microsoft SQL Server.

Une sous-requête peut être utilisée partout où une expression est autorisée. Dans cet exemple, une sous-requête est utilisée comme expression de colonne nommée MaxUnitPrice dans une instruction SELECT.

USE AdventureWorks2022;
GO
SELECT Ord.SalesOrderID, Ord.OrderDate,
    (SELECT MAX(OrdDet.UnitPrice)
     FROM Sales.SalesOrderDetail AS OrdDet
     WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPrice
FROM Sales.SalesOrderHeader AS Ord;
GO

Principes fondamentaux des sous-requêtes

Une sous-requête est également appelée « requête interne » ou « sélection interne » et l'instruction qui la contient est aussi appelée « requête externe » ou « sélection externe ».

De nombreuses instructions Transact-SQL incluant des sous-requêtes peuvent aussi être formulées comme des jointures. D'autres questions peuvent uniquement être posées par le biais de sous-requêtes. Dans Transact-SQL, il n’y a généralement pas de différence de performance entre une instruction qui inclut une sous-requête et une version sémantiquement équivalente qui ne l’inclut pas. Pour plus d’informations sur l’architecture du traitement des requêtes par SQL Server, consultez Traitement des instructions SQL. Toutefois, lorsque vous devez vérifier l'existence, une jointure offre de meilleures performances. En effet, la requête imbriquée doit être traitée pour chaque résultat de la requête externe de façon à éliminer les doublons. Dans de tels cas, une jointure donnera de meilleurs résultats.

L’exemple suivant présente une sous-requête SELECT et une jointure SELECT qui renvoient le même jeu de résultats et le même plan d’exécution :

USE AdventureWorks2022;
GO

/* SELECT statement built using a subquery. */
SELECT [Name]
FROM Production.Product
WHERE ListPrice =
    (SELECT ListPrice
     FROM Production.Product
     WHERE [Name] = 'Chainring Bolts' );
GO

/* SELECT statement built using a join that returns
   the same result set. */
SELECT Prd1.[Name]
FROM Production.Product AS Prd1
     JOIN Production.Product AS Prd2
       ON (Prd1.ListPrice = Prd2.ListPrice)
WHERE Prd2.[Name] = 'Chainring Bolts';
GO

Une sous-requête imbriquée dans l'instruction SELECT externe comporte les éléments suivants :

  • Une requête SELECT standard constituée des éléments standard d’une liste de sélection.
  • Une clause FROM standard comportant les noms d’une ou de plusieurs tables ou vues.
  • Une clause WHERE facultative.
  • Une clause GROUP BY facultative.
  • Une clause HAVING facultative.

La requête SELECT d'une sous-requête se place toujours entre parenthèses. Elle ne peut pas inclure de clause COMPUTE ou FOR BROWSE. De même, elle ne peut inclure une clause ORDER BY que si une clause TOP est également spécifiée.

Une sous-requête peut être imbriquée dans la clause WHERE ou HAVING d’une instruction SELECT, INSERT, UPDATE ou DELETE externe, ou dans une autre sous-requête. Vous pouvez aller jusqu'à 32 niveaux d'imbrication mais cette limite dépend de la mémoire disponible et de la complexité des autres expressions constituant la requête. Les requêtes individuelles, pour leur part, n'acceptent pas toujours 32 niveaux d'imbrication. Une sous-requête peut apparaître à tout endroit où une expression est autorisée, à condition de ne retourner qu'une seule valeur.

Si une table n’apparaît que dans une sous-requête et non dans la requête externe, les colonnes de cette table ne peuvent pas être incluses dans la production (la liste de sélection de la requête externe).

Les instructions contenant une sous-requête se présentent généralement sous une des formes suivantes :

  • WHERE expression [NOT] IN (subquery)
  • WHERE expression comparison_operator [ANY | ALL] (subquery)
  • WHERE [NOT] EXISTS (subquery)

Dans certaines instructions Transact-SQL, la sous-requête peut être évaluée comme s’il s’agissait d’une requête indépendante. Conceptuellement, les résultats de la sous-requête sont substitués dans la requête externe (bien que ce ne soit pas nécessairement la façon dont SQL Server traite les instructions Transact-SQL avec les sous-requêtes).

Il existe trois sous-requêtes de base :

  • les sous-requêtes qui opèrent sur des listes, introduites par IN ou par un opérateur de comparaison modifié par ANY ou ALL ;
  • les sous-requêtes introduites par un opérateur de comparaison non modifié et qui doivent retourner une valeur unique ;
  • les sous-requêtes introduites par EXISTS qui constituent des tests d’existence.

Règles relatives aux sous-requêtes

Une sous-requête est soumise aux restrictions suivantes :

  • La liste de sélection d’une sous-requête introduite par un opérateur de comparaison ne peut contenir qu’une seule expression ou qu’un seul nom de colonne (sauf quand EXISTS et IN opèrent respectivement sur SELECT * ou sur une liste).
  • Si la clause WHERE d’une requête externe comprend un nom de colonne, ce dernier doit pouvoir être joint à la colonne spécifiée dans la liste de sélection de la sous-requête.
  • Les types de données ntext, text et image ne peuvent pas être utilisés dans la liste de sélection des sous-requêtes.
  • Parce qu’elles doivent renvoyer une seule valeur, les sous-requêtes introduites par un opérateur de comparaison non modifié (non suivi du mot-clé ANY ou ALL) ne peuvent pas inclure les clauses GROUP BY et HAVING.
  • Le mot-clé DISTINCT ne peut pas être utilisé avec des sous-requêtes incluant GROUP BY.
  • Les clauses COMPUTE et INTO ne peuvent pas être spécifiées.
  • ORDER BY peut être spécifiée uniquement quand TOP l’est aussi.
  • Une fenêtre créée à l’aide d’une sous-requête ne peut pas être mise à jour.
  • Par convention, la liste de sélection d’une sous-requête introduite par EXISTS est dotée d’un astérisque (*) au lieu d’un nom de colonne unique. Les règles à appliquer à une sous-requête introduite par EXISTS sont identiques à celles d’une liste de sélection standard. En effet, une sous-requête introduite par EXISTS crée un test d’existence et retourne les valeurs TRUE ou FALSE au lieu des données.

Qualifier les noms de colonnes dans les sous-requêtes

Dans l’exemple suivant, la colonne BusinessEntityID indiquée dans la clause WHERE de la requête externe est implicitement qualifiée par le nom de table figurant dans la clause FROM de la requête externe, (Sales.Store). La référence à la colonne CustomerID dans la liste de sélection de la sous-requête est qualifiée par la clause FROM de la sous-requête, c’est-à-dire par la table Sales.Customer.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Sales.Store
WHERE BusinessEntityID NOT IN
    (SELECT CustomerID
     FROM Sales.Customer
     WHERE TerritoryID = 5);
GO

En règle générale, les noms de colonne précisés dans une instruction sont implicitement qualifiés par la table spécifiée dans la clause FROM appartenant au même niveau d’imbrication. Si une colonne n’existe pas dans la table référencée de la clause FROM d’une sous-requête, elle est implicitement qualifiée par la table référencée de la clause FROM de la requête externe.

La requête se présente comme suit lorsque ces hypothèses implicites sont spécifiées :

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Sales.Store
WHERE Sales.Store.BusinessEntityID NOT IN
    (SELECT Sales.Customer.CustomerID
     FROM Sales.Customer
     WHERE TerritoryID = 5);
GO

Indiquer explicitement le nom de la table ne peut jamais être considéré comme une erreur. Par ailleurs, il est toujours possible de remplacer les hypothèses implicites concernant les noms de tables par des qualifications explicites.

Important

Si une colonne est référencée dans une sous-requête qui n’existe pas dans la table référencée par la clause FROM de la sous-requête, mais qu’elle existe dans une table référencée par la clause FROM de la requête externe, la requête s’exécute sans erreur. SQL Server qualifie implicitement la colonne dans la sous-requête avec le nom de la table dans la requête externe.

Plusieurs niveaux d’imbrication

Une sous-requête peut imbriquer une ou plusieurs autres sous-requêtes. Le nombre de sous-requêtes imbriquées dans une instruction est illimité.

La requête ci-après recherche les noms des employés qui sont également vendeurs.

USE AdventureWorks2022;
GO
SELECT LastName, FirstName
FROM Person.Person
WHERE BusinessEntityID IN
    (SELECT BusinessEntityID
     FROM HumanResources.Employee
     WHERE BusinessEntityID IN
        (SELECT BusinessEntityID
         FROM Sales.SalesPerson)
    );
GO

Voici le jeu de résultats obtenu.

LastName                                           FirstName
-------------------------------------------------- -----------------------
Jiang                                              Stephen
Abbas                                              Syed
Alberts                                            Amy
Ansman-Wolfe                                       Pamela
Campbell                                           David
Carson                                             Jillian
Ito                                                Shu
Mitchell                                           Linda
Reiter                                             Tsvi
Saraiva                                            Jos
Vargas                                             Garrett
Varkey Chudukatil                                  Ranjit
Valdez                                             Rachel
Tsoflias                                           Lynn
Pak                                                Jae
Blythe                                             Michael
Mensa-Annan                                        Tete

(17 row(s) affected)

La requête la plus profonde retourne les numéros d'identification des vendeurs. La requête du niveau supérieur suivant est évaluée en fonction de ces numéros d'identification de vendeurs et retourne les numéros d'identification des employés. Enfin, la requête externe se sert des numéros d'identification des contacts pour rechercher les noms des employés.

Cette requête peut également s'exprimer sous la forme d'une jointure :

USE AdventureWorks2022;
GO
SELECT LastName, FirstName
FROM Person.Person c
INNER JOIN HumanResources.Employee e
ON c.BusinessEntityID = e.BusinessEntityID
JOIN Sales.SalesPerson s
ON e.BusinessEntityID = s.BusinessEntityID;
GO

Sous-requêtes corrélées

De nombreuses requêtes peuvent être évaluées en exécutant une fois la sous-requête et en entrant la ou les valeurs obtenues dans la clause WHERE de la requête externe. Dans les requêtes qui contiennent une sous-requête en corrélation (aussi appelée sous-requête répétitive), la sous-requête dépend de la requête externe pour ses valeurs. Cela signifie que la sous-requête s'exécute de manière répétitive, une fois pour chaque ligne que la requête externe pourrait sélectionner. Cette requête récupère une instance du prénom et du nom de chaque employé pour lequel la prime est égale à 5 000 dans la table SalesPerson et dont le numéro d’identification se trouve dans les tables Employee et SalesPerson.

USE AdventureWorks2022;
GO
SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID
WHERE 5000.00 IN
    (SELECT Bonus
    FROM Sales.SalesPerson sp
    WHERE e.BusinessEntityID = sp.BusinessEntityID) ;
GO

Voici le jeu de résultats obtenu.

LastName FirstName BusinessEntityID
-------------------------- ---------- ------------
Ansman-Wolfe Pamela 280
Saraiva José 282

(2 row(s) affected)

La sous-requête précédente de cette instruction ne peut pas être évaluée indépendamment de la requête externe. Elle a besoin d’une valeur correspondant à Employee.BusinessEntityID. Toutefois, cette valeur change au fur et à mesure que SQL Server examine les différentes lignes d’Employé. Cette requête est évaluée de cette manière : SQL Server prend en compte chaque ligne de la table Employé pour les inclure dans les résultats en substituant la valeur de chaque ligne dans la requête interne. Par exemple, si SQL Server examine d’abord la ligne pour Syed Abbas, la variable Employee.BusinessEntityID prend la valeur 285, remplacée par SQL Server dans la requête interne. Ces deux exemples de requêtes représentent une décomposition de l’exemple précédent avec la sous-requête corrélée.

USE AdventureWorks2022;
GO
SELECT Bonus
FROM Sales.SalesPerson
WHERE BusinessEntityID = 285;
GO

Le résultat est 0.00 (Syed Abbas n’a pas reçu de commission parce qu’il n’est pas un commercial), donc la requête externe est évaluée à :

USE AdventureWorks2022;
GO
SELECT LastName, FirstName
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID
WHERE 5000 IN (0.00);
GO

Étant donné que la valeur est false, la ligne pour Syed Abbas n’est pas incluse dans les résultats de l’échantillon de requête précédent avec la sous-requête corrélée. Suivez la même procédure pour la ligne de Pamela Ansman-Wolfe. Vous remarquez que cette ligne est incluse dans les résultats, car WHERE 5000 IN (5000) inclut les résultats.

Les sous-requêtes corrélées peuvent aussi inclure des fonctions table dans la clause FROM en référençant des colonnes d’une table dans la requête externe sous la forme d’argument de cette fonction table. Dans ce cas, pour chaque ligne de la requête externe, la fonction table est évaluée en fonction de la sous-requête.

Types de sous-requête

Les sous-requêtes peuvent être spécifiées dans de nombreux endroits :

Sous-requêtes avec alias de table

De nombreuses instructions où la sous-requête et la requête externe portent sur la même table peuvent également être formulées sous forme de jointures réflexives (jointure d'une table à elle-même). Par exemple, vous pouvez rechercher les adresses d'employés dans un état donné à l'aide d'une sous-requête :

USE AdventureWorks2022;
GO
SELECT StateProvinceID, AddressID
FROM Person.Address
WHERE AddressID IN
    (SELECT AddressID
     FROM Person.Address
     WHERE StateProvinceID = 39);
GO

Voici le jeu de résultats obtenu.

StateProvinceID AddressID
----------- -----------
39 942
39 955
39 972
39 22660

(4 row(s) affected)

Vous pouvez aussi utiliser une auto-jointure :

USE AdventureWorks2022;
GO
SELECT e1.StateProvinceID, e1.AddressID
FROM Person.Address AS e1
INNER JOIN Person.Address AS e2
ON e1.AddressID = e2.AddressID
AND e2.StateProvinceID = 39;
GO

Les alias de table e1 et e2 sont nécessaires parce que la table jointe à elle-même apparaît dans deux rôles différents. Les alias peuvent aussi s'employer dans des requêtes imbriquées qui portent sur la même table, qu'il s'agisse d'une requête interne ou externe.

USE AdventureWorks2022;
GO
SELECT e1.StateProvinceID, e1.AddressID
FROM Person.Address AS e1
WHERE e1.AddressID IN
    (SELECT e2.AddressID
     FROM Person.Address AS e2
     WHERE e2.StateProvinceID = 39);
GO

Les alias de table explicites indiquent clairement qu’une référence à Person.Address dans la sous-requête n’a pas la même signification que la référence dans la requête externe.

Sous-requêtes avec IN

Le résultat d’une sous-requête introduite par IN (ou par NOT IN) est une liste de valeurs zéro ou plus. Dès que la sous-requête retourne des résultats, la requête externe les utilise. La requête suivante recherche le nom de toutes les roues fabriquées par Adventure Works Cycles.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE [Name] = 'Wheels');
GO

Voici le jeu de résultats obtenu.

Name
----------------------------
LL Mountain Front Wheel
ML Mountain Front Wheel
HL Mountain Front Wheel
LL Road Front Wheel
ML Road Front Wheel
HL Road Front Wheel
Touring Front Wheel
LL Mountain Rear Wheel
ML Mountain Rear Wheel
HL Mountain Rear Wheel
LL Road Rear Wheel
ML Road Rear Wheel
HL Road Rear Wheel
Touring Rear Wheel

(14 row(s) affected)

Cette instruction est évaluée en deux étapes. D'abord, la requête interne retourne le numéro d'identification de la sous-catégorie qui correspond au nom « Wheel » (roue) (17). Ensuite, cette valeur est intégrée dans la requête externe, qui recherche les noms de produits correspondant aux numéros d’identification des sous-catégories dans Production.Product.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN ('17');
GO

Dans la résolution de problèmes de ce type, une jointure se distingue d'une sous-requête en ce sens qu'elle vous permet d'afficher des colonnes provenant de plusieurs tables dans les résultats. Par exemple, si vous voulez inclure le nom de la sous-catégorie de produits dans le résultat, vous devez utiliser une jointure.

USE AdventureWorks2022;
GO
SELECT p.[Name], s.[Name]
FROM Production.Product p
INNER JOIN Production.ProductSubcategory s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
AND s.[Name] = 'Wheels';
GO

Voici le jeu de résultats obtenu.

Name
LL Mountain Front Wheel Wheels
ML Mountain Front Wheel Wheels
HL Mountain Front Wheel Wheels
LL Road Front Wheel Wheels
ML Road Front Wheel Wheels
HL Road Front Wheel Wheels
Touring Front Wheel Wheels
LL Mountain Rear Wheel Wheels
ML Mountain Rear Wheel Wheels
HL Mountain Rear Wheel Wheels
LL Road Rear Wheel Wheels
ML Road Rear Wheel Wheels
HL Road Rear Wheel Wheels
Touring Rear Wheel Wheels

(14 row(s) affected)

La requête suivante recherche le nom de tous les fournisseurs dont la cote de solvabilité est bonne, auprès desquels Adventure Works Cycles commande au moins 20 articles et dont le délai moyen de livraison est inférieur à 16 jours.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Purchasing.Vendor
WHERE CreditRating = 1
AND BusinessEntityID IN
    (SELECT BusinessEntityID
     FROM Purchasing.ProductVendor
     WHERE MinOrderQty >= 20
     AND AverageLeadTime < 16);
GO

Voici le jeu de résultats obtenu.

Name
--------------------------------------------------
Compete Enterprises, Inc
International Trek Center
First National Sport Co.
Comfort Road Bicycles
Circuit Cycles
First Rate Bicycles
Jeff's Sporting Goods
Competition Bike Training Systems
Electronic Bike Repair & Supplies
Crowley Sport
Expert Bike Co
Team Athletic Co.
Compete, Inc.

(13 row(s) affected)

La requête interne est évaluée, puis retourne les numéros d'identification des trois fournisseurs qui remplissent les critères de la sous-requête. La requête externe est évaluée ensuite. Notez qu’il est possible d’inclure plusieurs conditions dans la clause WHERE des requêtes interne et externe.

En utilisant une jointure, la requête s'exprime de la façon suivante :

USE AdventureWorks2022;
GO
SELECT DISTINCT [Name]
FROM Purchasing.Vendor v
INNER JOIN Purchasing.ProductVendor p
ON v.BusinessEntityID = p.BusinessEntityID
WHERE CreditRating = 1
  AND MinOrderQty >= 20
  AND AverageLeadTime < 16;
GO

Une jointure peut toujours s'exprimer sous la forme d'une sous-requête. Une sous-requête peut souvent s'exprimer sous la forme d'une jointure, mais pas toujours. Les jointures étant symétriques, vous pouvez joindre une table A à une table B ou inversement et obtenir le même résultat. En revanche, il n’en va pas de même lorsqu’il s’agit d’une sous-requête.

Sous-requêtes avec NOT IN

Les sous-requêtes introduites par le mot clé NOT IN retournent également une liste de valeurs zéro ou plus. La requête suivante permet de trouver les noms des produits autres que les vélos assemblés.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID NOT IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE [Name] = 'Mountain Bikes'
        OR [Name] = 'Road Bikes'
        OR [Name] = 'Touring Bikes');
GO

Cette instruction ne peut pas être convertie en une jointure. La jointure analogue non égale a une signification différente : elle trouve les noms des produits qui sont dans une sous-catégorie autre qu’un vélo assemblé.

Sous-requêtes dans les instructions UPDATE, DELETE et INSERT

Les sous-requêtes peuvent être imbriquées dans les instructions de manipulation de données (DML, Data Manipulation Language) UPDATE, DELETE, INSERT et SELECT.

L’exemple suivant double la valeur de la colonne ListPrice dans la table Production.Product. La sous-requête contenue dans la clause WHERE fait référence à la table Purchasing.ProductVendor pour limiter les lignes mises à jour dans la table Product à celles fournies par BusinessEntity 1540 uniquement.

USE AdventureWorks2022;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2
WHERE ProductID IN
    (SELECT ProductID
     FROM Purchasing.ProductVendor
     WHERE BusinessEntityID = 1540);
GO

Vous trouverez ci-dessous une instruction UPDATE équivalente utilisant une jointure :

USE AdventureWorks2022;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2
FROM Production.Product AS p
INNER JOIN Purchasing.ProductVendor AS pv
    ON p.ProductID = pv.ProductID AND BusinessEntityID = 1540;
GO

Par souci de clarté, si la même table est elle-même référencée dans d’autres sous-requêtes, utilisez l’alias de la table cible :

USE AdventureWorks2022;
GO
UPDATE p
SET ListPrice = ListPrice * 2
FROM Production.Product AS p
INNER JOIN Purchasing.ProductVendor AS pv
    ON p.ProductID = pv.ProductID AND BusinessEntityID = 1540;
GO

Sous-requêtes avec opérateurs de comparaison

Les sous-requêtes peuvent être introduites par l’un de ces opérateurs de comparaison (=, < >, >, > =, <, ! >, ! < ou < =).

Une sous-requête introduite par un opérateur de comparaison non modifié (autrement dit, un opérateur de comparaison non suivi de ANY ou de ALL) ne doit retourner qu’une seule valeur et non une liste de valeurs, à l’instar des sous-requêtes introduites par IN. Si une sous-requête de ce type retourne plusieurs valeurs, SQL Server affiche un message d’erreur.

Pour utiliser une sous-requête introduite par un opérateur de comparaison non modifié, et savoir si elle ne va retourner qu'une seule valeur, vous devez bien connaître vos données et la nature du problème.

Par exemple, supposons que chaque commercial couvre uniquement un territoire commercial, et que vous voulez trouver le nom des clients situés dans le territoire couvert par Linda Mitchell, vous pouvez écrire une instruction comportant une sous-requête introduite par le seul opérateur de comparaison = (signe égal).

USE AdventureWorks2022;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID =
    (SELECT TerritoryID
     FROM Sales.SalesPerson
     WHERE BusinessEntityID = 276);
GO

Toutefois, si Linda Mitchell couvre plusieurs territoires commerciaux, un message d’erreur s’affiche. À la place de l’opérateur de comparaison = (signe égal), vous pouvez utiliser une sous-requête introduite par IN (=ANY fonctionne aussi).

Étant donné que les fonctions d'agrégation retournent une seule unique, elles figurent souvent dans les sous-requêtes introduites par un opérateur de comparaison non modifié. Par exemple, l'instruction suivante recherche le nom de tous les produits dont le tarif est supérieur au tarif moyen.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >
    (SELECT AVG (ListPrice)
     FROM Production.Product);
GO

Étant donné que les sous-requêtes introduites avec des opérateurs de comparaison non modifiés doivent renvoyer une seule valeur, elles ne peuvent pas inclure de clauses GROUP BY ou HAVING, sauf si vous savez que la clause GROUP BY ou HAVING renvoie elle-même une seule valeur. Par exemple, la requête suivante trouve les produits vendus plus chers que le produit le moins cher de ProductSubcategoryID 14.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >
    (SELECT MIN (ListPrice)
     FROM Production.Product
     GROUP BY ProductSubcategoryID
     HAVING ProductSubcategoryID = 14);
GO

Opérateurs de comparaison modifiés par ANY, SOME ou ALL

Les opérateurs de comparaison qui introduisent une sous-requête peuvent être modifiés par les mots clés ALL ou ANY. SOME est un équivalent de ANY dans la norme ISO. Pour plus d’informations sur ces opérateurs de comparaison, consultez SOME | ANY.

Les sous-requêtes introduites par un opérateur de comparaison modifié retournent une liste contenant aucune ou plusieurs valeurs et peuvent contenir une clause GROUP BY ou HAVING. Ces sous-requêtes peuvent être reformulées avec EXISTS.

En utilisant l’opérateur de comparaison > comme exemple, > ALL est supérieur à toutes les valeurs. En d'autres termes, cela signifie supérieur à la valeur maximale. Par exemple, > ALL (1, 2, 3) signifie supérieur à 3. > ANY signifie supérieur à une valeur au moins, autrement dit supérieur à la valeur minimale. Par conséquent, > ANY (1, 2, 3) signifie supérieur à 1.

Pour qu’une ligne d’une sous-requête comportant > ALL réponde au critère défini dans la requête externe, la valeur de la colonne introduisant la sous-requête doit être supérieure à chaque valeur de la liste de valeurs retournée par la sous-requête.

De même, > ANY signifie qu’afin qu’une ligne satisfasse au critère défini dans la requête externe, la valeur spécifiée dans la colonne qui introduit la sous-requête doit être supérieure à au moins une valeur de la liste retournée par la sous-requête.

La requête suivante donne un exemple de sous-requête introduite par un opérateur de comparaison modifié par ANY. Elle recherche les produits dont les tarifs sont supérieurs ou égaux au tarif maximal de n'importe quelle sous-catégorie de produits.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >= ANY
    (SELECT MAX (ListPrice)
     FROM Production.Product
     GROUP BY ProductSubcategoryID);
GO

Pour chaque sous-catégorie Product, la requête interne recherche le tarif maximal. Parmi toutes ces valeurs, la requête externe recherche les tarifs de produit particulier supérieurs ou égaux au tarif maximal de n'importe quelle sous-catégorie de produits. Si ANY est remplacé par ALL, la requête ne renvoie que les produits dont le prix catalogue est supérieur ou égal à tous les prix catalogues renvoyés dans la requête interne.

Si la sous-requête ne renvoie aucune valeur, la requête entière ne renverra aucune valeur.

L’opérateur = ANY est équivalent à IN. Par exemple, pour trouver le nom de toutes les roues fabriquées par Adventure Works Cycles, vous pouvez utiliser IN ou = ANY.

--Using = ANY
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID = ANY
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Wheels');
GO

--Using IN
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Wheels');
GO

Les jeu de résultats de chacune de ces deux requêtes sont présentés ci-dessous :

Name
--------------------------------------------------
LL Mountain Front Wheel
ML Mountain Front Wheel
HL Mountain Front Wheel
LL Road Front Wheel
ML Road Front Wheel
HL Road Front Wheel
Touring Front Wheel
LL Mountain Rear Wheel
ML Mountain Rear Wheel
HL Mountain Rear Wheel
LL Road Rear Wheel
ML Road Rear Wheel
HL Road Rear Wheel
Touring Rear Wheel

(14 row(s) affected)

Toutefois, l’opérateur <> ANY diffère de NOT IN :

  • <> ANY signifie non = a, ou non = b, ou non = c
  • NOT IN signifie non = a, et non = b, et non = c
  • <> ALL signifie identique à NOT IN

Par exemple, la requête suivante recherche les clients situés dans un secteur non couvert par un vendeur.

USE AdventureWorks2022;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID <> ANY
    (SELECT TerritoryID
     FROM Sales.SalesPerson);
GO

Les résultats comprennent tous les clients, à l'exception de ceux dont le secteur géographique a pour valeur NULL, car chaque secteur affecté à un client est couvert par un vendeur. La requête interne recherche tous les secteurs de vente couverts par les vendeurs. Ensuite, pour chaque territoire, la requête externe trouve les clients qui n’en font pas partie.

Pour la même raison, quand vous utilisez NOT IN dans cette requête, les résultats ne comprennent aucun des clients.

Vous pouvez obtenir les mêmes résultats avec l’opérateur <> ALL, qui est l’équivalent de NOT IN.

Sous-requêtes avec EXISTS

Une sous-requête introduite par le mot clé EXISTS constitue un test d’existence. La clause WHERE de la requête externe teste l’existence des lignes retournées par la sous-requête. La sous-requête ne produit pas réellement de données ; elle renvoie une valeur TRUE ou FALSE.

Une sous-requête introduite par EXISTS présente la syntaxe suivante :

WHERE [NOT] EXISTS (subquery)

La requête suivante recherche le nom de tous les produits de la sous-catégorie Wheels :

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE EXISTS
    (SELECT *
     FROM Production.ProductSubcategory
     WHERE ProductSubcategoryID =
            Production.Product.ProductSubcategoryID
        AND [Name] = 'Wheels');
GO

Voici le jeu de résultats obtenu.

Name
--------------------------------------------------
LL Mountain Front Wheel
ML Mountain Front Wheel
HL Mountain Front Wheel
LL Road Front Wheel
ML Road Front Wheel
HL Road Front Wheel
Touring Front Wheel
LL Mountain Rear Wheel
ML Mountain Rear Wheel
HL Mountain Rear Wheel
LL Road Rear Wheel
ML Road Rear Wheel
HL Road Rear Wheel
Touring Rear Wheel

(14 row(s) affected)

Pour comprendre les résultats de cette requête, considérez les noms de chaque produit un par un. La valeur provoque-t-elle le retour d'au moins une ligne par la sous-requête ? En d’autres termes, le test d’existence retourne-t-il la valeur TRUE ?

Les sous-requêtes introduites par EXISTS sont légèrement différentes des autres sous-requêtes pour les raisons suivantes :

  • Le mot-clé EXISTS n’est pas précédé d’un nom de colonne, d’une constante ou de toute autre expression.
  • La liste de sélection d’une sous-requête introduite par EXISTS se résume presque toujours à un astérisque (*). Il n’y a aucune raison d’énumérer les noms des colonnes, car vous testez simplement l’existence de lignes répondant aux conditions spécifiées dans la sous-requête.

Le mot-clé EXISTS est important, car très souvent, aucune autre formulation n’est possible sans sous-requêtes. Bien que certaines requêtes créées avec EXISTS ne puissent pas être exprimées autrement, de nombreuses requêtes peuvent utiliser IN ou un opérateur de comparaison modifié par ANY ou ALL pour obtenir des résultats similaires.

Par exemple, la requête précédente peut être exprimée à l’aide de IN :

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE [Name] = 'Wheels');
GO

Sous-requêtes avec NOT EXISTS

NOT EXISTS fonctionne comme EXISTS, sauf que la clause WHERE dans laquelle il est utilisé est exécutée si aucune ligne n’est renvoyée par la sous-requête.

Par exemple, pour trouver les noms des produits qui ne sont pas dans la sous-catégorie des wheels :

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE NOT EXISTS
    (SELECT *
     FROM Production.ProductSubcategory
     WHERE ProductSubcategoryID =
            Production.Product.ProductSubcategoryID
        AND [Name] = 'Wheels');
GO

Sous-requêtes utilisées à la place d’une expression

Dans Transact-SQL, une sous-requête peut être substituée partout où une expression peut être utilisée dans les instructions SELECT, UPDATE, INSERT et DELETE, à l’exception d’une liste ORDER BY.

L'exemple suivant illustre l'application de cette amélioration. Cette requête fournit le prix de tous les produits VTT, leur prix moyen, ainsi que la différence entre le prix de chaque VTT et le prix moyen.

USE AdventureWorks2022;
GO
SELECT [Name], ListPrice,
(SELECT AVG(ListPrice) FROM Production.Product) AS Average,
    ListPrice - (SELECT AVG(ListPrice) FROM Production.Product)
    AS Difference
FROM Production.Product
WHERE ProductSubcategoryID = 1;
GO

Voir aussi

Syntaxe

Concepts relatifs à la performance des requêtes