ANY and ALL Operators

1. Introduction

The ALL and ANY operators allow us to query data by comparing a value with a list of values that are returned by a subquery. This is an important distinction for the ANY and ALL operators, as they are focused on the lists from a subquery.

The syntax of the operator looks like:

<columnname> <operator> [ANY/ALL] (subquery);

The ANY operator is less restrictive than the ALL when it comes to comparisons. The ANY operator returns true if any of the values in the subquery meets the condition, otherwise it returns false. The ALL operator returns true if ALL of the values in the subquery meets the condition, otherwise it returns false.

2. Subquery Example

Let's take a look at an example where we’re needing to compare the average invoice totals per country:

SELECT AVG(total)
FROM invoice 
GROUP BY billing_country;

We will use that average by country as our subquery. If we wanted to find invoices that have a value higher than the average of any of the totals from the country, we would use the following:

SELECT * 
FROM invoice
WHERE total > ANY(
SELECT AVG(total)
FROM invoice 
GROUP BY billing_country);

To find invoices that have a value higher than all of the averages from all countries:

SELECT * 
FROM invoice
WHERE total > ALL(
SELECT AVG(total)
FROM invoice 
GROUP BY billing_country);

Notice the count difference between the two queries. The first query (with the ANY operator) is less restrictive.

3. Potential Error

With the ANY and ALL operator, the subquery must return a single column to compare. If we return more than one column in the subquery, only the first result set is returned:

SELECT * 
FROM invoice
WHERE (total,total) >= ALL(
SELECT AVG(total),max(total)
FROM invoice 
GROUP BY billing_country);

If we just compared the total to the max of the totals:

SELECT * 
FROM invoice
WHERE total >= ALL(
SELECT max(total)
FROM invoice 
GROUP BY billing_country);

One record would be returned:

4. Operators for Comparisons

You may notice that the query above uses >= vs a >. We can compare the ANY and ALL using a variety of operators.

• If we use > ALL, the expression evaluates to true if a value in the subquery is greater than the largest value returned by the subquery.
• If we use >= ALL, the expression evaluates to true if a value in the subquery is greater than or equal to the largest value returned by the subquery.
• If we use < ALL, the expression evaluates to true if a value in the subquery is less than the smallest value returned by the subquery.
• If we use <= ALL, the expression evaluates to true if a value in the subquery is less than or equal to the smallest value returned by the subquery.
• If we use = ALL, the expression evaluates to true if a value in the subquery is equal to every value returned by the subquery.
• If we use != ALL, the expression evaluates to true if a value in the subquery is not equal to any value returned by the subquery.
• If we use > ANY, the expression evaluates to true if a value in the subquery is greater than the smallest value returned by the subquery.
• If we use >= ANY, the expression evaluates to true if a value in the subquery is greater than or equal to the smallest value returned by the subquery.
• If we use < ANY, the expression evaluates to true if a value in the subquery is less than the largest value returned by the subquery.
• If we use <= ANY, the expression evaluates to true if a value in the subquery is less than or equal to the largest value returned by the subquery.
• If we use = ANY, the expression evaluates to true if a value in the subquery is equal to any value returned by the subquery. It works like the IN operator.
• If we use <> ANY, this expression is not the same as the NOT IN. Rather, if we had a scenario where we had column <> ANY (a, b, c), it would look like x <> a OR x <>b OR x<>c.

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.