SQL ANY and ALL keywords are used in subquery comparisons to compare a set of value against all values in the result or any one value in the set.
Comparison operators such as greater than, less than, equal, and not equal can be modified in interesting ways to enhance comparisons done in conjunction with WHERE clauses.
Rather than using >, which only makes sense when comparing to a single (scalar) value, you can use > ANY or > ALL to compare a column value to a list results returned from the subquery.
Table of contents
Using the SQL ANY Modifier
The comparison operator > ANY means greater than one or more items in the list. This is the same as saying it greater than the MIN value of the list. So the expression
Sales > ANY (1000, 2000, 2500)
returns TRUE if Sales are greater than 1000 as this expression is equivalent to
Sales > MIN(1000, 2000, 2500)
Which simplifies to
Sales > 1000
Note: You may see some queries using SOME. Queries using SOME return the same result as those using ANY. Simply said > ANY is the same as > SOME.
Let’s do an example using the adventure works database. We’re going to find all products which may have a high safety stock level. To do so, we’ll look for all products that have a SafetyStockLevel that is greater than the average SafetyStockLevel for various DaysToManufacture.
The query to do this is:
SELECT ProductID, Name, SafetyStockLevel, DaysToManufacture FROM Production.Product WHERE SafetyStockLevel > ANY (SELECT AVG(SafetyStockLevel) FROM Production.Product GROUP BY DaysToManufacture)
When this subquery is run it first calculates the Average SafetyStockLevel. This returns a list of numbers. Then for each product row in the outer query SafetyStockLevel is compared. If it is greater than one or more from the list, then include it in the results.
Like me, you may at first think that > ANY is redundant, and not needed. It is equivalent to > MIN(…) right?
What I found out is that though it is equivalent in principle, you can’t use MIN. The statement
SELECT ProductID, Name, SafetyStockLevel, DaysToManufacture FROM Production.Product WHERE SafetyStockLevel > MIN((SELECT AVG(SafetyStockLevel) FROM Production.Product GROUP BY DaysToManufacture))
Won’t run. It return the error, “Cannot perform an aggregate function on an expression containing an aggregate or a subquery.”
Read More: SQL Subqueries – The Ultimate Guide >>
Using the SQL ALL Modifier
The > ALL modifier works in a similar fashion except it returns the outer row if it’s comparison value is greater than every value returned by the inner query.
The comparison operator > ALL means greater than the MAX value of the list. Using the example above, then
Sales > ALL (1000, 2000, 2500)
is equivalent to
Sales > MAX(1000, 2000, 2500)
Which returns TRUE if Sales > 2500
In this example, we’ll return all SalesPeople that have a bonus greater than ALL salespeople whose year-to-date sales were less than a million dollars.
SELECT p.BusinessEntityID, p.FirstName, p.LastName, s.Bonus, s.SalesYTD FROM Person.Person AS p INNER JOIN Sales.SalesPerson AS s ON p.BusinessEntityID = s.BusinessEntityID WHERE s.Bonus > ALL (SELECT Bonus FROM Sales.SalesPerson WHERE Sales.SalesPerson.SalesYTD < 1000000)
Summary of Various Comparison Modifiers
You can use comparison modifiers with other operators, such as equals. Use the chart below to get a better understanding of the examples. I’ve listed all the combinations, even those that don’t make too much sense.
When reviewing the example assume the subquery returns a list of three numbers: 1,2,3.
Some combinations of these comparison modifiers are downright goofy. For instance, I can’t imagine using “= ALL” or “<> ANY.” The others make sense, and as we have shown you can really use MAX or MIN as legal equivalent statements. SQL ANY and ALL operators do have their places!
Out of all of the items we discussed today I’ve used EXISTS and NOT EXISTS the most with subqueries. I use IN quite a bit, but usually with a static list, not with subqueries.
What to read next: