使用 IN 的子查询
通过 IN（或 NOT IN）引入的子查询结果是包含零个值或多个值的列表。子查询返回结果之后，外部查询将利用这些结果。
下面的查询查找 Adventure Works Cycles 生成的所有车轮产品的名称。
USE AdventureWorks2008R2;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID IN
(SELECT ProductSubcategoryID
FROM Production.ProductSubcategory
WHERE Name = 'Wheels');
下面是结果：
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)
该语句分两步进行评估。首先，内部查询返回与名称 'Wheel' (17) 匹配的子类别标识号。然后，这些值将替换到外部查询中，此外部查询将在 Product 中查找与子类别标识号匹配的产品名称。
USE AdventureWorks2008R2;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID IN ('17');
使用联接而不使用子查询处理该问题及类似问题的一个不同之处在于，联接使您可以在结果中显示多个表中的列。例如，如果要在结果中包括产品子类别的名称，则必须使用联接版本。
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';
下面是结果：
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)
以下示例查找所有信誉度良好，与 Adventure Works Cycles 有过至少 20 项订购记录，并且其平均交付时间小于 16 天的所有供应商的名称。
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);
下面是结果：
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)
评估内部查询后，产生符合子查询限定条件的供应商的 ID 号。然后评估外部查询。注意，在内部和外部查询的 WHERE 子句中，都可以包括多个条件。
使用联接，同一查询可以用如下方式表示：
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 和 B，都将得到相同的结果。而对子查询来说，情况则并非如此。