Usare una sottoquery correlata in una dichiarazione T-SQL

Nell’articolo del mese scorso, ho discusso cosa e come usare una sottoquery in una dichiarazione T-SQL. Questo mese amplierò l’argomento discutendo le subquery correlate. Spiegherò cos’è una subquery correlata e mostrerò una serie di esempi diversi su come utilizzare una subquery in un’istruzione T-SQL.

Cos’è una subquery correlata?

Una subquery correlata è un’istruzione SELECT annidata all’interno di un’altra istruzione T-SQL, che contiene un riferimento a una o più colonne nella query esterna. Pertanto, si può dire che la sottoquery correlata dipende dalla query esterna. Questa è la differenza principale tra una subquery correlata e una semplice subquery. Una semplice subquery non dipende dalla query esterna, può essere eseguita indipendentemente dalla query esterna e restituisce un set di risultati. Una sottoquery correlata, poiché dipende dalla query esterna, restituirà un errore di sintassi se viene eseguita da sola.

Una sottoquery correlata verrà eseguita molte volte durante l’elaborazione della dichiarazione T-SQL che contiene la sottoquery correlata. La sottoquery correlata verrà eseguita una volta per ogni riga candidata selezionata dalla query esterna. Le colonne della query esterna, referenziate nella sottoquery correlata, sono sostituite con i valori della riga candidata prima di ogni esecuzione. A seconda dei risultati dell’esecuzione della sottoquery correlata, essa determina se la riga della query esterna viene restituita nel set di risultati finale.

Utilizzare una sottoquery correlata in una clausola WHERE

Supponiamo che si voglia un rapporto di tutti gli “OrderID” in cui il cliente non ha acquistato più del 10% della quantità media venduta per un dato prodotto. In questo modo potreste rivedere questi ordini, e possibilmente contattare i clienti, per aiutare a determinare se c’è una ragione per l’ordine di bassa quantità. Una sottoquery correlata in una clausola WHERE può aiutarvi a produrre questo rapporto. Ecco un’istruzione SELECT che produce l’elenco desiderato di “OrderID’s”:

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

La subquery correlata nel comando di cui sopra è contenuta nella parentesi che segue il segno maggiore di nella clausola WHERE di cui sopra. Qui potete vedere che questa sottoquery correlata contiene un riferimento a “OD.ProductID”. Questo riferimento confronta il “ProductID” della query esterna con il “ProductID” della query interna. Quando questa query viene eseguita, il motore SQL eseguirà la query interna, la sottoquery correlata, per ogni record “”. Questa query interna calcolerà la “Quantità” media per il particolare “ProductID” per la riga candidata che viene elaborata nella query esterna. Questa sottoquery correlata determina se la query interna restituisce un valore che soddisfa la condizione della clausola WHERE. In caso affermativo, la riga identificata dalla query esterna viene inserita nel record set che verrà restituito dall’istruzione T-SQL SELECT completa.

Il codice qui sotto è un altro esempio che utilizza una sottoquery correlata nella clausola WHERE per visualizzare i primi due clienti, in base all’importo in dollari associato ai loro ordini, per regione. Potreste voler eseguire una query come questa in modo da poter premiare questi clienti, dato che comprano di più per regione.

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

Qui potete vedere che la query interna è una subquery correlata perché fa riferimento a “OuterC”, che è l’alias della tabella “Northwind.DBO.Customer” nella query esterna. Questa query interna usa il valore “Region” per calcolare i primi due clienti per la regione associata alla riga che viene elaborata dalla query esterna. Se il “CustomerID” della query esterna è uno dei primi due clienti, allora il record viene inserito nell’insieme di record da restituire.

Sottoquery correlata nella clausola HAVING

Supponiamo che la vostra organizzazione voglia eseguire un programma di incentivi annuale per aumentare le entrate. Pertanto, pubblicizzano ai vostri clienti che se ogni ordine che effettuano, durante l’anno, è superiore a 750 dollari, fornirete loro uno sconto alla fine dell’anno al tasso di 75 dollari per ogni ordine che effettuano. Qui sotto c’è un esempio di come calcolare l’importo dello sconto. Questo esempio usa una sottoquery correlata nella clausola HAVING per identificare i clienti che si qualificano per ricevere lo sconto. Ecco il mio codice per questa 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 ) 

Rivedendo questa query, potete vedere che sto usando una query correlata nella clausola HAVING per calcolare l’importo totale dell’ordine di ogni cliente. Uso il “CustomerID” dalla query esterna e l’anno dell’ordine “Datepart(yy,OrderDate)”, per aiutare a identificare i record dell’ordine associati a ciascun cliente, che sono stati effettuati nell’anno ‘1998’. Per questi record associati sto calcolando l’importo totale dell’ordine, per ogni ordine, sommando tutti i record “”, utilizzando la seguente formula: sum(UnitPrice * Quantity * (1-Discount)). Se ogni ordine di un cliente, per l’anno 1998, ha un importo totale in dollari superiore a 750, allora calcolo l’importo del rimborso nella query esterna utilizzando questa formula “Count(*)*75”.

Il motore di query di SQL Server eseguirà la sottoquery interna correlata nella clausola HAVING solo per quei record di clienti identificati nella query esterna, o fondamentalmente solo quei clienti che hanno fatto ordini nel “1998”.

Eseguire una dichiarazione di aggiornamento usando una sottoquery correlata

Una sottoquery correlata può anche essere usata in una dichiarazione di aggiornamento. Ecco un esempio:

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

Ecco il risultato che ottengo quando eseguo questa query sulla mia macchina:

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

Nella mia query di cui sopra, ho usato la sottoquery correlata per aggiornare la colonna A nella tabella A con la somma della colonna B nella tabella B per le righe che hanno lo stesso valore nella colonna A della riga da aggiornare.

Conclusione

Riassumo. Una subquery e una subquery correlata sono query SELECT codificate all’interno di un’altra query, nota come query esterna. La subquery correlata e la subquery contribuiscono a determinare il risultato del set di risultati restituito dalla query completa. Una subquery, quando viene eseguita indipendentemente dalla query esterna, restituisce un set di risultati, e quindi non dipende dalla query esterna. Invece, una sottoquery correlata non può essere eseguita indipendentemente dalla query esterna perché utilizza uno o più riferimenti alle colonne della query esterna per determinare il set di risultati restituito dalla sottoquery correlata. Spero che ora abbiate capito la differenza tra subquery e subquery correlate, e come possono essere usate nel vostro codice T-SQL.

” Vedi tutti gli articoli del giornalista Gregory A. Larsen