ANY

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, the ANY keyword can be used with the comparison operators:

     =   ¬=   >   >=   <   <=

With ANY, at least one value in the set returned must be satisfied.

IN can be used in a subquery in place of = ANY, and SOME is a synonym for ANY.

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

The following query produces a list of employees who work in the Eastern division. First, the subquery finds the department numbers in the Eastern division. Then, the main query finds the employees who work in any of these departments.

This query:

SELECT NAME, ID
FROM Q.STAFF
WHERE DEPT = ANY
 (SELECT DEPTNUMB FROM Q.ORG WHERE DIVISION='EASTERN')

Produces a list of names and IDs of employees who work in the Eastern division.

The keyword ANY was used in this query because there are multiple departments in the Eastern division. If ALL is used instead of ANY, the result is an empty set. No employee works in all the departments of the Eastern division.