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.

Cet article requiert l'échantillon de base de données AdventureWorks, que vous pouvez télécharger à partir de la page d'accueil des AdventureWorks2022échantillons et Projects de communautés 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 SELECT instruction.

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

Notions de base de la sous-requête

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 qui incluent des sous-requêtes peuvent également être formulées en tant que jointures. D'autres questions peuvent uniquement être posées par le biais de sous-requêtes. Dans Transact-SQL, il n’existe généralement aucune différence de performances entre une instruction qui inclut une sous-requête et une version sémantiquement équivalente qui ne le fait pas. Pour plus d’informations architecturales sur la façon dont SQL Server traite les requêtes, 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 montre à la fois une sous-requête SELECT et une jointure SELECT qui retournent 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. Il ne peut pas inclure ou COMPUTEFOR BROWSE clause et peut inclure uniquement une ORDER BY clause lorsqu’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 apparaît uniquement dans une sous-requête et non dans la requête externe, les colonnes de cette table ne peuvent pas être incluses dans la sortie (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 remplacés dans la requête externe (bien que cela ne soit pas nécessairement la façon dont SQL Server traite réellement des instructions Transact-SQL avec des 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 de sous-requête

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, texte et image ne peuvent pas être utilisés dans la liste de sélection des sous-requêtes.
  • Étant donné qu’ils doivent retourner une valeur unique, les sous-requêtes introduites par un opérateur de comparaison non modifié (un opérateur de comparaison non suivi du mot clé ANY ou ALL) ne peuvent pas inclure GROUP BY et HAVING clauses.
  • Le DISTINCT mot clé ne peut pas être utilisé avec les sous-requêtes qui incluent GROUP BY.
  • Les COMPUTE clauses et INTO les clauses ne peuvent pas être spécifiées.
  • ORDER BY peut être spécifiée uniquement quand TOP l’est aussi.
  • Une vue 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 dans la FROM clause d’une sous-requête, elle est implicitement qualifiée par la table référencée dans la FROM clause de la requête externe.

Voici à quoi ressemble la requête avec ces hypothèses implicites 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

Il n’est jamais incorrect d’indiquer explicitement le nom de la table, et il est toujours possible de remplacer les hypothèses implicites relatives aux noms de tables avec 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 de 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 dans cette instruction ne peut pas être évaluée indépendamment de la requête externe. Elle a besoin d’une valeur pour Employee.BusinessEntityID, mais cette valeur change lorsque SQL Server examine différentes lignes dans Employee. C’est exactement la façon dont cette requête est évaluée : SQL Server considère chaque ligne de la table Employee pour l’inclusion dans les résultats en remplaçant la valeur de chaque ligne dans la requête interne. Par exemple, si SQL Server examine d’abord la ligne, Syed Abbasla variable Employee.BusinessEntityID prend la valeur 285, que SQL Server remplace 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 bonus parce qu’il ne s’agit pas d’une personne de vente), de sorte que 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 cette valeur est false, la ligne pour Syed Abbas laquelle elle n’est pas incluse dans les résultats de l’exemple 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 voyez que cette ligne est incluse dans les résultats, car WHERE 5000 IN (5000) elle inclut des 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 des 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 e1 table et e2 sont requis, car 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 ne signifie pas la même chose 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). Deuxièmement, cette valeur est remplacée dans la requête externe, qui recherche les noms de produit qui vont avec les numéros d’identification de sous-catégorie 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. La même chose n’est pas vraie si une sous-requête est impliquée.

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 recherche les noms des produits qui ne sont pas finis pour les vélos.

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 jointure. La jointure analogue non égale a une signification différente : elle trouve les noms des produits qui se trouvent dans une sous-catégorie qui n’est pas un vélo fini.

Sous-requêtes dans UPDATE, DELETEet INSERT instructions

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

Voici une instruction équivalente UPDATE à l’aide d’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 retourner une valeur unique, elles ne peuvent pas inclure GROUP BY ou HAVING clauses, sauf si vous connaissez la ou HAVING la GROUP BY clause elle-même retourne une valeur unique. 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, SOMEou 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.

L’utilisation de l’opérateur > de comparaison comme exemple > ALL signifie une valeur supérieure à chaque valeur. 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 elle est modifiée ALL, la requête retourne uniquement les produits dont le prix de liste est supérieur ou égal à tous les prix de liste retournés dans la requête interne.

Si la sous-requête ne retourne aucune valeur, la requête entière ne retourne pas de valeurs.

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

Voici le jeu de résultats pour l’une ou l’autre requête :

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 territoires de vente couverts par les vendeurs, puis, pour chaque territoire, la requête externe recherche les clients qui ne sont pas en un.

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 retourne une valeur de 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 d’une 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’existe aucune raison de répertorier les noms de colonnes, car vous testez simplement si les lignes qui répondent aux conditions spécifiées dans la sous-requête existent.

Le EXISTS mot clé est important, car il n’existe souvent aucune autre formulation sans sous-requêtes. Bien que certaines requêtes créées avec EXISTS ne puissent pas être exprimées d’une autre façon, de nombreuses requêtes peuvent utiliser IN ou utiliser 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 la WHERE clause dans laquelle elle est utilisée est satisfaite si aucune ligne n’est retournée par la sous-requête.

Par exemple, pour rechercher les noms des produits qui ne se trouvent pas dans la sous-catégorie roues :

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 n’importe où une expression peut être utilisée dans SELECT, UPDATE, INSERTet DELETE des instructions, sauf dans une ORDER BY liste.

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 de performances des requêtes