Usando uma Subconsulta Correlata em uma Declaração T-SQL

No artigo do mês passado, discuti o que e como usar uma subconsulta em uma declaração T-SQL. Este mês vou expandir este assunto, discutindo subconsultas correlatas. Vou explicar o que é uma subconsulta correlata, e mostrar uma série de exemplos diferentes sobre como usar uma subconsulta em uma declaração T-SQL.

O que é uma subconsulta correlata?

Uma subconsulta correlata é uma declaração SELECT aninhada dentro de outra declaração T-SQL, que contém uma referência a uma ou mais colunas na consulta externa. Portanto, pode-se dizer que a subconsulta correlata é dependente da consulta externa. Esta é a principal diferença entre uma subconsulta correlata e apenas uma subconsulta simples. Uma subconsulta simples não é dependente da consulta externa, pode ser executada independentemente da consulta externa e retornará um conjunto de resultados. Uma subconsulta correlacionada, já que depende da consulta externa, retornará um erro de sintaxe se ela for executada por si mesma.

Uma subconsulta correlacionada será executada muitas vezes durante o processamento da instrução T-SQL que contém a subconsulta correlacionada. A subconsulta correlacionada será executada uma vez para cada linha candidata selecionada pela consulta externa. As colunas da consulta externa, referenciadas na subconsulta correlata, são substituídas por valores da linha do candidato antes de cada execução. Dependendo dos resultados da execução da subconsulta correlata, determinará se a linha da consulta externa é retornada no conjunto de resultados finais.

Usando uma subconsulta relacionada em uma Cláusula WHERE

Se quiser um relatório de todos os “OrderID’s” onde o cliente não comprou mais de 10% da quantidade média vendida para um determinado produto. Desta forma poderá rever estas encomendas, e possivelmente contactar os clientes, para ajudar a determinar se existe uma razão para a baixa quantidade encomendada. Uma subconsulta correlacionada numa cláusula WHERE pode ajudá-lo a produzir este relatório. Aqui está uma declaração SELECT que produz a lista desejada de “OrderID’s”:

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

A subconsulta correlata no comando acima está contida dentro do parêntese que segue o sinal maior do que o da cláusula WHERE acima. Aqui você pode ver que esta subconsulta correlata contém uma referência a “OD.ProductID”. Esta referência compara a consulta externa “ProductID” da consulta externa com a consulta interna “ProductID” da consulta interna. Quando esta consulta é executada, o motor SQL irá executar a consulta interna, a subconsulta correlacionada, para cada registo “”. Esta consulta interna calculará a “Quantidade” média para o “ProductID” específico para a linha candidata que está sendo processada na consulta externa. Esta subconsulta correlacionada determina se a consulta interna retorna um valor que atenda a condição da cláusula WHERE. Se o fizer, a linha identificada pela consulta externa é colocada no conjunto de registos que será devolvido a partir da declaração completa T-SQL SELECT.

O código abaixo é outro exemplo que utiliza uma subconsulta correlacionada na cláusula WHERE para exibir os dois principais clientes, com base no valor em dólares associado às suas encomendas, por região. Você pode querer executar uma consulta como esta para que você possa recompensar esses clientes, já que eles compram mais por região.

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

Aqui você pode ver que a consulta interna é uma subconsulta correlacionada porque ela se refere a “OuterC”, que é a tabela alias para a tabela “Northwind.DBO.Customer” na consulta externa. Esta consulta interna utiliza o valor “Region” para calcular os dois principais clientes para a região associada à linha que está a ser processada a partir da consulta externa. Se o “CustomerID” da consulta externa for um dos dois principais clientes, então o registo é colocado no conjunto de registos a ser devolvido.

Subconsulta relacionada com o Cliente na cláusula HAVING

Diga que a sua organização quer executar um programa de incentivos de um ano para aumentar as receitas. Portanto, eles anunciam aos seus clientes que se cada encomenda que fizerem, durante o ano, for superior a $750, você lhes dará um desconto no final do ano à taxa de $75 por encomenda que fizerem. Abaixo está um exemplo de como calcular o montante do desconto. Este exemplo utiliza uma subconsulta correlata na cláusula HAVING para identificar os clientes que se qualificam para receber o bônus. Aqui está o meu código para esta consulta:

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 ) 

Ao rever esta consulta, você pode ver que estou usando uma consulta correlata na cláusula HAVING para calcular o valor total do pedido para cada pedido de cliente. Eu uso o “CustomerID” da consulta externa e o ano da encomenda “Datepart(yy,OrderDate)”, para ajudar a identificar os registos da encomenda associados a cada cliente, que foram colocados no ano ‘1998’. Para estes registos associados estou a calcular o montante total da encomenda, para cada encomenda, através da soma de todos os registos “”, utilizando a seguinte fórmula: soma(Preço Unitário * Quantidade * (1-Desconto)). Se cada pedido de um cliente, para o ano de 1998, tiver um montante total em dólares superior a 750, eu então calculo o montante do bônus na consulta externa usando esta fórmula “Count(*)*75 “.

O motor de consulta do servidorSQL só executará a subconsulta interna correlacionada na cláusula HAVING para aqueles registos de clientes identificados na consulta externa, ou basicamente só aqueles clientes que fizeram encomendas em “1998”.

Executar uma declaração de actualização usando uma subconsulta correlacionada

Uma subconsulta correlacionada pode até ser usada numa declaração de actualização. Aqui está um exemplo:

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

Aqui está o conjunto de resultados que recebo quando executo esta consulta na minha máquina:

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

Na minha consulta acima, usei a subconsulta correlacionada para atualizar a coluna A na tabela A com a soma da coluna B na tabela B para linhas que têm o mesmo valor na coluna A como a linha sendo atualizada.

Conclusão

Deixe me resumir. Uma subconsulta e uma subconsulta correlata são consultas SELECT codificadas dentro de outra consulta, conhecida como a consulta externa. A subconsulta correlacionada e a subconsulta ajudam a determinar o resultado do conjunto de resultados retornados pela consulta completa. Uma subconsulta, quando executada independentemente da consulta externa, retornará um conjunto de resultados e, portanto, não é dependente da consulta externa. Onde como, uma subconsulta correlacionada não pode ser executada independentemente da consulta externa porque utiliza uma ou mais referências a colunas na consulta externa para determinar o conjunto de resultados retornado da subconsulta correlacionada. Espero que agora você entenda o diferente das subconsultas e subconsultas correlacionadas, e como elas podem ser usadas no seu código T-SQL.

” Veja Todos os Artigos do Colunista Gregory A. Larsen