ANY and ALL

The words ANY and ALL may be used with subqueries that produce a single column of
numbers. If the subquery is preceded by the keyword ALL, the condition will only be true
if it is satisfied by all values produced by the subquery. If the subquery is preceded by
the keyword ANY, the condition will be true if it is satisfied by any (one or more) values
produced by the subquery. If the subquery is empty, the ALL condition returns true, the
ANY condition returns false. The ISO standard also allows the qualifier SOME to be used
in place of ANY.
Example 5.22 Use of ANY/SOME
Find all staff whose salary is larger than the salary of at least one member of staff at
branch B003.
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > SOME (SELECT salary
FROM Staff
WHERE branchNo = ‘B003’);
While this query can be expressed using a subquery that finds the minimum salary of the
staff at branch B003, and then an outer query that finds all staff whose salary is greater
than this number (see Example 5.20), an alternative approach uses the SOME/ANY
keyword. The inner query produces the set {12000, 18000, 24000} and the outer query
selects those staff whose salaries are greater than any of the values in this set (that is,
greater than the minimum value, 12000). This alternative method may seem more natural
than finding the minimum salary in a subquery. In either case, the result table is shown in
Table 5.22.
Example 5.23 Use of ALL
Find all staff whose salary is larger than the salary of every member of staff at
branch B003.
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > ALL (SELECT salary
FROM Staff
WHERE branchNo = ‘B003’);
This is very similar to the last example. Again, we could use a subquery to find the maximum
salary of staff at branch B003 and then use an outer query to find all staff whose
salary is greater than this number. However, in this example we use the ALL keyword. The
result table is shown in Table 5.23.