Utilisation d’une sous-requête corrélée dans une instruction T-SQL

juin 2, 2021admin

Dans l’article du mois dernier, j’ai discuté de ce qu’est une sous-requête et comment l’utiliser dans une instruction T-SQL. Ce mois-ci, je vais développer ce sujet en abordant les sous-requêtes corrélées. J’expliquerai ce qu’est une sous-requête corrélée et je montrerai différents exemples sur la façon d’utiliser une sous-requête dans une instruction T-SQL.

Qu’est-ce qu’une sous-requête corrélée ?

Une sous-requête corrélée est une instruction SELECT imbriquée dans une autre instruction T-SQL, qui contient une référence à une ou plusieurs colonnes de la requête externe. Par conséquent, on peut dire que la sous-requête corrélée dépend de la requête externe. C’est la principale différence entre une sous-requête corrélée et une simple sous-requête. Une sous-requête simple ne dépend pas de la requête externe, peut être exécutée indépendamment de cette dernière et renvoie un ensemble de résultats. Une sous-requête corrélée, puisqu’elle dépend de la requête externe, renverra une erreur de syntaxe si elle est exécutée seule.

Une sous-requête corrélée sera exécutée plusieurs fois lors du traitement de l’instruction T-SQL qui contient la sous-requête corrélée. La sous-requête corrélée sera exécutée une fois pour chaque ligne candidate sélectionnée par la requête externe. Les colonnes de la requête externe, référencées dans la sous-requête corrélée, sont remplacées par les valeurs de la ligne candidate avant chaque exécution. En fonction des résultats de l’exécution de la sous-requête corrélée, elle déterminera si la ligne de la requête externe est renvoyée dans le jeu de résultats final.

Utilisation d’une sous-requête corrélée dans une clause WHERE

Supposons que vous souhaitiez obtenir un rapport de toutes les « OrderID’s » où le client n’a pas acheté plus de 10% de la quantité moyenne vendue pour un produit donné. De cette façon, vous pourriez examiner ces commandes, et éventuellement contacter les clients, pour aider à déterminer s’il y avait une raison pour cette commande de faible quantité. Une sous-requête corrélée dans une clause WHERE peut vous aider à produire ce rapport. Voici une instruction SELECT qui produit la liste souhaitée de « OrderID’s »:

select distinct OrderId from Northwind.dbo. OD where Quantity <l; (select avg(Quantity) * .1 from Northwind.dbo. where OD.ProductID = ProductID)

La sous-requête corrélée dans la commande ci-dessus est contenue dans la parenthèse qui suit le signe plus grand que dans la clause WHERE ci-dessus. Vous pouvez voir ici que cette sous-requête corrélée contient une référence à « OD.ProductID ». Cette référence compare le « ProductID » de la requête externe avec le « ProductID » de la requête interne. Lorsque cette requête est exécutée, le moteur SQL exécute la requête interne, la sous-requête corrélée, pour chaque enregistrement « ». Cette requête interne calculera la « Quantité » moyenne pour le « ProductID » particulier de la ligne candidate traitée dans la requête externe. Cette sous-requête corrélée détermine si la requête interne renvoie une valeur qui répond à la condition de la clause WHERE. Si c’est le cas, la ligne identifiée par la requête externe est placée dans le jeu d’enregistrements qui sera renvoyé par l’instruction T-SQL SELECT complète.

Le code ci-dessous est un autre exemple qui utilise une sous-requête corrélée dans la clause WHERE pour afficher les deux premiers clients, en fonction du montant en dollars associé à leurs commandes, par région. Vous pourriez vouloir exécuter une requête de ce type pour pouvoir récompenser ces clients, puisqu’ils achètent le plus par région.

select CompanyName, ContactName, Address, City, Country, PostalCode from Northwind.dbo.Customers OuterCwhere CustomerID in ( select top 2 InnerC.CustomerId from Northwind.dbo. OD join Northwind.dbo.Orders O on OD.OrderId = O.OrderID join Northwind.dbo.Customers InnerC on O.CustomerID = InnerC.CustomerId Where Region = OuterC.Region group by Region, InnerC.CustomerId order by sum(UnitPrice * Quantity * (1-Discount)) desc ) order by Region

Ici, vous pouvez voir que la requête interne est une sous-requête corrélée car elle fait référence à « OuterC », qui est l’alias de la table « Northwind.DBO.Customer » dans la requête externe. Cette requête interne utilise la valeur « Region » pour calculer les deux premiers clients de la région associée à la ligne traitée par la requête externe. Si le « CustomerID » de la requête externe est l’un des deux meilleurs clients, alors l’enregistrement est placé dans le jeu d’enregistrements à renvoyer.

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

Disons que vos organisations veulent exécuter un programme d’incitation tout au long de l’année pour augmenter les revenus. Par conséquent, elles annoncent à vos clients que si chaque commande qu’ils passent, au cours de l’année, est supérieure à 750 $, vous leur accorderez une remise à la fin de l’année à raison de 75 $ par commande passée. Vous trouverez ci-dessous un exemple de calcul du montant de la remise. Cet exemple utilise une sous-requête corrélée dans la clause HAVING pour identifier les clients qui remplissent les conditions pour recevoir la remise. Voici mon code pour cette requête:

select C.CustomerID, Count(*)*75 Rebate from Northwind.DBO.Customers C join Northwind.DBO.Orders O on c.CustomerID = O.CustomerID where Datepart(yy,OrderDate) = '1998' group by C.CustomerId having 750 < ALL(select sum(UnitPrice * Quantity * (1-Discount)) from Northwind.DBO.Orders O join Northwind.DBO. OD on O.OrderID = OD.OrderID where CustomerID = C.CustomerId and Datepart(yy,OrderDate) = '1998' group by O.OrderId )

En examinant cette requête, vous pouvez voir que j’utilise une requête corrélée dans la clause HAVING pour calculer le montant total de la commande pour chaque commande client. J’utilise le « CustomerID » de la requête externe et l’année de la commande « Datepart(yy,OrderDate) », pour aider à identifier les enregistrements de commande associés à chaque client, qui ont été passés l’année ‘1998’. Pour ces enregistrements associés, je calcule le montant total de la commande, pour chaque commande, en additionnant tous les enregistrements « », en utilisant la formule suivante : sum(UnitPrice * Quantity * (1-Discount)). Si chaque commande d’un client, pour l’année 1998, a un montant total en dollars supérieur à 750, je calcule alors le montant de la remise dans la requête externe en utilisant cette formule « Count(*)*75 « .

Le moteur de requête du serveur SQL n’exécutera la sous-requête corrélée interne dans la clause HAVING que pour les enregistrements de clients identifiés dans la requête externe, ou en gros, seulement les clients qui ont passé des commandes en » 1998 « .

Exécution d’une déclaration de mise à jour à l’aide d’une sous-requête corrélée

Une sous-requête corrélée peut même être utilisée dans une déclaration de mise à jour. Voici un exemple:

create table A(A int, S int)create table B(A int, B int)set nocount on insert into A(A) values(1)insert into A(A) values(2)insert into A(A) values(3)insert into B values(1,1)insert into B values(2,1)insert into B values(2,1)insert into B values(3,1)insert into B values(3,1)insert into B values(3,1)update A set S = (select sum(B) from B where A.A = A group by A) select * from Adrop table A,B

Voici le jeu de résultats que j’obtiens lorsque j’exécute cette requête sur ma machine:

A S ----------- ----------- 1 12 23 3

Dans ma requête ci-dessus, j’ai utilisé la sous-requête corrélée pour mettre à jour la colonne A de la table A avec la somme de la colonne B de la table B pour les lignes qui ont la même valeur dans la colonne A que la ligne mise à jour.

Conclusion

Laissez-moi résumer. Une sous-requête et une sous-requête corrélée sont des requêtes SELECT codées à l’intérieur d’une autre requête, dite requête externe. La sous-requête corrélée et la sous-requête contribuent à déterminer le résultat de l’ensemble de résultats renvoyé par la requête complète. Une sous-requête, lorsqu’elle est exécutée indépendamment de la requête externe, renvoie un ensemble de résultats et ne dépend donc pas de la requête externe. En revanche, une sous-requête corrélée ne peut pas être exécutée indépendamment de la requête externe car elle utilise une ou plusieurs références à des colonnes de la requête externe pour déterminer le jeu de résultats renvoyé par la sous-requête corrélée. J’espère que vous comprenez maintenant les différentes sous-requêtes et sous-requêtes corrélées, et comment elles peuvent être utilisées dans votre code T-SQL.

» Voir tous les articles du chroniqueur Gregory A. Larsen