SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-98
Quantified Comparison Predicates
ANY | SOME
specifies that the predicate is true if the comparison is true for at least one row
selected by the table-subquery and specifies that the predicate is false if the
comparison is false for every row selected (or if table-subquery selects no
rows).
table-subquery
provides the values for the comparison. The number of values returned by the
row-value-constructor must be equal to the number of values specified by
the table-subquery, and the data types of values returned by the row-value-
constructor must be comparable to the data types of values returned by the
table-subquery. See Subquery on page 6-105.
Considerations for ALL, ANY, SOME
Let R be the result of the row-value-constructor, T the result of the table-
subquery, and RT a row in T.
Result of R comparison-op ALL T
If T is empty or if R comparison-op RT is true for every row RT in T, the
comparison-op ALL predicate is true.
If R comparison-op RT is false for at least one row RT in T, the comparison-op
ALL predicate is false.
Result of R comparison-op ANY T or R comparison-op SOME T
If T is empty or if R comparison-op RT is false for every row RT in T, the
comparison-op ANY predicate is false.
If R comparison-op RT is true for at least one row RT in T, the comparison-op
ANY predicate is true.
Examples of ALL, ANY, SOME
•
This predicate is true if the salary is greater than the salaries of all the employees
who have a jobcode of 420:
salary > ALL (SELECT salary
FROM persnl.employee
WHERE jobcode = 420)
Consider this SELECT statement using the preceding predicate:
SELECT empnum, first_name, last_name, salary
FROM persnl.employee
WHERE salary > ALL (SELECT salary
FROM persnl.employee
WHERE jobcode = 420);