4.2.4. Quantified Subquery Predicates
A quantifier is a logical operator that sets the number of objects for which this assertion is true. It is not a numeric quantity, but a logical one that connects the assertion with the full set of possible objects. Such predicates are based on logical universal and existential quantifiers that are recognised in formal logic.
In subquery expressions, quantified predicates make it possible to compare separate values with the results of subqueries; they have the following common form:
<value expression> <comparison operator> <quantifier> <subquery>
ALL
Available
DSQL, PSQL, ESQL
Syntax
<value> <op> ALL (<select_stmt>)
When the ALL
quantifier is used, the predicate is TRUE if every value returned by the subquery satisfies the condition in the predicate of the main query.
Example
Show only those clients whose ratings are higher than the rating of every client in Paris.
SELECT c1.*
FROM Customers c1
WHERE c1.rating > ALL
(SELECT c2.rating
FROM Customers c2
WHERE c2.city = 'Paris')
If the subquery returns an empty set, the predicate is TRUE for every left-side value, regardless of the operator. This may appear to be contradictory, because every left-side value will thus be considered both smaller and greater than, both equal to and unequal to, every element of the right-side stream. Nevertheless, it aligns perfectly with formal logic: if the set is empty, the predicate is true 0 times, i.e., for every row in the set. |
ANY
and SOME
Available
DSQL, PSQL, ESQL
Syntax
<value> <op> {ANY | SOME} (<select_stmt>)
The quantifiers ANY
and SOME
are identical in their behaviour. Apparently, both are present in the SQL standard so that they could be used interchangeably in order to improve the readability of operators. When the ANY
or the SOME
quantifier is used, the predicate is TRUE if any of the values returned by the subquery satisfies the condition in the predicate of the main query. If the subquery would return no rows at all, the predicate is automatically considered as FALSE.
Example
Show only those clients whose ratings are higher than those of one or more clients in Rome.
SELECT *
FROM Customers
WHERE rating > ANY
(SELECT rating
FROM Customers
WHERE city = 'Rome')