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;
FROM Production.Product
WHERE ProductSubcategoryID IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Wheels')

Here is the result:

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;
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;
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;
FROM Purchasing.Vendor
WHERE CreditRating = 1
    (SELECT VendorID
     FROM Purchasing.ProductVendor
     WHERE MinOrderQty >= 20
     AND AverageLeadTime < 16)

Here is the result:

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;
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.

See Also