Subqueries with IN
The result of a subquery introduced with IN (or with NOT IN) is a list of zero or more values. After the subquery returns results, the outer query makes use of them.
The following query finds the names of all the wheel products that Adventure Works Cycles makes.
USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID IN
(SELECT ProductSubcategoryID
FROM Production.ProductSubcategory
WHERE Name = 'Wheels')
Here is the result:
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)
This statement is evaluated in two steps. First, the inner query returns the subcategory identification number that matches the name 'Wheel' (17). Second, this value is substituted into the outer query, which finds the product names that go with the subcategory identification numbers in Product.
USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID in ('17')
One difference in using a join rather than a subquery for this and similar problems is that the join lets you show columns from more than one table in the result. For example, if you want to include the name of the product subcategory in the result, you must use a join version.
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'
Here is the result:
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)
The following query finds the name of all vendors whose credit rating is good, from whom Adventure Works Cycles orders at least 20 items, and whose average lead time to deliver is less than 16 days.
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)
Here is the result:
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)
The inner query is evaluated, producing the ID numbers of the vendors who meet the subquery qualifications. The outer query is then evaluated. Notice that you can include more than one condition in the WHERE clause of both the inner and the outer query.
Using a join, the same query is expressed like this:
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
A join can always be expressed as a subquery. A subquery can often, but not always, be expressed as a join. This is because joins are symmetric: you can join table A to B in either order and get the same answer. The same is not true if a subquery is involved.