Comparison Operators Modified by ANY, SOME, or ALL
Comparison operators that introduce a subquery can be modified by the keywords ALL or ANY. SOME is an ISO standard equivalent for ANY.
Subqueries introduced with a modified comparison operator return a list of zero or more values and can include a GROUP BY or HAVING clause. These subqueries can be restated with EXISTS.
Using the > comparison operator as an example, >ALL means greater than every value. In other words, it means greater than the maximum value. For example, >ALL (1, 2, 3) means greater than 3. >ANY means greater than at least one value, that is, greater than the minimum. So >ANY (1, 2, 3) means greater than 1.
For a row in a subquery with >ALL to satisfy the condition specified in the outer query, the value in the column introducing the subquery must be greater than each value in the list of values returned by the subquery.
Similarly, >ANY means that for a row to satisfy the condition specified in the outer query, the value in the column that introduces the subquery must be greater than at least one of the values in the list of values returned by the subquery.
The following query provides an example of a subquery introduced with a comparison operator modified by ANY. It finds the products whose list prices are greater than or equal to the maximum list price of any product subcategory.
USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ListPrice >= ANY
(SELECT MAX (ListPrice)
FROM Production.Product
GROUP BY ProductSubcategoryID)
For each Product subcategory, the inner query finds the maximum list price. The outer query looks at all of these values and determines which individual product's list prices are greater than or equal to any product subcategory's maximum list price. If ANY is changed to ALL, the query will return only those products whose list price is greater than or equal to all the list prices returned in the inner query.
If the subquery does not return any values, the entire query fails to return any values.
The =ANY operator is equivalent to IN. For example, to find the names of all the wheel products that Adventure Works Cycles makes, you can use either IN or =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')
Here is the result set for either query:
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)
The < >ANY operator, however, differs from NOT IN: < >ANY means not = a, or not = b, or not = c. NOT IN means not = a, and not = b, and not = c. <>ALL means the same as NOT IN.
For example, the following query finds customers located in a territory not covered by any sales persons.
Use AdventureWorks;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID <> ANY
(SELECT TerritoryID
FROM Sales.SalesPerson)
The results include all customers, except those whose sales territories are NULL, because every territory that is assigned to a customer is covered by a sales person. The inner query finds all the sales territories covered by sales persons, and then, for each territory, the outer query finds the customers who are not in one.
For the same reason, when you use NOT IN in this query, the results include none of the customers.
You can get the same results with the < >ALL operator, which is equivalent to NOT IN.
See Also