SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—523725-004
6-91
EXISTS Predicate
Find all employees from other departments whose salary is less than the minimum
salary of employees (not in department 1500) that have a salary greater than the
average salary for department 1500:
SELECT first_name, last_name, deptnum, salary
FROM persnl.employee
WHERE deptnum <> 1500 AND
salary < (SELECT MIN (salary)
FROM persnl.employee
WHERE deptnum <> 1500 AND
salary > (SELECT AVG (salary)
FROM persnl.employee
WHERE deptnum = 1500));
FIRST_NAME LAST_NAME DEPTNUM SALARY
--------------- -------------------- ------- -----------
JESSICA CRINER 3500 39500.00
ALAN TERRY 3000 39500.00
DINAH CLARK 9000 37000.00
BILL WINN 2000 32000.00
MIRIAM KING 2500 18000.00
...
--- 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.
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 on page 6-108.
Examples of EXISTS
•
Find locations of employees with job code 300:
SELECT deptnum, location FROM persnl.dept D
WHERE EXISTS
[NOT] EXISTS subquery