Online College Courses for Credit

+
ANY and ALL Operators

ANY and ALL Operators

Rating:
(0)
Author: Sophia Tutorial
Description:

Compose a query that uses ANY and ALL to compare values to values within a subquery.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

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);

File:11459-2350-1.png

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);

File:11460-2350-2.png

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);

File:11461-2350-3.png

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:

File:11462-2350-4.png

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.

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.

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.