Quantified Predicates
The quantified predicates take one of the following forms:
x quantified_compare (subquery)
(x,…) quantified_compare (subquery)
The subquery must have the same number of elements in the target list of its outermost subselect as is being compared with. In other words, if x is not a row valued expression then there must be just a single column returned. If x is row valued, then there must be the same number of columns returned as are there are elements in the row.
The supported quantified_compare operators are:
The != (instead of <>) can also be used to specify not equal. Include a space between the comparison operator and the keyword ANY or ALL. IS DISTINCT FROM and its negated form cannot be used with ANY and 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. Note that if any of the subquery results are NULL, then the result set will be empty.
The operator =ANY is equivalent to the operator IN; <>ALL is equivalent to NOT 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: 01/30/2023