Using ALL

Go Up to Using Comparison Operators in Expressions

ALL tests that a value is true when compared to every value in a list returned by a subquery. The complete syntax for ALL is:

<value> <comparison_operator> ALL (<subquery>)

For example, the following cursor retrieves information about employees whose salaries are larger than that of the vice president of channel marketing:

EXEC SQL
DECLARE MORE_THAN_VP CURSOR FOR
SELECT LAST_NAME, FIRST_NAME, SALARY
FROM EMPLOYEE
WHERE SALARY > ALL (SELECT SALARY FROM EMPLOYEE
WHERE DEPT_NO = 7734);

ALL returns Unknown if the subquery returns a NULL value. It can also return Unknown if the value to be compared is NULL and the subquery returns any non-NULL data. If the value is NULL and the subquery returns an empty set, ALL evaluates to True.

For more information about subqueries, see Using Subqueries.

Advance To:

• Using ANY and SOME