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
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);
Good catch! Thanks, -Kev