Using ANY and SOME

Go Up to Using Comparison Operators in Expressions

ANY and SOME test that a value is true if it matches any value in a list returned by a subquery. The complete syntax for ANY is:

<value> <comparison_operator> ANY | SOME (<subquery>)

For example, the following cursor retrieves information about salaries that are larger than at least one salary in the channel marketing department:

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

ANY and SOME return Unknown if the subquery returns a NULL value. They 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, ANY and SOME evaluate to False.

For more information about subqueries, see Using Subqueries.

Advance To:

• Using EXISTS