SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
E-12
EXISTS Predicate
EXISTS Predicate
EXISTS is a predicate that determines whether any rows satisfy conditions in a
subquery. The EXISTS predicate evaluates to true if the subquery finds at least one
row that satisfies the search condition.
In an EXISTS predicate, the result of subquery can be a table of more than one
column.
An EXISTS subquery is typically correlated with an outer query.
Examples—EXISTS

This 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);

This 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);

This 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);
[ NOT ] EXISTS subquery