使用 IN 的子查詢

IN (或 NOT IN) 提出的子查詢結果為零或多個值的清單。當子查詢傳回結果之後，外部查詢將會使用這些傳回結果。

下列查詢會找到 Adventure Works Cycles 製造的所有滾輪產品的名稱。

USE AdventureWorks;
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 AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID in ('17')

在這個相似的問題中，使用聯結 (Join) 而非子查詢的差別在於，聯結可讓您在結果中顯示多個資料表的資料行。例如，若您想要在結果中包含產品子類別目錄的名稱，必須使用聯結的版本。

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'

結果如下：

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

結果如下：

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)

內部查詢進行運算後，會產生符合子查詢資格的廠商識別碼。外部查詢然後再進行運算。請注意，您可在內部與外部查詢的 WHERE 子句中包含多個條件。

透過聯結的使用，上面的查詢可以下列形式來表示：

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 與 B，最後答案都是一樣的。若是包含子查詢，則得到的答案不一定相同。