 |
|
|
 | Conditions and Predicates | BETWEEN Predicate |  |
generalized_comparison ::= expression relational_operator quantifier ( query_expression ) relational_operator ::= < | > | = | <= | >= | <>quantifier ::= SOME | ANY | ALL
The generalized comparison compares the value with the set of values from the query_expression result (subquery).
If the quantifier is SOME or ANY, then the generalized comparison will equal TRUE if the relation specified by the relational_operator is valid for at least one of the subquery result values. The result will be UNKNOWN when the relation is UNKNOWN for at least one of the subquery result values, otherwise it will be FALSE.
If the quantifier is ALL, then the generalized comparison will equal TRUE if the relation for all values of the subquery result is valid. The result will be UNKNOWN if the relation is valid or UNKNOWN for all values from the result, otherwise it will be FALSE.
If the subquery result is empty the generalized comparison will be FALSE for SOME or ANY quantifiers, and TRUE for ALL quantifier.
The subquery result must be scalar (contain only one word) and the type must be compatible with the expression type.
The ANY and SOME identifiers are fully equivalent. The notation =ANY is equivalent with the IN predicate.
Example:
Select those employees that have a salary higher than people in Prague.
SELECT TAB1.name,TAB1.salary
FROM People TAB1
WHERE salary > ALL (SELECT salary
FROM People
WHERE address LIKE '%Prague%') | Conditions and Predicates | BETWEEN Predicate | |