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);
can be rewritten as:
select ename
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);
Last modified date: 08/29/2024