Subconsultas com IN

O resultado de uma subconsulta apresentada com IN (ou com NOT IN) é uma lista com zeros ou outros valores. Depois dos resultados da subconsulta retornarem, a consulta exterior os utiliza.

A consulta a seguir encontra os nomes de todos os produtos de roda que o Adventure Works Cycles cria.

USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Wheels')

Aqui está o resultado.

Name
--------------------------------------------------
LL Mountain Front Wheel
ML Mountain Front Wheel
HL Mountain Front Wheel
LL Road Front Wheel
ML Road Front Wheel
HL Road Front Wheel
Touring Front Wheel
LL Mountain Rear Wheel
ML Mountain Rear Wheel
HL Mountain Rear Wheel
LL Road Rear Wheel
ML Road Rear Wheel
HL Road Rear Wheel
Touring Rear Wheel

(14 row(s) affected)

Esta instrução é avaliada em dois passos. Primeiro, a consulta interna retorna o número de identificação da subcategoria que corresponde ao nome 'Wheel' (Roda) (17). Depois, esse valor é substituído na consulta exterior a qual acha o nome do produto que vai com os números de identificação da subcategoria em Product.

USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID in ('17')

Uma diferença ao usar uma junção em vez de uma subconsulta para esse e outros problemas semelhantes é que a junção o deixa mostrar colunas de mais de uma tabela no resultado. Por exemplo, se você quiser incluir o nome da subcategoria do produto no resultado, deverá usar uma versão de junção.

Use AdventureWorks;
GO
SELECT p.Name, s.Name
FROM Production.Product p
INNER JOIN Production.ProductSubcategory s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
AND s.Name = 'Wheels'

Aqui está o resultado.

Name                                               Name
LL Mountain Front Wheel                            Wheels
ML Mountain Front Wheel                            Wheels
HL Mountain Front Wheel                            Wheels
LL Road Front Wheel                                Wheels
ML Road Front Wheel                                Wheels
HL Road Front Wheel                                Wheels
Touring Front Wheel                                Wheels
LL Mountain Rear Wheel                             Wheels
ML Mountain Rear Wheel                             Wheels
HL Mountain Rear Wheel                             Wheels
LL Road Rear Wheel                                 Wheels
ML Road Rear Wheel                                 Wheels
HL Road Rear Wheel                                 Wheels
Touring Rear Wheel                                 Wheels

(14 row(s) affected)

A consulta a seguir acha o nome de todos os fornecedores cuja avaliação de crédito é boa, os nomes daqueles que Adventure Works Cycles compram no mínimo 20 itens e aqueles cujo tempo médio de entrega é menor que 16 dias.

Use AdventureWorks;
GO
SELECT Name
FROM Purchasing.Vendor
WHERE CreditRating = 1
AND VendorID IN
    (SELECT VendorID
     FROM Purchasing.ProductVendor
     WHERE MinOrderQty >= 20
     AND AverageLeadTime < 16)

Aqui está o resultado.

Name
--------------------------------------------------
Electronic Bike Repair & Supplies
Comfort Road Bicycles
Compete, Inc.
Compete Enterprises, Inc
First Rate Bicycles
First National Sport Co.
Competition Bike Training Systems
Circuit Cycles
Crowley Sport
Expert Bike Co

(10 row(s) affected)

A consulta interna é avaliada, produzindo os números de ID dos fornecedores que atendam às qualificações da subconsulta. A consulta exterior é então avaliada. Observe que você pode incluir mais de uma condição na cláusula WHERE tanto da consulta interna quanto da exterior.

Usando uma junção, a mesma consulta é expressada assim:

USE AdventureWorks;
GO
SELECT DISTINCT Name
FROM Purchasing.Vendor v
INNER JOIN Purchasing.ProductVendor p
ON v.VendorID = p.VendorID
WHERE CreditRating = 1
AND MinOrderQty >= 20
AND AverageLeadTime < 16

Uma junção sempre pode ser expressada como uma subconsulta. Uma subconsulta pode freqüentemente, mas não sempre, ser expressada como uma junção. Isso se deve ao fato de as junções serem simétricas: você pode unir as tabelas A e B em qualquer ordem e obter a mesma resposta. O mesmo não será verdade se uma subconsulta for envolvida.