Transact-SQL: Utilizando Subconsultas no T-SQL

Quando nos deparamos com criação de códigos SQL mais complexos, que vão além das instruções básicas do Transact-SQL, podemos encontrar a necessidade de limitar a nossa consulta usando os resultados de outras instruções SELECT. Quando incorporamos uma instrução SELECT em uma instrução Transact-SQL pai, estas instruções SELECT incorporadas são conhecidas como subconsulta (subqueries), ou mesmo como uma subconsulta correlacionada. Neste artigo estaremos discutindo os diferentes aspectos de uma subconsulta.

O que é uma subconsulta?

Uma subconsulta é apenas uma instrução SELECT que está contida em outra instrução Transact-SQL. Uma subconsulta pode ser usada em qualquer lugar onde uma expressão pode ser usada. Muitas subconsultas retornam um único valor da coluna, porque eles são usados ​​em conjunto com os operadores de comparação (=, !=, <, <=, >, >=) ou com uma expressão. Quando uma subconsulta não é usada como uma expressão ou com um operador de comparação, ela pode retornar vários valores. Além disso, subconsultas podem até retornar várias colunas e valores quando elas são usadas ​​em uma cláusula FROM ou com a palavra-chave EXIST.

Uma subconsulta é fácil de ser identificada dentro de uma instrução Transact-SQL, pois será a instrução SELECT contida dentro de parênteses. Desde uma subconsulta está contida dentro de uma instrução Transact-SQL, a subconsulta é muitas vezes referida como uma consulta interna. Considerando que a instrução Transact-SQL que contém uma subconsulta é referida como consulta externa. Outra característica de uma subconsulta é que pode ser executada independentemente da consulta externa e será executada sem erros, além disso, pode retornar um conjunto de linhas com valores, ou um conjunto de linhas vazio.

Outra forma de subconsulta é correlacionando subconsultas. Mas a subconsulta correlacionada não pode ser executada independentemente da instrução Transact-SQL externa. Uma subconsulta correlacionada usa uma ou mais colunas da consulta externa para restringir os resultados retornados da subconsulta correlacionada. E este será o nosso foco referente a este artigo.

Aqui estão algumas outras coisas a considerar quando usamos subconsultas:

• ntext, tipos de dados de texto e imagem não estão autorizados a ser devolvidos por subconsultas
• A cláusula ORDER BY não pode ser utilizada em uma subconsulta, a menos que o operador TOP seja utilizado
• Uma subconsulta não pode ser atualizada

Dados de exemplo para exemplos de subconsulta

Para demonstrar como usar uma subconsulta, precisaremos de alguns dados de teste. Ao invés de criar dados neste momento, iremos utilizar o banco de dados AdventureWorks2012. Caso vocês tenham interesse em acompanhar e executar os exemplos aqui apresentados em seu ambiente, então vocês podem realizar o download do banco de dados AdventureWorks2012 pelo seguinte link: https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2012.bak

Exemplos de subconsultas que retornam um único valor

Como relatado anteriormente, subconsultas são usadas ​​em uma expressão ou para retornar um valor de um dos lados de um operador de comparação que são requeridos para retornar um único valor. Há muitos lugares diferentes em uma instrução Transact-SQL, onde uma subconsulta é necessária para retornar um único valor da coluna, como em uma lista de seleção, uma cláusula WHERE, etc. Nesta seção, iremos fornecer uma série de exemplos que demonstramos usando uma subconsulta como uma expressão ou com um operador de comparação para atender diferentes necessidades de negócios.

Subconsulta em lista de colunas

Uma subconsulta em uma lista de colunas é uma instrução SELECT que retorna um único valor da coluna que é colocado na lista de colunas de uma cláusula SELECT. Para demonstrarmos como realizar uma subconsulta na lista de seleção, vamos supor que temos um conjunto de resultados de uma instrução SELECT que deverá ter os seguintes requisitos de negócios, vejamos então abaixo os requisitos necessários:

• Retornar todos os registros que tenham um Sales.SalesOrderHeader OrderDate igual a "2007-02-19 00:00:00.000"
• Ordenando os registros retornados de acordo pelo SalesOrderID
• Retornar os registros em ordem crescente de acordo com o RowNumber
• O conjunto de resultados terá uma coluna chamada TotalOrders que precisa ser preenchida com o número total de pedidos que têm um OrderDate que é igual a "2007-02-19 00:00:00.000"

Agora que temos nossos requisitos, seguiremos com a criação do código que irá atender o nosso propósito de acordo com a Listagem 1.

Listagem 1. Subconsulta em linha de colunas

      SELECT ROW_NUMBER() OVER (ORDER BY SalesOrderID) RowNumber
            , (SELECT COUNT(*) 
               FROM [Sales].[SalesOrderHeader] 
               WHERE ModifiedDate = '2007-02-19 00:00:00.000') 
                           AS TotalOrders
            , *
      FROM [Sales].[SalesOrderHeader]
      WHERE OrderDate = '2007-02-19 00:00:00.000';

O resultado de nossa consulta pode ser visto de acordo com a Figura 1.

Figura 1. Representação de consulta realizada.

Nesta simples declaração Transact-SQL, vemos duas cláusulas SELECT diferentes. A subconsulta é a instrução SELECT que está incorporada no meio da declaração da Listagem 1 e tem parênteses em torno dele. Retiramos a declaração da subconsulta e a colocamos na Listagem 2, no caso de você querer testar para verificar se ele pode ser executado independentemente da instrução Transact-SQL completa.

Listagem 2. Consulta separada para teste.

SELECT COUNT(*) 
      FROM [Sales].[SalesOrderHeader]
      WHERE OrderDate = '2007-02-19 00:00:00.000'

Por ter essa subconsulta na lista de colunas, esta declaração Transact-SQL apresentada na Listagem 1, é capaz de contar o número de linhas SalesOrderHeader que tem um OrderDate de "2007-02-19 00:00:00.000" e a partir daí, retornar as informações juntamente com o detalhamento das informações sobre os registros Sales.SalesOrderHeader que têm o mesmo valor OrderDate.

Exemplo de subconsulta com cláusula WHERE

Há momentos em que queremos induzir uma condição utilizando a cláusula WHERE com base nos resultados de uma instrução SELECT. Quando temos uma instrução SELECT na cláusula WHERE, esta instrução SELECT é realmente uma subconsulta. Para demonstrarmos sua utilização, supúnhamos que vamos precisar apresentar os registros de Sales.SalesOrderDetail que contenham a compra de jerseys logotipo extra-grandes de manga longa. O código na Listagem 3 atende as nossas necessidades de exibição fazendo uso de uma subconsulta.

Listagem 3. Subquery com a clásula WHERE.

      SELECT * FROM [Sales].[SalesOrderDetail]
      WHERE ProductID = (SELECT ProductID 
                         FROM [Production].[Product]
                         WHERE Name = 'Long-Sleeve Logo Jersey, XL');

A subconsulta, a qual apresentamos na Listagem 3, está no lado direito da condição WHERE. Esta subconsulta identifica um ProductID para um determinado registro Production.Product, onde o nome do produto é “Long-Sleeve Logo Jersey, XL”. Esta subconsulta nos permite encontrar todos os registros Sales.SalesOrderDetail que tenham um ProductID que está associado com o nome do produto de "Long-Sleeve Logo Jersey, XL".

Usando uma subconsulta para controlar a cláusula TOP

O número de linhas retornadas utilizando a cláusula TOP pode ser controlada por uma expressão. O código apresentado pela Listagem 4 identifica o número de linhas Sales.SalesOrderDetail que devem ser retornadas com base em uma subconsulta presente na cláusula TOP.

Listagem 4. Subquery com cláusula TOP

SELECT TOP (SELECT TOP 1 OrderQty 
                  FROM [Sales].[SalesOrderDetail]
                  ORDER BY ModifiedDate) *  
      FROM [Sales].[SalesOrderDetail]
      WHERE ProductID = 709;

O código presente na Listagem 4 usa o valor OrderQty retornado da subconsulta para identificar o valor que será utilizado na cláusula TOP. Usando uma subconsulta para controlar o número de linhas que a cláusula TOP irá retornar, permitindo assim que você construa uma subconsulta que irá identificar dinamicamente o número de linhas retornadas de suas consultas em tempo de execução.

Exemplo de subconsulta na cláusula HAVING

A fim de demonstrar usando uma subconsulta na cláusula HAVING, suponha que tenhamos o seguinte requisito de negócio para trabalhar:

Requisito: Produzir um conjunto de resultados que contenha o Sales.SalesOrderHeader.OrderDate e o número de registros para cada data encontrada, onde o número de pedidos exceda o número de pedidos recebidos em '2006-05-01 '.

A fim de atendermos a essa exigência, temos então que desenvolver a consulta de acordo com a Listagem 5 que usa uma subconsulta na cláusula HAVING.

Listagem 5. Subqueries com a cláusula HAVING.

      SELECT count(*), OrderDate 
      FROM [Sales].[SalesOrderHeader]
      GROUP BY OrderDate
      HAVING count(*) >
             (SELECT count(*) 
              FROM [Sales].[SalesOrderHeader]
              WHERE OrderDate = '2006-05-01 00:00:00.000');

Utilizando de uma subconsulta em uma chamada de função

Para demonstrarmos a utilização de uma subconsulta em uma chamada de função, suponhamos que temos a obrigação de exibir o número de dias entre a OrderDate e o OrderDate máximo para cada registro Sales.SalesOrderHeader. O código apresentado na Listagem 6 atende a esse requisito.

Listagem 6. Subquery em chamada de funções.

SELECT OrderDate, 
    (SELECT MAX(OrderDate) FROM [Sales].[SalesOrderHeader]) AS OrderDateMax,
    DATEDIFF(DAY, OrderDate, (SELECT MAX(OrderDate) FROM [Sales].[SalesOrderHeader])) AS DiffDias
    FROM [Sales].[SalesOrderHeader];

O código apresentado na Listagem 6 tem duas subconsultas diferentes. Ambas as subconsultas devolvem o OrderDate máximo na tabela Sales.SalesOrderHeader. Mas a primeira subconsulta é usado para passar uma data para o segundo parâmetro da função DATEDIFF.

Exemplos de subconsultas que retornam vários valores

Todos os exemplos até aqui continham subqueries que retornavam apenas um único valor em uma única coluna, bem sabemos, que nem todas as subconsultas têm essa exigência. O próximo par de exemplos usará subqueries que retornam vários valores e/ou várias colunas.

Exemplo de subconsulta na cláusula FROM

Na cláusula FROM normalmente identificamos uma tabela ou um conjunto de tabelas com a qual nossa declaração Transact-SQL irá operar contra. Cada tabela fornece um conjunto de registros que a consulta irá usar para determinar o conjunto de resultados final para sua consulta. Uma subconsulta pode ser pensada como uma consulta que devolve um conjunto de registros, e, portanto, pode ser usado em um FROM da mesma forma como numa table. A consulta apresentada na Listagem 7 mostra como usamos uma subconsulta em uma cláusula FROM. Quando uma subconsulta é usada na cláusula do conjunto de resultados produzido a partir de outra subconsulta é comumente chamado de tabela derivada.

Listagem 7. Subquery com a cláusula FROM.

SELECT SalesOrderID 
      FROM (SELECT TOP 10 SalesOrderID 
            FROM [Sales].[SalesOrderDetail]
            WHERE ProductID = 707
            ORDER BY ModifiedDate DESC) AS Last10SalesOrders;

O código presente na Listagem 7 utiliza subconsulta na cláusula FROM para criar um alias de tabela, com o nome Last10SalesOrders. Nossa subconsulta então retornou os últimos 10 registros Sales.alesOrderDetail que continham um ProductID igual a 707.

De acordo com nosso código, presente na Listagem 7 é um exemplo muito trivial de como podemos usar uma subconsulta na cláusula FROM. Usando uma subconsulta em uma cláusula FROM que você poderia facilmente construir de formas mais complexas a partir da sintaxe que se junta aos resultados de uma subconsulta com outras tabelas, ou subconsultas adicionais, como fizemos de acordo com a Listagem 8.

Listagem 8. Fazendo um JOIN de uma tabela real com uma derivada.

SELECT DISTINCT OrderDate
      FROM (SELECT TOP 10 SalesOrderID 
            FROM [Sales].[SalesOrderDetail]
            WHERE ProductID = 707
            ORDER BY ModifiedDate DESC) AS Last10SalesOrders
      JOIN [Sales].[SalesOrderHeader] AS SalesOrderHeader
      ON Last10SalesOrders.SalesOrderID = SalesOrderHeader.SalesOrderID
      ORDER BY OrderDate

Na Listagem 8 tomamos a subconsulta/tabela derivada que criamos na Listagem 7 e juntamos com a tabela SalesOrderHeader. Ao fazer isso, poderíamos então determinar OrderDate’s distintos para as últimos 10 registros ordenados pelo ProductID = 707.

Exemplo de utilização de uma subconsulta com a palavra-chave IN

Outro lugar onde poderíamos escrever uma subconsulta que retornasse vários valores para uma coluna seria quando sua subconsulta produzisse um conjunto de registros que seria usada com a palavra-chave IN. O código presente na Listagem 9 demonstra como passar valores para a palavra-chave IN usando uma subconsulta.

Listagem 9. Utilizando a palavra-chave IN em subqueries.

SELECT * FROM [Sales].[SalesOrderDetail] 
      WHERE ProductID IN 
              (SELECT ProductID 
               FROM [Production].[Product]
               WHERE Name like '%XL%');

O código apresentado na Listagem 9 usa então uma subconsulta para retornar valores diferentes para os ProductID da tabela Production.Product que tem um nome que contém os caracteres "XL". Estes valores ProductID retornados da subconsulta são então utilizados na palavra-chave IN para restringir as linhas que serão retornadas da tabela de Sales.SalesOrderDetail.

Exemplo de utilização de uma subconsulta em um comunicado que modifica dados

Todos os nossos exemplos até o presente momento vêm demonstrando como usar uma subconsulta dentro de diferentes partes de uma instrução SELECT. Uma subconsulta pode ser também usada dentro de um INSERT, UPDATE ou um DELETE. O código que iremos apresentar na Listagem 10 mostra como usar uma subconsulta em uma instrução INSERT.

Listagem 10. Subquery com instrução Insert.

      DECLARE @SQTable TABLE (
      OrderID int,
      OrderDate datetime,
      TotalDue money,
      MaxOrderDate datetime);
       
      -- INSERT com SubQuery
      INSERT INTO @SQTable 
         SELECT SalesOrderID,
                OrderDate, 
                                       TotalDue, 
                                       (SELECT MAX(OrderDate) 
                                        FROM [Sales].[SalesOrderHeader]) 
         FROM [Sales].[SalesOrderHeader]
         WHERE CustomerID = 29614;
       
      -- Apresentando os registros
      SELECT * FROM @SQtable;

No código apresentado na Listagem 10 utilizamos uma subconsulta para calcular o valor a ser inserido na coluna MaxOrderDate. Este é apenas um exemplo de como usar uma subconsulta em uma instrução INSERT. Tenham em mente que uma subconsulta também pode ser usada dentro de um UPDATE e/ou DELETE. O que irá requerer uma lógica similar a apresentada.

Considerações sobre o desempenho entre Subqueries e JOIN’S

Se por acaso, você já tenha lido o documento "Subquery Fundamentals", produzido pela Microsoft (http://technet.microsoft.com/en-us/library/ms189575(v=sql.105).aspx), então vocês podem ter se deparado com a declaração sobre desempenho de instruções que contêm uma subconsulta, como o seguinte:

"No Transact-SQL, normalmente não há diferença de desempenho entre uma instrução que inclui uma subconsulta e uma versão semanticamente equivalente que não."

Para compararmos o desempenho de uma consulta usando subconsulta com uma consulta equivalente que não use uma subconsulta, iremos reescrever nossa subconsulta presente na Listagem 3 para usarmos com uma operação JOIN. A Listagem 11 nos mostra a consulta re-escrita que é equivalente a nossa consulta presente na Listagem 3.

Listagem 11. Subquery reescrita utilizando o JOIN.

SELECT SOD.* 
      FROM [Sales].[SalesOrderDetail] AS SOD
      INNER JOIN 
      [Production].[Product] AS P
      ON SOD.ProductID = P.ProductID
      WHERE P.Name = 'Long-Sleeve Logo Jersey, XL';

Para compararmos o desempenho da consulta na Listagem 3, que usamos uma subconsulta e a consulta presente na Listagem 11 que usamos um JOIN, estaremos executando duas consultas utilizando o código da Listagem 12.

Listagem 12. Código para teste de performance das Listagens 3 e 11.

SET STATISTICS IO ON;
      SET STATISTICS TIME ON;
      -- Query da Listagem 3
      SELECT * FROM [Sales].[SalesOrderDetail]
      WHERE ProductID = (SELECT ProductID 
                         FROM Production.Product
                         WHERE Name = 'Long-Sleeve Logo Jersey, XL'); 
       
      -- Query da Listagem 11
      SELECT SOD.* 
      FROM [Sales].[SalesOrderDetail] AS SOD
      INNER JOIN 
      [Production].[Product] AS P
      ON SOD.ProductID = P.ProductID
      WHERE P.Name = 'Long-Sleeve Logo Jersey, XL';

Depois de executarmos o código na Listagem 12, revimos as mensagens produzidas pelas declarações "set estatísticas". Ao analisar as estatísticas, descobrimos que as duas consultas tiveram 3.301 leituras lógicas em relação à tabela SalesOrderDetail e duas leituras lógicas em relação à tabela de Produtos, e cada um usou 31 ms de CPU. Além disso revimos o plano de execução que o SQL Server criado para ambas as perguntas. Achamos então que o SQL Server produziu o mesmo plano de execução para ambos. Assim, usando uma subconsulta ou uma consulta com JOIN para nossa situação no momento, produziu um desempenho equivalente, tal como documentado pela Microsoft.

Com isso então finalizamos este artigo, apresentando de forma prática que uma subconsulta é uma instrução SELECT incorporada com outra instrução Transact-SQL. A subconsulta pode ser executada independentemente da consulta externa e, portanto, sendo muitas vezes referida como uma consulta independente. Lembrem-se que quando temos uma subconsulta no lugar de uma expressão, ou ela será usada com um operador de comparação, que só poderá retornar uma única coluna e valor. Muitas vezes, uma subconsulta poderá ser reescrita usando JOINS. Subconsultas são poderosas ferramentas para nos ajudar a construir nossas instruções Transact-SQL mais complexas que nos levará a entender nossas necessidades de negócios.

Até a próxima!