Share this page

Any-or-All Predicate

An any‑or‑all predicate takes the 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 =all

<>any <>all

<any <all

<=any <=all

>any >all

>=any >=all

Let $ denote any one of the comparison operators =, <>, <, <=, >, >=. Then the predicate:

x $any (subquery)

evaluates to true if the comparison predicate:

x $ y

is true for at least one value y in the set of values represented by subquery. If the subquery is empty, the $any comparison fails (evaluates to false).

Likewise, the predicate:

x $all (subquery)

is true if the comparison predicate:

x $ y

is true for all values y in the set of values represented by subquery. If the subquery is empty, the $all comparison evaluates to 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 ename

from employee

where dept = some

(select dno from dept where floor = 3);

from employee

where dept = some

(select dno from dept where floor = 3);