ALL Operator

The ALL operator returns True when all of the records in query result set pass the Boolean condition.

A bit more explanation.  The ALL statement gives a TRUE value when: 1) the subquery returns no records, or 2) all records meet the comparison.  ALL will return false if even one record fails the Boolean condition.  If any of the values are NULL, then the operation value will be NULL, not TRUE.

For example, we want to see which authors currently have no titles:

SELECT au_id
FROM authors
WHERE au_id <> ALL(SELECT au_id FROM titleauthor)

Syntax

SELECT …
WHERE expression comparison {ALL | ANY | SOME} ( subquery )

Explained

WHERE expression

Tests a single value, such as a column, against all other values in the subquery. All rows need to match the expression to return a Boolean TRUE value for the ALL operator to return a Boolean TRUE value.

comparison

Compares the expression to the subquery.  The comparison must be a standard comparison operator like =, <>, !=, >, >=, <, or <=.

Advice

ALL is unique in that it guarantees that all records in the result set evaluate to TRUE.

DBMS Variations

All the major commercial database platforms support ALL.

Oracle supports a minor variation in that you can supply a list of values instead of a subquery.  For example, we can find all employees who have a job_lvl value equal to 9 or 14:

SELECT * FROM employee
WHERE job_lvl = ALL(9, 14);

DB2 and SQL Server supports some additional comparison operators with ALL, including not greater than (!>) and not less than (!<).

Related Entries

BETWEEN

EXISTS

IN

Operators

SELECT

SOME

WHERE

Google Author

Comments

  1. CROKUSEK says

    In this example shouldn’t it be ANY to get an OR effect (same as traditional “in (9,14)” when using the “=” operator)?

    SELECT * FROM employee
    WHERE job_lvl = ALL(9, 14);

  2. Good catch! Thanks, -Kev

Speak Your Mind

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.