Neoview SQL Reference Manual (R2.2)

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” (page 250).
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);
The inner query providing the comparison values yields these results:
SELECT salary
FROM persnl.employee
WHERE jobcode = 420;
SALARY
-----------
33000.00
36000.00
18000.10
--- 3 row(s) selected.
The SELECT statement using this inner query yields these results. The salaries listed are
greater than the salary of every employees with jobcode equal to 420—that is, greater than
$33,000.00, $36,000.00, and $18,000.10:
SELECT empnum, first_name, last_name, salary
FROM persnl.employee
WHERE salary > ALL (SELECT salary
FROM persnl.employee
WHERE jobcode = 420);
Predicates 243