SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-37
EXISTS Predicate
SELECT DEPT_NUM, LAST_NAME, FIRST_NAME
FROM EMPLOYEE
WHERE DEPT_NUM IN (SELECT DEPT_NUM
FROM DEPT
WHERE DEPT_LOC BETWEEN 900 AND 999) ;
The query returns this result:
DEPT_NUM LAST_NAME FIRST_NAME
-------- --------- ----------
6400 Nakagawa Etsuro
6480 Nakamura Eichiro
6410 Murakami Kazuo
6400 Smithson Richard
--- 4 row(s) selected.
The list provided by the subquery is a result of the location of the department. (If the
database contained employees in departments 6420, 6440, or 6470, SQL would have
also selected these departments and their employees.)
If the IN predicate compares two character strings, these guidelines apply to the use of
character sets:
•
The same character set must be associated with each of the two character
expressions. Any character data type is compatible with other character data types
as long as both have the same associated character set.
•
If neither character expression is a column with an associated collation, a binary
comparison is used for all comparisons.
•
If the character expressions have two different lengths, the shorter string is filled on
the right with spaces to match the length of the longer string. Spaces are used
whether or not a single-byte or double-byte character set is associated with the
expression.
EXISTS Predicate
An EXISTS predicate always involves a subquery. The EXISTS predicate evaluates to
true if the subquery selects a row that satisfies the specified condition or conditions.
This statement queries the database to select information about all those employees
who are identified as managers:
SELECT DISTINCT EMP_ID, LAST_NAME, FIRST_NAME
FROM EMPLOYEE EMP1
WHERE EXISTS (SELECT MGR_ID
FROM EMPLOYEE EMP2
WHERE EMP2.MGR_ID = EMP1.EMP_ID) ;
The query returns this result: