Neoview SQL Reference Manual (R2.3)

...
--- 35 row(s) selected.
The first subquery of this query determines the minimum salary of employees from other
departments whose salary is greater than the average salary for department 1500. The main
query then finds the names of employees who are not in department 1500 and whose salary
is less than the minimum salary determined by the first subquery.
EXISTS Predicate
The EXISTS predicate determines whether any rows are selected by a subquery. If the subquery
finds at least one row that satisfies its search condition, the predicate evaluates to true. Otherwise,
if the result table of the subquery is empty, the predicate is false.
[NOT] EXISTS subquery
subquery
specifies the operand of the predicate. A subquery is a query expression enclosed in
parentheses. An EXISTS subquery is typically correlated with an outer query. See “Subquery”
(page 260).
Examples of EXISTS
• Find locations of employees with job code 300:
SELECT deptnum, location FROM persnl.dept D
WHERE EXISTS
(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
248 SQL Language Elements