ANY、SOME、または ALL で修飾された比較演算子
サブクエリを導く比較演算子は、キーワード ALL または ANY で修飾できます。SOME は ANY に相当する ISO 標準です。
修飾した比較演算子で導かれたサブクエリは、0 個以上の値のリストを返し、GROUP BY 句または HAVING 句を含むことができます。これらのサブクエリは、EXISTS を使用して書き換えることができます。
> 比較演算子を例として使用すると、>ALL は、どの値よりも大きいという意味になります。つまり、最大値よりも大きいという意味です。たとえば、>ALL (1, 2, 3) は 3 より大きいという意味になります。>ANY は少なくとも 1 つの値より大きい、つまり最小値より大きいという意味です。したがって、>ANY (1、2、3) は 1 より大きいという意味になります。
>ALL が含まれたサブクエリの行が外側のクエリで指定された条件を満たすには、サブクエリを導く列の値がサブクエリによって返される値のリストのどの値よりも大きい必要があります。
同様に、>ANY は、行が外側のクエリで指定された条件を満たすには、サブクエリを導く列の値がサブクエリによって返される値のリストの少なくとも 1 つの値よりも大きい必要があることを意味します。
次のクエリでは、ANY によって修飾された比較演算子で導かれたサブクエリの例を示します。このクエリでは、製品のサブカテゴリの中から最も高い定価以上の定価が付けられた製品を検索します。
USE AdventureWorks2008R2;
GO
SELECT Name
FROM Production.Product
WHERE ListPrice >= ANY
(SELECT MAX (ListPrice)
FROM Production.Product
GROUP BY ProductSubcategoryID) ;
Product サブカテゴリごとに、内側のクエリが最も高い定価を検索します。外側のクエリは、これらすべての値を比較し、製品のサブカテゴリの中で最も高い定価以上の定価が付けられた製品を決定します。ANY を ALL に変更すると、クエリは内側のクエリで返されたすべての定価以上の定価が付けられた製品のみを返します。
サブクエリが値を返さない場合は、クエリ全体が値を返しません。
=ANY 演算子は IN と同じ意味を持ちます。たとえば、Adventure Works Cycles が製造しているすべてのホイール製品の名前を検索するときに、IN または =ANY を使用できます。
--Using =ANY
USE AdventureWorks2008R2;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID =ANY
(SELECT ProductSubcategoryID
FROM Production.ProductSubcategory
WHERE Name = 'Wheels') ;
--Using IN
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)
ただし、< >ANY 演算子は NOT IN とは異なります。< >ANY は、"not = a または not = b または not = c" という意味です。NOT IN は、"not = a かつ not = b かつ not = c" という意味です。<>ALL は NOT IN と同じ意味になります。
たとえば、次のクエリでは、担当販売員がいない区域の顧客を検索します。
Use AdventureWorks2008R2;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID <> ANY
(SELECT TerritoryID
FROM Sales.SalesPerson) ;
結果には、販売区域が NULL の顧客を除くすべての顧客が含まれます。これは、顧客に割り当てられている区域はすべて、いずれかの販売員が担当しているためです。内側のクエリで販売員が担当しているすべての販売区域が検索されてから、外部クエリで各区域に属していない顧客が検索されます。
同様の理由で、このクエリで NOT IN を使用すると、結果にはどの顧客も含められません。
NOT IN と同じ意味の < >ALL 演算子を使用しても同じ結果が得られます。