Another way to check data is to use the ANY and ALL commands.

These commands are not used that often, but they are good to know in case you come across any code that uses them.

SQL ALL Comparison

The SQL ALL comparison is used to check if an expression matches all values in a list.

It can be used with any of the basic operators: =, <>, >, <, >=, <=.

It looks like this:

WHERE expression = ALL (values)

Internally, this expression is translated into multiple WHERE clauses using the AND keyword.

So, a query like this:

SELECT id, first_name, last_name
FROM customer
WHERE first_name = ALL ('John', 'Mary', 'Susan', 'Mark')

Is the same as this query:

SELECT id, first_name, last_name
FROM customer
WHERE first_name = 'John'
AND first_name = 'Mary'
AND first_name = 'Susan'
AND first_name = 'Mark';

Now, looking at that query, there’s no way a first name can be equal to all of those values. So this query would return no results.

But, the ALL keyword is perhaps better used with another operator.

For example:

SELECT id, first_name, last_name
FROM customer
WHERE id > ALL (3, 8, 15);

This is the same as this query:

SELECT id, first_name, last_name
FROM customer
WHERE id > 3
AND id > 8
AND id > 15;

Using ALL, the criteria only needs to check against the minimum or maximum value. In this example, if an id is greater than 15, it’s greater than 3 and 8, so the checks for 3 and 8 are not needed. The query could be written as:

SELECT id, first_name, last_name
FROM customer
WHERE id > 15;

But that’s how you use the ALL operator. It’s not used very often that I’ve seen, but it’s good to know what it is.

SQL ANY Comparison

The SQL ANY comparison is similar to ALL. It is used to check if an expression matches all values in a list.

It can be used with any of the basic operators: =, <>, >, <, >=, <=.

It looks like this:

WHERE expression = ANY (values)

Internally, this expression is translated into multiple WHERE clauses using the OR keyword (The ALL operator used the AND keyword).

SELECT id, first_name, last_name
FROM customer
WHERE first_name = ANY ('John', 'Mary', 'Susan', 'Mark');

Is the same as this query:

SELECT id, first_name, last_name
FROM customer
WHERE first_name = 'John'
OR first_name = 'Mary'
OR first_name = 'Susan'
OR first_name = 'Mark';

It can also be written as an IN query:

SELECT id, first_name, last_name
FROM customer
WHERE first_name IN ('John', 'Mary', 'Susan', 'Mark');

If you use another operator, such as less than, the ANY keyword can look like this:

SELECT id, first_name, last_name
FROM customer
WHERE id < ANY (4, 9, 16);

This can also be written like this:

SELECT id, first_name, last_name
FROM customer
WHERE id < 4
OR id < 9
OR id < 16;

Because a value that is less than 4 is also less than 9 and 16, those can be removed. This query will return the same results:

SELECT id, first_name, last_name
FROM customer
WHERE id < 4;

Summary of ALL and ANY

The results of different combinations of ANY and ALL can be summarised as follows:

As mentioned earlier, you probably won’t find the need to use this syntax very often, but it’s good to know it exists.

More Information

Here are some more articles on ANY and ALL:

• Oracle Base: ANY ALL and SOME

Copyright de Ben Brumm - DatabaseStar