SQL ANY and ALL Operators

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.

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.

Learn More

What to read next:

• SQL Subqueries – The Ultimate Guide
• SQL In Operator
• SQL Exists Operator