NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
E-13
Examples—EXISTS
Examples—EXISTS
•
The following query searches for departments that have no engineers (job code
420):
SELECT DEPTNAME FROM PERSNL.DEPT D
WHERE NOT EXISTS
(SELECT JOBCODE
FROM PERSNL.EMPLOYEE E
WHERE E.DEPTNUM = D.DEPTNUM
AND JOBCODE = 420);
•
The following query searches 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);
•
The following query finds the locations of salespersons (employees with jobcode
300).
The EXISTS predicate contains a subquery that determines which locations have
salespersons. The subquery depends on the value of DEPT.DEPTNUM from the
outer query. In this case, the subquery must be evaluated for each row of the result
table where DEPT.DEPTNUM equals EMPLOYEE.DEPTNUM. Column
DEPT.DEPTNUM is an example of using an implicit correlation name as an outer
reference.
SELECT DEPTNUM, LOCATION
FROM DEPT
WHERE EXISTS (SELECT JOBCODE
FROM EMPLOYEE
WHERE DEPT.DEPTNUM = EMPLOYEE.DEPTNUM
AND JOBCODE = 300);