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

Here is the result:

Name

--------------------------------------------------

Compete Enterprises, Inc

International Trek Center

First National Sport Co.

Comfort Road Bicycles

Circuit Cycles

First Rate Bicycles

Jeff's Sporting Goods

Competition Bike Training Systems

Electronic Bike Repair & Supplies

Crowley Sport

Expert Bike Co

Team Athletic Co.

Compete, Inc.

(13 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 AdventureWorks2008R2;
GO
SELECT DISTINCT Name
FROM Purchasing.Vendor v
INNER JOIN Purchasing.ProductVendor p
ON v.BusinessEntityID = p.BusinessEntityID
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.