T-SQL e a utiliza��o de Subconsultas correlacionadas

Ao precisarmos utilizar instruç�es mais complexas de SQL, o que precisamos para chegarmos ao nosso resultado? Neste artigo, trataremos da utilizaç�o de subconsultas correlacionadas que possuem algumas particularidades com relaç�o a sua utilizaç�o junto ao T-SQL. Aprenderemos ent�o como us�-las de forma segura e veremos quando n�o utiliz�-las tamb�m.

O que � uma subconsulta correlacionada?

Com relaç�o a subconsultas existem dois tipos que podem ser vistos da seguinte forma: as que s�o correlacionadas e as n�o correlacionadas. As subconsultas correlacionadas s�o consultas que dependem e fazem refer�ncias as colunas de consultas externas a qual est�o contidas (este tipo de subconsulta � o que abordaremos neste artigo); j� com relaç�o a consultas n�o correlacionadas s�o consultas independentes das consultas externas nas quais est�o contidas.

A subconsulta normal � apenas uma instruç�o SELECT dentro de uma outra instruç�o Transact-SQL, onde a subconsulta pode retornar resultados se executada independentemente da consulta externa. A subconsulta correlacionada difere da anterior neste ponto, pois ela � uma forma de subconsulta que n�o pode ser executada independentemente da consulta externa, pelo fato de conter uma ou mais colunas da consulta externa. A subconsulta correlacionada, como uma subconsulta do tipo normal, � muitas vezes referida como a consulta interna. Se a subconsulta correlacionada (consulta interna) for executada, independentemente da consulta externa, ele retornar� um erro. Como a execuç�o da consulta interna depende dos valores da consulta externa, ela � chamada de subconsulta correlacionada.

Uma subconsulta correlacionada pode ser executada v�rias vezes no decorrer de uma consulta. Ela vai ser executada uma vez para cada linha candidata escolhida na consulta externa. Os valores de cada linha da coluna candidata ser�o usados para fornecer valores para as colunas da consulta externa no interior de cada execuç�o da subconsulta correlacionada. Os resultados finais de uma declaraç�o que cont�m uma subconsulta correlacionada ser�o baseados nos resultados de cada execuç�o da subconsulta correlacionada. Iremos ent�o apresentar alguns exemplos no decorrer deste artigo para apresentar melhor esse tipo de consulta.

Dados de exemplo para exemplos subconsulta correlacionada

Para demonstrarmos como podemos utilizar subconsultas correlacionadas, precisaremos de alguns dados para teste. Ao inv�s de criarmos nossos dados para testes, todos os exemplos utilizados aqui utilizar�o a base de dados AdventureWorks2012_Database. Caso voc�s queiram acompanhar e executar nossos exemplos em seu ambiente, ser� necess�rio obter a base de testes atrav�s do link https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2012.bak

Subconsulta correlacionada na cl�usula WHERE

Para demonstrarmos o uso de uma subconsulta correlacionada em uma cl�usula WHERE analisaremos da seguinte forma: digamos que nosso objetivo inicial seja identificar os CustomerID que compraram mais de 70 itens em uma �nica ordem (�nico pedido). Para cumprirmos este requisito podemos executar o c�digo de acordo com o apresentado na Listagem 1.

Listagem 1. Subconsulta correlacionada com cl�usula Where.

SELECT CustomerID FROM Sales.SalesOrderHeader OH
  WHERE (SELECT COUNT(*) FROM Sales.SalesOrderDetail 
           WHERE SalesOrderID = OH.SalesOrderID) > 70;

Quando executarmos o c�digo apresentado na Listagem 1, receberemos a sa�da de acordo com a representada pela Figura 1.

Figura 1. Resultados retornados ao executar o c�digo na Listagem 1 usando cl�usula Where.

Se voc�s prestarem atenç�o no c�digo da Listagem 1, ir�o ver que usamos nossa cl�usula WHERE usando uma subconsulta correlacionada. A subconsulta � o c�digo que est� presente entre par�nteses. Esta mesma consulta tornou-se independente, de acordo com a Listagem 2, para a realizaç�o de um teste onde vermos o resultado que ser� retornado.

Listagem 2. C�digo retirado da subconsulta presente na Listagem 1.

SELECT COUNT(*) FROM Sales.SalesOrderDetail
           WHERE SalesOrderID = OH.SalesOrderID

Se executarmos agora este c�digo separadamente, veremos como resultado na tela uma mensagem de erro sendo exibida, conforme a seguir:

Msg 4104, Level 16, State 1, Line 2
  The multi-part identifier "OH.SalesOrderID" could not be bound.

Recebemos esta mensagem devido a nossa subconsulta correlacionada, pois ela contem uma refer�ncia � coluna OH.SalesOrderID que � uma coluna presente na consulta externa. Uma vez que todas as subconsultas correlacionadas s�o referenciadas a uma ou mais colunas da consulta externa, n�o podemos execut�-las independentemente da consulta externa com a qual ela est� associada. O fato de n�o podermos executar a subconsulta independentemente de toda a instruç�o Transact-SQL � o que distingue uma subconsulta correlacionada sobre uma subconsulta normal.

Outro exemplo que poder�amos apresentar aqui seria com relaç�o a esta consulta que recupera uma inst�ncia do nome e sobrenome de cada funcion�rio para o qual o b�nus na tabela de vendedores � de 5000 e para o qual os n�meros de identificaç�o dos empregados sejam correspondentes nas tabelas de Employee e vendedor. Podemos ver a consulta sendo realizada de acordo com o exposto pela Listagem 3.

Listagem 3. Consulta de funcion�rios com b�nus de 5000.

 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

Da mesma forma que o nosso primeiro exemplo, a subconsulta apresentada na Listagem 3 n�o pode ser avaliada independentemente da consulta externa. Ela precisa de um valor para o Employee.BusinessEntityID, mas esse valor � alterado quando o SQL Server examina diferentes linhas em Employee. E � exatamente como esta consulta � avaliada: o SQL Server considera cada linha da tabela Employee para inclus�o nos resultados, substituindo o valor em cada linha na consulta interna.

As subconsultas correlacionadas tamb�m podem incluir funç�es com valor de tabela na cl�usula FROM, fazendo assim refer�ncia a colunas de uma tabela na consulta externa como um argumento da funç�o com valor de tabela. Neste caso, para cada linha da consulta externa, a funç�o com valor de tabela � avaliada de acordo com a sua subconsulta.

Os exemplos que apresentamos aqui s�o exemplos muito triviais de utilizaç�o de uma subconsulta correlacionada em uma cl�usula WHERE. Esperamos que tenha sido poss�vel o entendimento com relaç�o a diferença entre uma subconsulta normal e uma subconsulta correlacionada. Normalmente uma subconsulta correlacionada pode ser consideravelmente mais complexa. Al�m disso, tenham em mente que podem haver outros m�todos para atender �s suas necessidades de neg�cios sem que seja necess�rio a utilizaç�o de uma subconsulta correlacionada.

Como pudemos perceber aqui, uma subconsulta correlacionada � muito semelhante a uma subconsulta normal, mas que n�o podemos simplesmente execut�-la independentemente da consulta externa.

Exemplo de subconsulta correlacionada na cl�usula HAVING

H� momentos em que voc�s podem querer restringir uma cl�usula HAVING por valores diferentes de uma consulta externa. Isto �, quando voc� pode usar uma subconsulta correlacionada na sua cl�usula HAVING. Suponha que tenhamos que escrever uma consulta que ir� calcular os montantes de descontos para aqueles clientes que t�m comprado mais de $ 150.000 no valor dos produtos antes de impostos no ano de 2008. O c�digo presente na Listagem 4 calcula o montante de descontos para os clientes avaliados usando uma subconsulta correlacionada com a cl�usula HAVING.

Listagem 4. Subconsulta correlacionada coma utilizaç�o da cl�usula HAVING.

SELECT Outer_H.[CustomerID] 
       , SUM(Outer_H.[SubTotal]) AS TotalPurchase
                   , SUM(Outer_H.[SubTotal]) * .10 AS Rebate
  FROM [Sales].[SalesOrderHeader] AS Outer_H 
  WHERE YEAR(Outer_H.[OrderDate]) = '2008'
  GROUP BY Outer_H.[CustomerID]
  HAVING (SELECT SUM(Inner_H.[SubTotal]) FROM [Sales].[SalesOrderHeader] AS Inner_H
          WHERE Inner_H.[CustomerID] = Outer_H.[CustomerID]
                                 AND YEAR(Inner_H.[OrderDate]) = '2008') > 150000
  ORDER BY Rebate DESC;

Quando executarmos o c�digo da Listagem 4 receberemos os resultados como os apresentados pela Figura 2.

Figura 2. Resultados da execuç�o utilizando a cl�usula HAVING.

O c�digo da subconsulta correlacionada presente na Listagem 4 usa o Cliente da cl�usula GROUP BY na consulta externa dentro da subconsulta correlacionada. A subconsulta correlacionada ser� executada uma vez para cada linha retornada da cl�usula GROUP BY. Isso permite que a cl�usula HAVING realize o c�lculo da quantidade total de produtos vendidos para cada Cliente da consulta externa pela soma dos valores da coluna de subtotal referente a cada registro SalesOrderHeader, onde o registro est� associado ao Cliente da consulta externa. A instruç�o Transact-SQL presente na Listagem 4 s� nos retorna uma linha onde o Cliente tenha adquirido um valor acima de $150.000 no valor do produto.

Criaç�o de subconsultas correlacionadas com Alias (Apelidos)

As subconsultas correlacionadas podem ser utilizadas em operaç�es como selecionar dados de uma tabela referenciada na consulta externa. Neste caso, uma tabela de alias (tamb�m chamado um nome de correlaç�o) deve ser usado para especificar sem ambiguidades qual a tabela de refer�ncia a ser usada. Por exemplo, podemos usar uma subconsulta correlacionada para encontrar os produtos que s�o fornecidos por mais de um fornecedor. Aliases s�o obrigat�rios para que possamos distinguir os dois pap�is diferentes em que a tabela ProductVendor aparece. Veremos isso de acordo com a Listagem 5.

Listagem 5. Utilizando Alias nas subconsultas correlacionadas.

SELECT DISTINCT pv1.ProductID, pv1.BusinessEntityID
  FROM Purchasing.ProductVendor pv1
  WHERE ProductID IN
      (SELECT pv2.ProductID
       FROM Purchasing.ProductVendor pv2
       WHERE pv1.BusinessEntityID <> pv2.BusinessEntityID)
  ORDER  BY pv1.BusinessEntityID

Esta mesma consulta que apresentamos na Listagem 5 � equivalente a utilizaç�o de um SELF JOIN, como mostra a Listagem 6 a seguir.

Listagem 6. Utilizaç�o de SELF JOIN para mostrar a equival�ncia entre as consultas.

SELECT DISTINCT pv1.ProductID, pv1.BusinessEntityID
  FROM Purchasing.ProductVendor pv1
  INNER JOIN Purchasing.ProductVendor pv2
  ON pv1.ProductID = pv2.ProductID
      AND pv1.BusinessEntityID <> pv2.BusinessEntityID
  ORDER BY pv1.BusinessEntityID

Exemplo de instruç�o de Update que cont�m uma subconsulta correlacionada

As subconsultas correlacionadas podem ser utilizadas n�o s� para retornar um conjunto de resultados usando uma instruç�o SELECT. Este tipo de consulta pode ser utilizado tamb�m quando precisamos realizar a atualizaç�o dos dados em uma determinada tabela do SQL Server. Para demonstrarmos isso, vamos primeiramente gerar alguns dados de teste em uma tabela tempdb, usando o c�digo disposto de acordo com a Listagem 7.

Listagem 7. C�digo para criaç�o e preenchimento da tabela de testes.

USE tempdb;
  GO
  SET NOCOUNT ON;
  CREATE TABLE CarInventory (
  ID int identity, 
  CarName varchar(50),
  VIN varchar(50),
  StickerPrice decimal (7,2),
  InvoicePrice decimal (7,2));
  GO
  INSERT INTO CarInventory VALUES 
  ('Explorer','EXP2014123456A', 46198.45, 38201.87),
  ('Explorer','EXP2014123492A', 47329.50, 48201.37),
  ('Explorer','EXP2014123492W', 46330.50, 48236.67),
  ('Explorer','EXP2014123493A', 47129.98, 38201.87),                               
  ('Grand Cherokee','JGC20141234345X', 41678.45, 36201.86),
  ('Grand Cherokee','JGC20141234556W', 44518.31, 36201.86),
  ('Grand Cherokee','JGC20141234556Y', 47548.61, 38251.86),
  ('Pathfinder','NPF2014987365A', 32587.73, 28917.10),
  ('Pathfinder','NPF2014239657B', 33577.54, 28917.10),
  ('Pathfinder','NPF2014234357G', 33937.54, 28767.10),
   ('Pathfinder','NPF2014098587C', 35876.12, 28917.10),
  ('Pathfinder','NPF2014098587D', 34696.12, 59317.60),
  ('Tahoe','TAH201409674A', 52001.08, 46000.01),
  ('Tahoe','TAH201409674D', 57231.48, 48500.50);

O c�digo presente na Listagem 7 cria ent�o uma tabela CarInventory e, em seguida, preenchemos ela com oito linhas que representam os carros atualmente no invent�rio. Neste exemplo, digamos que, periodicamente o gerente de vendas gosta de ver seu InvoicePriceRatio executando a consulta apresentada pela Listagem 8.

Listagem 8. Consulta a tabela InvoicePriceRatio.

SELECT CarName, InvoicePrice/StickerPrice*100.0 AS InvoicePriceRatio 
  FROM CarInventory;

Ao analisar esta consulta, o gerente percebe que h� um n�mero de carros similares com os mesmos valores InvoicePrice que tem valores InvoicePriceRatio diferentes. Para maximizar a sua receita referente ao preço de etiqueta dos produtos, ele solicita ao seu suporte em banco de dados para que escreva uma consulta que ir� realizar a atualizaç�o do StickerPrice de todos os seus carros para que cada carro com o mesmo valor CarName que tenham o mesmo InvoicePriceRatio. Ele quer dessa forma, que o suporte em banco de dados defina o StickerPrice para o mesmo valor que o preço m�ximo de etiqueta que CarName. Desta forma, todos os carros com o mesmo valor CarName ter�o o mesmo valor StickerPrice. Para realizarmos essa atualizaç�o da tabela CarInventory, o respons�vel por preparar essa consulta executar� a instruç�o Transact-SQL na Listagem 9, que cont�m uma subconsulta correlacionada.

Listagem 9. Subconsulta correlacionada para atualizaç�o do valor de CarInventory.

UPDATE CarInventory  
  SET StickerPrice = (SELECT MAX(StickerPrice) 
                      FROM CarInventory Inner_CI 
                      WHERE Inner_CI.CarName = Outer_CI.CarName)  
  FROM CarInventory Outer_CI;

O c�digo presente na Listagem 9 usa ent�o o CarName da consulta externa na subconsulta correlacionada para identificar o StickerPrice m�ximo para cada CarName que seja �nico na base. Este valor m�ximo de StickerPrice que � encontrado na subconsulta correlacionada � ent�o utilizado para atualizar o valor StickerPrice para cada registro CarInventory que tenha o mesmo CarName.

Consideraç�es sobre o desempenho com subconsultas correlacionadas

H� algumas consideraç�es com relaç�o ao desempenho que voc�s devem estar cientes de quando se escreve instruç�es Transact-SQL que contenham subconsultas correlatas. O desempenho n�o � ruim quando a consulta externa cont�m um pequeno n�mero de linhas. Mas quando a consulta externa cont�m um grande n�mero de linhas, ele n�o escala bem do ponto de vista do desempenho. Isso ocorre porque a subconsulta correlacionada precisa ser executada para cada linha candidata na consulta externa. Portanto, quando a consulta externa contiver mais e mais linhas candidatas, uma subconsulta correlacionada tem de ser executado v�rias vezes, e, portanto, a instruç�o Transact-SQL vai demorar mais tempo para ser executada. Se voc� ver que o desempenho de suas instruç�es de subconsulta correlacionadas Transact-SQL n�o estejam a cumprir suas necessidades, ent�o voc� devem olhar buscar por soluç�es alternativas, como por exemplo, consultas que usam operaç�es INNER ou OUTER JOIN, ou aquelas que retornam um n�mero menor de linhas candidatas da consulta externa.

Chegamos ent�o ao fim de mais um artigo referente a utilizaç�o de itens mais avançados com T-SQL, onde vimos que uma subconsulta correlacionada � uma consulta interna, que inclui uma ou mais colunas da consulta externa. Esta, por sua vez, � executada uma vez para cada linha candidata da consulta externa. Devido a uma subconsulta correlacionada conter uma coluna da consulta externa, esta n�o pode ser executada independentemente da consulta externa, caso contr�rio, gerar� um erro. Subconsultas correlacionadas t�m o seu lugar, apesar de n�o ser bem dimensionadas a partir de uma perspectiva de desempenho quando h� um grande n�mero de linhas candidatas identificadas na consulta externa.

At� a pr�xima!