• 發行項

ANY、SOME 或 ALL 修改的比較運算子

提出子查詢的比較運算子可由關鍵字 ALL 或 ANY 來修改。在 ISO 標準中，SOME 就等於 ANY。

由已修改的比較運算子提出的子查詢會傳回零或多個值清單，並可包含 GROUP BY 或 HAVING 子句。這些子查詢可使用 EXISTS 來重新敘述。

使用 > 比較運算子作為範例，>ALL 表示大於每一個值。換言之，它表示大於最大值。例如，>ALL (1, 2, 3) 表示大於 3。>ANY 表示大於至少一個值，也就是大於最小值。因此 >ANY (1, 2, 3) 代表大於 1。

包含 >ALL 的子查詢中的資料列若要滿足外部查詢所指定的條件，提出子查詢之資料行中的數值，必須大於子查詢所傳回數值清單中的每個數值。

同樣地，>ANY 代表若要某個資料列滿足外部查詢所指定的條件，提出子查詢的資料行中數值，至少需要大於子查詢所傳回數值清單中的某個數值。

下列查詢提供了一個以 ANY 修改的比較運算子提出的子查詢範例。它會尋找清單價格大於或等於任何產品子類別之最大清單價格的產品。

USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ListPrice >= ANY
    (SELECT MAX (ListPrice)
     FROM Production.Product
     GROUP BY ProductSubcategoryID)

對於每一個產品子類別，內部查詢會尋找最大清單價格。外部查詢則會查看所有這些值，來判斷哪一項個別產品的清單價格大於或等於任何產品子類別的最大清單價格。如果將 ANY 變更為 ALL，則查詢只傳回其清單價格大於或等於內部查詢傳回之所有清單價格的產品。

若子查詢並未傳回任何數值，整個查詢將無法傳回任何數值。

=ANY 運算子等於 IN。例如，若要找出 Adventure Works Cycles 製作的所有輪類產品的名稱，您可以使用 IN 或 =ANY。

--Using =ANY
USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID =ANY
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Wheels')

--Using IN
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)

不過，< >ANY 運算子和 NOT IN 並不一樣：< >ANY 代表 not = a、或 not = b、或 not = c。NOT IN 代表 not = a，且 not = b，且 not = c。<>ALL 則和 NOT IN 一樣。

例如，下列查詢會找出位於任何銷售員都未涵蓋之地區的客戶。

Use AdventureWorks;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID <> ANY
    (SELECT TerritoryID
     FROM Sales.SalesPerson)

除了銷售區域為 NULL 的客戶以外，其結果將包含其他所有的客戶，因為指派給客戶的每一個區域都有一位銷售人員負責。內部查詢會先尋找有銷售人員負責的所有銷售區域，然後，外部查詢再尋找不在其中一個區域內的客戶。

基於相同理由，當您在這個查詢中使用 NOT IN 時，其結果將不包括任何客戶。

您可以使用 < >ALL 運算子取得相同的結果，它相當於 NOT IN。