Share this page

Any-or-All Predicate

The any-or-all predicate takes the following form:

any-or-all-operator (subquery)

The subquery must have exactly one element in the target list of its outermost subselect (so that it evaluates to a set of single values rather than a set of rows). The any-or-all operator must be one of the following:

=ANY <>ANY <ANY <=ANY >ANY >=ANY | =ALL <>ALL <ALL <=ALL >ALL >=ALL |

The != (instead of <>) can also be used to specify not equal. Include a space between the comparison operator and the keyword ANY or ALL.

A predicate that includes the ANY operator is true if the specified comparison is true for at least one value y in the set of values returned by the subquery. If the subquery returns no rows, the ANY comparison is false.

A predicate that includes the ALL operator is true if the specified comparison is true for all values y in the set of values returned by the subquery. If the subquery returns no rows, the ALL comparison is true.

The operator =ANY is equivalent to the operator IN. For example:

SELECT ename

FROM employee

WHERE dept = ANY

(SELECT dno

FROM dept

WHERE floor = 3);

FROM employee

WHERE dept = ANY

(SELECT dno

FROM dept

WHERE floor = 3);

can be rewritten as:

SELECT ename

FROM employee

WHERE dept IN

(SELECT dno

FROM dept

WHERE floor = 3);

FROM employee

WHERE dept IN

(SELECT dno

FROM dept

WHERE floor = 3);

The operator SOME is a synonym for operator ANY. For example:

SELECT name

FROM employee

WHERE dept = SOME

(SELECT dno

FROM dept

WHERE floor = 3);

FROM employee

WHERE dept = SOME

(SELECT dno

FROM dept

WHERE floor = 3);