3. Elements of SQL Statements : Predicates in SQL : Any-or-All Predicate
 
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);
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 name
FROM employee
WHERE dept = SOME
       (SELECT dno
       FROM dept
       WHERE floor = 3);