T-SQL Subqueries: Onde e quando utilizar
Neste artigo trataremos da utilização de subqueries em nossas pesquisas que realizamos diariamente em nossas bases de dados. Uma subconsulta é uma instrução SELECT que está condicionada dentro de outra instrução SQL.
Uma subconsulta (ou mais conhecida, subquery) é uma instrução SELECT que está condicionada dentro de outra instrução SQL. Como resultado desta operação, podemos fazer uso de subconsultas para criarmos consultas que seriam difíceis ou impossíveis de serem feitas utilizando outras maneiras. Desde que saibamos codificar nossas instruções SELECT, saberemos como codificar uma subconsulta, já que ela é apenas uma instrução SQL no interior de outra instrução SQL. O que precisamos saber nesse momento para utilizarmos as subqueries é onde e quando devemos utilizá-las. Vamos então aprender sobre algumas das especificidades de utilizarmos subconsultas ao longo deste artigo.
Utilizando subqueries
A utilização de subqueries é bastante simples, pois o que precisamos realmente é ter cuidado aonde devemos utilizá-las. Neste caso, afirmamos que nossas subqueries podem ser codificadas ou mesmo introduzidas em cláusulas WHERE, HAVING, FROM, ou mesmo SELECT de uma outra instrução SELECT. Essa instrução, por exemplo, pode então usar uma subconsulta na condição de pesquisa de uma cláusula WHERE. Quando ela é utilizada em uma condição de pesquisa, a subconsulta pode ser referida como sendo uma condição de pesquisa ou uma subconsulta predicado. Vejamos um pequeno exemplo de sua utilização, de acordo com a Listagem 1.Listagem 1. Exemplo de uso de subconsultas na cláusula SELECT.
SELECT DISTINCT NomeFornecedor,
(SELECT MAX(DataFatura) FROM Faturas
WHERE Faturas.Id_fornecedor = Fornecedores.Id_fornecedor) AS UltimaFatura
FROM Fornecedores
ORDER BY UltimaFatura DESC;
No exemplo que apresentamos, mostramos como usar subconsultas(subqueries) na cláusula SELECT. Como podemos observar, nós podemos usar uma subconsulta no lugar de uma especificação de coluna, por isso, uma subconsulta deve retornar um único valor. Na maioria dos casos, as subconsultas que usamos na cláusula SELECT serão subconsultas correlacionadas, estas são tipicamente ineficientes. Como mostrado pela Listagem 1, o exemplo é para calcular a data máxima das faturas para cada fornecedor na tabela de fornecedores, neste caso, ele se refere à coluna Id_fornecedor presente na tabela de Faturas numa consulta externa.
É preciso termos cuidado com a utilização de subqueries em cláusulas do tipo SELECT, pois elas, às vezes, são difíceis de ler. Na maioria dos casos, porém, podemos substituir uma subconsulta por um JOIN para facilitar mais as coisas. A consulta mostrada primeiramente pela Listagem 1 poderia ser reapresentada como mostrado pelo código apresentado pela Listagem 2. Esta consulta então une as tabelas de Fornecedores e Faturas, agrupando as linhas por NomeFornecedor e, em seguida, usa a função MAX para calcular a data máxima da fatura para cada fornecedor. Como podemos observar, essa consulta é muito mais fácil de ser lida do que aquela com a subconsulta, além de que, esta é executada muito mais rapidamente, já que ela faz a busca uma única vez, enquanto que utilizando subconsultas, é feita uma varredura para cada linha em execução consumindo mais recursos da aplicação.
Listagem 2. Consulta de faturas utilizando JOIN.
SELECT NomeFornecedor, MAX(DataFatura) AS UltimaFatura
FROM
Fornecedores LEFT JOIN Faturas ON Faturas.Id_fornecedor = Fornecedores.Id_fornecedor
GROUP BY NomeFornecedor
ORDER BY UltimaFatura DESC;
Quando uma subconsulta nos retorna um único valor, podemos usá-la em qualquer lugar no qual poderíamos usar normalmente uma expressão. No entanto, uma subconsulta também pode retornar um conjunto de resultados de coluna única com duas ou mais linhas. Nesse caso, ela pode ser usada no lugar de uma lista de valores, tais como a lista para um operador IN. Além disso, se uma subconsulta é codificada dentro de uma cláusula FROM, esta pode retornar um conjunto de resultados com duas ou mais colunas. Iremos aprender sobre todos esses tipos diferentes de subqueries neste artigo.
Além disso, também podemos codificar uma subconsulta dentro de outra subconsulta. Nesse caso, as subconsultas são ditas como sendo subconsultas aninhadas, mas elas podem ser de difícil leitura e também resultar em um baixo desempenho, por isso, devemos usá-las somente quando necessário. Existem, no entanto, quatro maneiras de introduzirmos uma subconsulta em uma instrução do tipo SELECT, que são:
- Através de uma cláusula WHERE como sendo uma condição de pesquisa;
- Em uma cláusula HAVING como condição de pesquisa;
- Na cláusula FROM como uma especificação de tabela;
- Na cláusula SELECT como uma especificação de coluna.
O que podemos dizer sobre as subqueries é que elas podem ser usadas para a realização de pesquisas compostas, onde uma informação da qual estamos precisando depende de uma ou mais informações vindas com base em outras respostas. Para esse tipo de consulta, duas ou mais consultas precisam ser executadas, onde, a primeira consulta que irá localizar a informação desejada, mas que é na segunda consulta que irá encontrar a informação que será utilizada para comparação, e dessa forma, realizarmos o cálculo do resultado desta comparação, nos trazendo assim a informação requerida. A utilização de subconsultas é uma abordagem que nos fornece grande capacidade de incorporarmos consultas umas nas outras. Vejamos como é a sintaxe de acordo com a Listagem 3.
Listagem 3. Sintaxe da subquery padrão.
SELECT <columnA, columnB, ..., columnN>
FROM <table>
WHERE expression operator
(SELECT <columnA, columnB, ..., columnN>
FROM <table>
WHERE <condition>
)
Como podemos perceber, a sintaxe apresentada no código anterior é a estrutura base em mais de uma consulta, onde uma se torna um termo comparativo, enquanto que a outra nos traz a informação a ser utilizada na comparação. Temos então algumas regras que precisamos entender para o correto funcionamento dessas consultas:
- Toda subquery deve ser colocada entre parênteses;
- As subqueries precisam ser colocadas do lado direito do operador de comparação;
- As subqueries não podem conter cláusulas de ORDERBY;
- As subqueries podem conter mais de uma subquery.
Existem três tipos de subqueries:
- A mais simples é a subconsulta de uma única linha, também conhecida como subquery single-row;
- A subquery de várias linhas, conhecida como subquery multiple-row;
- A subquery de múltiplas colunas, conhecida como subquery multiple columns.
A seguir apresentaremos alguns exemplos de utilização dessas subquerys e de alguns métodos que também são empregados em outras linguagens de banco de dados, como podemos citar o PL/SQL, da Java.
Para os exemplos utilizaremos a base de dados de testes oferecida pela Microsoft, que é a AdventureWorks2012_database e o SQL SERVER 2014 para realização das consultas. Os links para download estarão presentes no fim do artigo caso tenham interesse em ter esse material em mãos para acompanhar e aprimorar os seus conhecimentos.
Subquery de linha única (subquery single-row)
Subconsultas de linha única podem nos retornar zero ou uma linha para a instrução SQL exterior. Podemos então aplicar uma subconsulta em uma cláusula WHERE, ou uma cláusula HAVING, ou uma cláusula FROM presente numa instrução SELECT. Neste tipo de consulta podemos utilizar os operadores necessários para retornar as informações solicitadas em uma única linha. Como por exemplo, poderíamos fazer a seguinte declaração para trazermos as informações referentes a um determinado produto, como mostra o código presente na Listagem 4.
Listagem 4. Consulta de linha única com a clausula WHERE.
SELECT
[ProductID]
,[Name]
,[ProductNumber]
,[Color]
FROM [AdventureWorks2012].[Production].[Product]
WHERE [ProductID] = (
SELECT [ProductID]
FROM [AdventureWorks2012].[Production].[Product]
WHERE [Name] = 'Blade'
)
De acordo com o exemplo apresentado pela Listagem 4, recuperamos algumas informações referentes ao produto, fazendo uma busca a mais na mesma tabela para trazer os dados de um registro específico, que seria o número de identificação do produto, mas sendo feita a pesquisa na segunda instrução a partir do nome do produto, que no nosso caso foi [name] = ‘Blade’. Veremos agora o que acontece nela, separando-a em duas partes: apresentaremos a primeira parte de acordo com o código da Listagem 5.
Listagem 5. Primeira parte da instrução utilizando WHERE.
SELECT [ProductID]
FROM [AdventureWorks2012].[Production].[Product]
WHERE [Name] = 'Blade'
A subconsulta apresentada anteriormente é executada primeiro e após obter o resultado, ela retorna o id do produto para a linha cujo [Name] é ‘Blade’. O [ProductID] para esta linha é 316, o que é então passado para a cláusula WHERE da consulta externa (nossa consulta principal), a qual vemos no código da Listagem 6.
Listagem 6. Instrução principal da consulta.
SELECT
[ProductID]
,[Name]
,[ProductNumber]
,[Color]
FROM [AdventureWorks2012].[Production].[Product]
WHERE [ProductID] = 316
Utilizando operadores de comparação em subqueries de linha única
No exemplo que apresentamos na Listagem 4 fizemos uso do operador de igualdade (=) na cláusula WHERE. Além dele, outros operadores de comparação podem ser utilizados com uma única subconsulta, tais como o <>, > (Maior que), < (Menor que), >= (Maior ou igual) e o <= (Menor ou igual). No exemplo que iremos apresentar na Listagem 7 utilizaremos o operador de menor que na cláusula WHERE da consulta externa. Nela usaremos a função AVG() que será usada na subconsulta para obtermos a quantidade média das ordens e que após isso, será passada para a cláusula WHERE da consulta externa. O resultado final de toda a consulta é para obtermos os campos [PurchaseOrderID], [DueDate], [OrderQty] e o [UnitPrice].
Listagem 7. Quantidade média de ordens.
SELECT [PurchaseOrderID]
,[DueDate]
,[OrderQty]
,[UnitPrice]
FROM [AdventureWorks2012].[Purchasing].[PurchaseOrderDetail]
WHERE [PurchaseOrderID] > (
SELECT AVG([PurchaseOrderID])
FROM [AdventureWorks2012].[Purchasing].[PurchaseOrderDetail]
WHERE [DueDate] = '2007-10-31'
)
Se isolarmos a consulta secundária e executarmos, obteremos como resposta para a média do número de ordens o valor de 915, e com essa informação sendo passada para a consulta principal, obtemos todas as ordens com o [PurchaseOrderID] > 915.
Utilizando subqueries com cláusula HAVING
A cláusula HAVING é usada para filtrar grupos de linhas de registros, o que nos permite filtrarmos grupos de linhas com base no resultado retornado por uma subconsulta. Vejamos de forma mais prática esta questão de acordo com o código presente na Listagem 8.
Listagem 8. Utilizando a cláusula HAVING.
SELECT AVG([PurchaseOrderID])
,[VendorID]
,COUNT([VendorID])
FROM [AdventureWorks2012].[Purchasing].[PurchaseOrderHeader]
GROUP BY [VendorID]
HAVING AVG([PurchaseOrderID]) =
(SELECT AVG([PurchaseOrderID])
FROM [AdventureWorks2012].[Purchasing].[PurchaseOrderHeader]
where VendorID = 1690
)
Subqueries em uma cláusula FROM
Além das cláusulas HAVING e WHERE, também podemos criar subconsultas com a cláusula FROM de uma consulta externa, vejamos isso de acordo com o exemplo apresentado pela Listagem 9.
Listagem 9. Subconsulta com a cláusula FROM.
SELECT [ProductID]
FROM
(SELECT [ProductID]
FROM [AdventureWorks2012].[Production].[Product]
WHERE [ProductID] < 320) as TESTE
Na subconsulta apresentada no código anterior estamos interessados apenas em trazer os IDs dos produtos que sejam menores que o id do produto especificado, que no nosso caso, foi o [ProductID] = 320. Em alguns momentos podemos ter problemas com a utilização dessas subconsultas e por isso, termos o desprazer de receber notificações de erros.
Aqui, estaremos vendo alguns erros que podemos enfrentar em uma operação de simple row subquery. Nos nossos exemplos anteriores vimos uma subconsulta que nos retorna sempre uma única linha, mas se uma subconsulta nos retornar mais de uma linha, ocorrerá um erro. No exemplo apresentado pela Listagem 10, tentaremos passar uma subconsulta com várias linhas para o operador de igualdade (=), presente na consulta externa.
Listagem 10. Tentativa de múltiplas linhas numa subquery de linha única.
SELECT [ProductID]
,[Name]
FROM [AdventureWorks2012].[Production].[Product]
WHERE [ProductID] = (
SELECT ProductID
FROM [AdventureWorks2012].[Production].[Product]
WHERE [Name] LIKE '%a%'
)
Ao executarmos esta consulta no banco de dados, teremos um erro com a mesma informação do código abaixo:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Como podemos observar, a informação que é apresentada é de que a subquery tentou retornar mais que um valor e isto não é permitido neste tipo de subquery. Veremos então quando isso é possível.
Utilizando subqueries de múltiplas linhas (subquery multiple row)
Quando precisamos realizar consultas onde o resultado seja a apresentação de várias linhas, podemos utilizar os operadores IN, ANY ou ALL nas nossas consultas externas para lidar com uma subconsulta que retorna várias linhas.
No caso do operador IN, ele é utilizado para verificar um valor dentro de um conjunto de valores, onde esta lista de valores pode vir dos resultados de uma subconsulta. Vejamos então de acordo com a Listagem 11 como seria a sua utilização.
Listagem 11. Utilizando o operador IN.
SELECT [PurchaseOrderID]
,[VendorID]
,[OrderDate]
,[TotalDue]
FROM [AdventureWorks2012].[Purchasing].[PurchaseOrderHeader]
WHERE [VendorID] IN (
SELECT [VendorID] FROM [AdventureWorks2012].[Purchasing].[PurchaseOrderHeader]
WHERE [EmployeeID] = 255
Percebam que utilizamos o operador IN para trazer todos os fornecedores (instrução principal) com base na instrução secundária que era a obtenção desse resultado para um determinado empreendedor. Utilizando o operador NOT IN, teríamos o mesmo resultado, com a diferença de que seriam retornados os fornecedores de todos os empreendedores, menos para o que possui o id [EmployeeID] = 255.
Utilizando o operador ANY com uma subconsulta Multiple Row
No caso de utilizarmos o operador ANY, ele não restringe os resultados numa lista, ele nos traz qualquer coisa, precisando colocar um operador do tipo =, <>,>, <, <= ou >= antes de ANY na nossa consulta. Apresentaremos um exemplo com o código da Listagem 12.
Listagem 12. Utilizando o operador ANY.
SELECT [PurchaseOrderID]
,[VendorID]
,[OrderDate]
,[TotalDue]
FROM [AdventureWorks2012].[Purchasing].[PurchaseOrderHeader]
WHERE [VendorID] = ANY (
SELECT [VendorID] FROM [AdventureWorks2012].[Purchasing].[PurchaseOrderHeader]
WHERE [EmployeeID] = 255
)
Percebam que neste caso retornamos as ordens de compra para todos os fornecedores, de acordo com o empreendedor de id 255.
Utilizando subqueries de múltiplas colunas (multiple column subquery)
Como o próprio título nos informa, este é o tipo de consulta que será realizado utilizando-se de duas ou mais colunas, dependendo da granularidade necessária a nossa necessidade. Neste exemplo, buscaremos por todas as informações referentes ao id da pessoa, número de ordem de compra, número do pedido de compra e o id do cliente, todas essas informações a partir de uma consulta com base nas informações de [SalesOrderNumber] e [PurchaseOrderNumber], primeiro verificando se eles existem, utilizando a cláusula EXISTS. Vejamos então de forma prática como ficaria nossa consulta, como apresentada Listagem 13.
Listagem 13. Utilizando multiple row subquery.
SELECT [SalesOrderID]
,[SalesPersonID]
,[SalesOrderNumber]
,[PurchaseOrderNumber]
,[CustomerID]
FROM [AdventureWorks2012].[Sales].[SalesOrderHeader]
WHERE EXISTS
(SELECT [SalesOrderNumber],[PurchaseOrderNumber]
FROM [AdventureWorks2012].[Sales].SalesOrderHeader
)
ORDER BY [SalesPersonID] DESC
Como apresentado de acordo com a Listagem 13, utilizamos uma subconsulta utilizando a palavra-chave EXISTS, neste caso, a subconsulta funciona basicamente como sendo um teste de existência. A cláusula WHERE presente na consulta externa realiza um teste para ver se as linhas retornadas pela subconsulta existem. A subconsulta não produz nenhum resultado de fato, ela apenas retorna um valor TRUE ou FALSE.
No nosso próximo exemplo, estaremos apresentando uma pesquisa com a instrução IN onde informamos os departamentos que queremos pesquisar com a verificação da existência de uma pessoa com o primeiro nome igual a “Ken”, como apresentado pela Listagem 14.
Listagem 14. Execução de subquery com múltiplas colunas.
Select dep.Name, p.FirstName, p.LastName, p.MiddleName, p.BusinessEntityID from HumanResources.Department dep, HumanResources.EmployeeDepartmentHistory empDep, Person.Person p
where
dep.DepartmentID in(1,16,12)
AND exists (select * from HumanResources.Employee emp, Person.Person p2
where p.FirstName = p2.FirstName
AND p2.FirstName = 'Ken'
AND p2.Title LIKE 'Mr%'
)
AND dep.DepartmentID = empDep.DepartmentID
Como apresentado de acordo com a Listagem 14, criamos uma pesquisa interna com duas colunas, sendo utilizados os campos, título e primeiro nome do empregado da empresa, para que a partir daí trouxéssemos os departamentos correspondentes.
Com isso chegamos ao fim deste artigo, onde tivemos a finalidade de apresentarmos conceitos importantes com relação a utilização de subconsultas em nossas consultas T-SQL. O que não podemos esquecer é que essa não é a única maneira de filtrarmos nossos dados, pois podemos usar ainda junções (joins), mas o mais importante é sabermos quando usar cada uma delas.
Até a próxima! =)
Artigos relacionados
-
Artigo
-
Artigo
-
Artigo
-
Artigo
-
Artigo