SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-38
Quantified Predicates
EMP_ID LAST_NAME FIRST_NAME
------ --------- ----------
2705 Simpson Travis
2906 Nakagawa Etsuro
3598 Nakamura Eichiro
9069 Smith John
--- 4 row(s) selected.
Quantified Predicates
A quantified predicate always involves a subquery. You can use a quantified predicate
to compare an expression that applies to the outer query with all, any, or some of the
values returned by a subquery predicate.
If you specify ALL, a row selected by the outer query appears in the result if the
quantified predicate is true for each and every value selected by the subquery.
This example queries the database to select information about employees whose
salary is greater than or equal to the salary of all other employees:
SELECT LAST_NAME, FIRST_NAME, SALARY
FROM EMPLOYEE
WHERE SALARY >= ALL (SELECT SALARY
FROM EMPLOYEE) ;
The query returns this result:
LAST_NAME FIRST_NAME SALARY
--------- ---------- --------
Nakagawa Etsuro 72000.00
--- 1 row(s) selected.
If you specify SOME or ANY, a row selected by the outer query appears in the result if
the quantified predicate is true for at least one value selected by the subquery, as
follows:
SELECT LAST_NAME, FIRST_NAME, DEPT_NUM
FROM EMPLOYEE
WHERE DEPT_NUM = ANY (SELECT DEPT_NUM
FROM DEPT
WHERE DEPT_LOC BETWEEN 900 AND 999) ;
The query returns this result:
LAST_NAME FIRST_NAME DEPT_NUM
--------- ---------- --------
Nakagawa Etsuro 6400
Nakamura Eichiro 6480
Murakami Kazuo 6410
Smithson Richard 6400
--- 4 row(s) selected.