Or

Tutorial

what's covered

This tutorial explores using ANY and ALL operators to compare values with a list of values returned by a subquery.

The ALL and ANY operators allow us to be able to query data by comparing a value with a list of values that are returned by a subquery. This is an important distinction between 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.

Let us 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 an invoice that has a value that’s 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 get the results from the same criteria to compare to check the invoice total be 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 rows.

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:

You may notice that the query above using >= 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 it 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 it the subquery.
- If we use < ALL, the expression evaluates to true if a value in the subquery is less than the smallest value returned it 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 it the subquery.
- If we use = ALL, the expression evaluates to true if a value in the subquery is equal to any value returned it the subquery.
- If we use != ALL, the expression evaluates to true if a value in the subquery is not equal to any value returned it the subquery.
- If we use > ANY, the expression evaluates to true if a value in the subquery is greater than the smallest value returned it 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 it the subquery.
- If we use < ANY, the expression evaluates to true if a value in the subquery is less than the largest value returned it 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 it the subquery.
- If we use = ANY, the expression evaluates to true if a value in the subquery is equal to any value returned it 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.

summary

In this tutorial, we learned to use the ALL or ANY operator to compare values to a list of values that would be returned by a subquery.