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:
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);
Last modified date: 12/06/2024