October 28, 2021

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.

ANY and ALL Comparison Modifiers

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:

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>