ALL

A subquery generally returns only one value. However, it is possible for a query to return a set of values.

To permit a query to return a set of values, rather than an individual value, use the ALL keyword with the following comparison operators:

     =   ¬=   >   >=   <   <=

With ALL, each value in the returned set must be satisfied.

The symbol ¬= is an alternative symbol for < > (not equal to). It is an American National Standards Institute (ANSI) SQL operator. If you are using remote data access, the preferred symbol is < >.

The following query produces a report that lists the department with the highest average salary. Use of the ALL keyword specifies that the department selected by the main SELECT statement must have an average salary equal to or greater than all average salaries of other departments.

SELECT DEPT, AVG(SALARY) FROM Q.STAFF
GROUP BY DEPT
HAVING AVG(SALARY) >= ALL
      (SELECT AVG(SALARY) FROM Q.STAFF
       GROUP BY DEPT)

Operators other than the equal sign (=) can be used with the ALL keyword. If any of the results produced by the subquery are NULL, the result of the condition with ALL is unknown.