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

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-89
EXISTS Predicate
(SELECT jobcode FROM persnl.employee E
WHERE D.deptnum = E.deptnum AND jobcode = 300);
DEPTNUM LOCATION
------- -------------
3000 NEW YORK
3100 TORONTO
3200 FRANKFURT
3300 LONDON
3500 HONG KONG
--- 5 row(s) selected.
In the preceding example, the EXISTS predicate contains a subquery that
determines which locations have employees with job code 300. The subquery
depends on the value of D.DEPTNUM from the outer query and must be evaluated
for each row of the result table where D.DEPTNUM equals E.DEPTNUM. The
column D.DEPTNUM is an example of an outer reference.
•
Search for departments that have no employees with job code 420:
SELECT deptname FROM persnl.dept D
WHERE NOT EXISTS
(SELECT jobcode FROM persnl.employee E
WHERE D.deptnum = E.deptnum AND jobcode = 420);
DEPTNAME
------------
FINANCE
PERSONNEL
INVENTORY
...
--- 11 row(s) selected.
•
Search for parts with less than 20 units in the inventory:
SELECT partnum, suppnum
FROM invent.partsupp PS
WHERE EXISTS
(SELECT partnum FROM invent.partloc PL
WHERE PS.partnum = PL.partnum AND qty_on_hand < 20);
PARTNUM SUPPNUM
------- -------
212 1
212 3
2001 1
2003 2
...
--- 18 row(s) selected.