Lição 2: Implementando SubConsultas

junho 26, 2011

 

As subconsultas são úteis para construir consultas complexas e para obter resultados que antes pareciam impossíveis.

Existem dois tipos de subconsultas:

Correlacionada:  é uma consulta que depende e faz referência às colunas da consulta externa a qual está contida.

Não correlacionada: é uma consulta independente da consulta externa na qual está contida.

Ambos os tipos de subconsultas podem retornar uma ou mais linhas. Quando o retorno é escalar, somente um valor,  pode ser definido em qualquer lugar de dentro do SELECT onde um ou zero valores são esperados, quando o retorno é múltiplo a subconsulta pode ser utilizada em onde o resultset é esperado.

 

Consultas não correlacionadas

O principal objetivo de consultas não correlacionadas é permitir a escrita de códigos mais dinâmicos. Por exemplo, você precisa listar os produtos onde o nome da categoria inicia com a letra C, porém você desconhece todas as subcategorias necessárias para especificar na cláusula where, veja o exemplo abaixo:

SELECT P.Name, P.Color
FROM Production.Product P
     INNER JOIN Production.ProductSubcategory S
       ON P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE S.ProductCategoryID IN( SELECT C.ProductCategoryID       FROM Production.ProductCategory C
WHERE Name LIKE ‘C%’)

Outro exemplo, supondo que você gostaria de retornar todos os produtos onde o preço de lista está acima da média geral, ao invés de obter o valor e armazenar em uma variável, você pode fazer como no exemplo abaixo:

 
SELECT A.ProductID, A.Name, A.ListPrice
FROM Production.Product A
WHERE A.ListPrice> (
SELECT AVG(B.ListPrice)
                    FROM Production.Product B
)
 
Tabelas derivadas 

Ao invés de armazenar dados em tabelas temporárias para fazer joins e retornar um determinado conjunto de resultados, você pode utilizar as tabelas derivadas.

Tabelas derivadas são consultas realizadas dentro da clausula from de uma consulta externa, é necessário que todos os campos calculados sejam nomeados, a consulta deve estar contida entre parênteses e possuir um apelido.

Veja o exemplo abaixo:

 
SELECT B.BusinessEntityID, B.JobTitle, A.QtdeLinhas
FROM (SELECT E.JobTitle, COUNT(*) QtdeLinhas
      FROM HumanResources.Employee E

      GROUP BY JobTitle) A

INNER JOIN HumanResources.Employee B on A.JobTitle=B.JobTitle

O príncipal benefício de uma tabela derivada é o fato de que o resultado está armazenado inteiramente na memória, resultando em um ganho de desempenho.

Agrupamentos

Você pode realizar agrupamentos dentro de tabelas derivadas e combiná-los externamente.

Como no exemplo abaixo:

SELECT SH3.SalesPersonID,SH3.OrderDate, SH3.DailyTotal, SUM(SH4.DailyTotal) RunningTotal
FROM    (SELECT SH1.SalesPersonID, SH1.OrderDate, SUM(SH1.TotalDue) DailyTotal
                FROM Sales.SalesOrderHeader SH1
                WHERE SH1.SalesPersonID  IS NOT NULL
                GROUP BY SH1.SalesPersonID, SH1.OrderDate) SH3
INNER JOIN  (SELECT SH2.SalesPersonID, SH2.OrderDate, SUM(SH2.TotalDue) DailyTotal
                FROM Sales.SalesOrderHeader SH2
                WHERE SH2.SalesPersonID  IS NOT NULL
                GROUP BY SH2.SalesPersonID, SH2.OrderDate)SH4
ON SH3.SalesPersonID = SH4.SalesPersonID
    AND SH3.OrderDate>SH4.OrderDate
GROUP BY SH3.SalesPersonID, SH3.OrderDate, SH3.DailyTotal
ORDER BY SH3.SalesPersonID, SH3.OrderDate

As tabelas derivadas foram combinadas para retornar todos os pedidos de um vendedor que possuir mais de um pedido por dia. O join por SalesPersonID (vendedor) assegura que você esta acumulando linhas do mesmo vendedor. O join sem igualdade determina que somente os pedidos do vendedor onde data do pedido é anterior à data do pedido dentro do resultset serão retornados.

 

Consultas correlacionadas

As consultas correlacionadas dependem de valores da consulta externa. No exemplo abaixo será listado o Id do Produto e o preço de lista, porém será retornado apenas os produtos que já foram vendidos anteriormente.

SELECT a.ProductID, a.ListPrice 
FROM Production.Product A
WHERE EXISTS (SELECT 1 FROM Sales.SalesOrderDetail B
WHERE B.ProductID=A.ProductID)

Usando um Join ou uma cláusula where será retornado o produto para cada vez que ele foi vendido, já o comando EXISTS provém melhor desempenho, pois procura será por uma única ocorrência na tabela.

 

[]’s

Andressa Alves Martins

Twitter: Dre_Martins Skype: Dre.alvesm

Blog: http://sqlgo.wordpress.com