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!