使用 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 個資料列受到影響)
此陳述式將以兩個步驟進行評估。首先，內部查詢會傳回與 'Wheel' (17) 名稱相符的子類別目錄識別碼。接著，此數值將替代至外部查詢中，並在 Product 中找出具有這些子類別目錄識別碼的產品名稱。
USE AdventureWorks2008R2;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID IN ('17');
在這個相似的問題中，使用聯結 (Join) 而非子查詢的差別在於，聯結可讓您在結果中顯示多個資料表的資料行。例如，若您想要在結果中包含產品子類別目錄的名稱，必須使用聯結的版本。
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 個資料列受到影響)
下列查詢會尋找信用評比為良好、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 個資料列受到影響)
內部查詢進行評估後，會產生符合子查詢資格的廠商識別碼。外部查詢然後再進行運算。請注意，您可在內部與外部查詢的 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，最後答案都是一樣的。若是包含子查詢，則得到的答案不一定相同。