Een gecorreleerde subquery in een T-SQL verklaring gebruiken

juni 2, 2021admin

In het artikel van vorige maand heb ik besproken wat en hoe je een subquery in een T-SQL verklaring kunt gebruiken. Deze maand zal ik dit onderwerp uitbreiden door het bespreken van gecorreleerde subqueries. Ik zal uitleggen wat een correlated subquery is, en een aantal verschillende voorbeelden laten zien hoe je een subquery in een T-SQL statement kunt gebruiken.

Wat is een Correlated Subquery?

Een correlated subquery is een SELECT statement genest binnen een ander T-SQL statement, dat een verwijzing bevat naar een of meer kolommen in de outer query. Daarom kan worden gezegd dat de gecorreleerde subquery afhankelijk is van de buitenste query. Dit is het belangrijkste verschil tussen een gecorreleerde subquery en een gewone subquery. Een gewone subquery is niet afhankelijk van de buitenstaande query, kan onafhankelijk van de buitenstaande query uitgevoerd worden, en zal een resultaat set teruggeven. Een gecorreleerde sub query, omdat deze afhankelijk is van de buiten query, zal een syntax errors teruggeven als deze op zichzelf wordt uitgevoerd.

Een gecorreleerde sub query zal vele malen worden uitgevoerd tijdens het verwerken van het T-SQL statement dat de gecorreleerde sub query bevat. De gecorreleerde sub query wordt eenmaal uitgevoerd voor elke kandidaat rij die door de buiten query is geselecteerd. De kolommen van de buitenquery, waarnaar in de gecorreleerde subquery wordt verwezen, worden vóór elke uitvoering vervangen door waarden uit de kandidaatrij. Afhankelijk van de resultaten van de uitvoering van de gecorreleerde sub query, wordt bepaald of de rij van de buiten query wordt geretourneerd in de uiteindelijke resultaat set.

Het gebruik van een gecorreleerde sub query in een WHERE clausule

Stel dat u een rapport wilt van alle “OrderID’s” waarbij de klant niet meer dan 10% van de gemiddelde verkochte hoeveelheid voor een bepaald product heeft gekocht. Op deze manier kunt u deze orders bekijken, en eventueel contact opnemen met de klanten, om te helpen bepalen of er een reden was voor de lage bestelhoeveelheid. Een gecorreleerde subquery in een WHERE-clausule kan u helpen dit rapport te produceren. Hier is een SELECT statement dat de gewenste lijst van “OrderID’s” produceert:

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

De gecorreleerde subquery in het bovenstaande commando staat tussen haakjes na het groter dan teken in de WHERE clausule hierboven. Hier kun je zien dat deze gecorreleerde subquery een verwijzing bevat naar “OD.ProductID”. Deze verwijzing vergelijkt de “ProductID” van de buitenste query met de “ProductID” van de binnenste query. Wanneer deze query wordt uitgevoerd, zal de SQL engine de binnenste query, de gecorreleerde subquery, uitvoeren voor elk “” record. Deze binnenste query berekent de gemiddelde “Hoeveelheid” voor de specifieke “ProductID” voor de kandidaat-rij die in de buitenste query wordt verwerkt. Deze gecorreleerde subquery bepaalt of de binnenquery een waarde retourneert die voldoet aan de voorwaarde van de WHERE-clausule. Als dat het geval is, wordt de rij die door de buitenste query wordt geïdentificeerd, geplaatst in de record set die zal worden geretourneerd van de volledige T-SQL SELECT statement.

De code hieronder is een ander voorbeeld dat een gecorreleerde subquery gebruikt in de WHERE clause om de top twee klanten weer te geven, gebaseerd op het dollar bedrag dat is gekoppeld aan hun orders, per regio. Misschien wilt u een query als deze uitvoeren, zodat u deze klanten kunt belonen, omdat zij het meest per regio kopen.

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

Hier kunt u zien dat de binnenste query een gecorreleerde subquery is, omdat deze verwijst naar “OuterC”, wat de tabel alias is voor de “Northwind.DBO.Customer” tabel in de buitenste query. Deze binnen query gebruikt de “Regio” waarde om de top twee klanten te berekenen voor de regio geassocieerd met de rij die wordt verwerkt uit de buiten query. Als de “CustomerID” van de outer query een van de top twee klanten is, dan wordt het record in de te retourneren record set geplaatst.

Gerelateerde Subquery in de HAVING Clause

Stel dat uw organisatie een jaar lang een incentive programma wil uitvoeren om de omzet te verhogen. Daarom adverteren zij uw klanten dat als elke bestelling die zij gedurende het jaar plaatsen meer dan $750 bedraagt, u hun aan het eind van het jaar een korting geeft van $75 per bestelling die zij plaatsen. Hieronder ziet u een voorbeeld van hoe u de korting berekent. Dit voorbeeld gebruikt een gecorreleerde subquery in de HAVING clausule om de klanten te identificeren die in aanmerking komen om de korting te ontvangen. Hier is mijn code voor deze query:

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 )

Als je deze query bekijkt, zie je dat ik een gecorreleerde query in de HAVING-clausule gebruik om het totale orderbedrag voor elke klantorder te berekenen. Ik gebruik de “CustomerID” van de buitenste query en het jaar van de order “Datepart(yy,OrderDate)”, om de order records geassocieerd met elke klant, die werden geplaatst het jaar ‘1998’ te helpen identificeren. Voor deze geassocieerde records bereken ik het totale orderbedrag, voor elke order, door alle “” records bij elkaar op te tellen, gebruik makend van de volgende formule: sum(UnitPrice * Quantity * (1-Discount)). Als elke bestelling voor een klant, voor het jaar 1998 een totaal dollarbedrag heeft dat hoger is dan 750, dan bereken ik het kortingbedrag in de buitenste query met behulp van deze formule “Count(*)*75 “.

De query-engine van SQL Server zal de binnenste gecorreleerde subquery in de HAVING-clausule alleen uitvoeren voor die klantrecords die in de buitenste query zijn geïdentificeerd, of eigenlijk alleen die klantrecords die in “1998” orders hebben geplaatst.

Een Update Statement uitvoeren met behulp van een gecorreleerde subquery

Een gecorreleerde subquery kan zelfs in een update statement worden gebruikt. Hier is een voorbeeld:

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

Hier is de resultatenset die ik krijg als ik deze query op mijn machine uitvoer:

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

In mijn query hierboven heb ik de correlated subquery gebruikt om kolom A in tabel A bij te werken met de som van kolom B in tabel B voor rijen die dezelfde waarde in kolom A hebben als de rij die wordt bijgewerkt.

Conclusie

Laat ik even samenvatten. Een subquery en een gecorreleerde subquery zijn SELECT queries gecodeerd binnen een andere query, bekend als de outer query. De gecorreleerde subquery en de subquery bepalen mede de uitkomst van de resultatenset die door de volledige query wordt geretourneerd. Een subquery die onafhankelijk van de buitenste query wordt uitgevoerd, geeft een resultaatset terug en is dus niet afhankelijk van de buitenste query. Een gecorreleerde subquery kan niet onafhankelijk van de buitenste query worden uitgevoerd, omdat deze gebruik maakt van een of meer verwijzingen naar kolommen in de buitenste query om de resultaatreeks te bepalen die door de gecorreleerde subquery wordt teruggegeven. Ik hoop dat u nu het verschil tussen subqueries en gecorreleerde subqueries begrijpt, en hoe ze in uw T-SQL code kunnen worden gebruikt.

” Zie alle artikelen van columnist Gregory A. Larsen