The quantified comparison predicate compares the specified value ( x ) with the values returned by a subquery. If ALL is specified, then the value is tested for its appearance in all records returned by the subquery. If either ANY or SOME is specified (these two keywords are equivalent), the value is tested for its appearance in at least one of the records returned by the subquery. If no keyword is supplied, the subquery is interpreted as a scalar subquery, which must return exactly one row and one value for noncorrelated subqueries, or one row and value per row of the outer table for correlated subqueries.
If the value x is null or the subquery returns no rows, the predicate will evaluate to UNKNOWN. There is an exception in the case of ALL, where, if the subquery returns no rows, the predicate will evaluate to TRUE.
The SELECT statement (subquery) may only return values from a single column, or in the case of a scalar subquery, only a single value.
Example
SELECT * FROM inventory
WHERE cost >= ALL (SELECT I.price
FROM items I, orders O
WHERE I.o_no = O.o_no);
In the above statement, each cost value in the inventory table is tested against all the price values returned by the subquery. Since the operator/keyword is >= ALL, if a particular row�s cost value is greater than every single value returned by the subquery, the value expression returns TRUE, and that row is included in the result set.