SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-36
IN Predicate
The query returns this result:
EMP_ID LAST_NAME FIRST_NAME DEPT_NUM MGR_ID
------ --------- ---------- -------- ------
2705 Simpson Travis 7600 6554
2906 Nakagawa Etsuro 6400 6554
--- 2 row(s) selected.
IN Predicate
You can use an IN predicate to compare the value of an expression with one or more
values of another expression. There are two types of IN predicates, classified
according to the comparison expression:
•
An IN predicate with a list of values as its comparison expression
•
An IN subquery predicate
If the expression is a list of values, the predicate is specified as shown by this example:
SELECT DEPT_NUM, LAST_NAME, FIRST_NAME
FROM EMPLOYEE
WHERE DEPT_NUM IN (6400, 6410, 6470, 6480) ;
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.
SQL transforms the list of values associated with an IN predicate into a search
condition involving predicates connected by one or more OR operators. The IN
predicate in the previous example is transformed into this search condition:
WHERE DEPT_NUM = 6400
OR DEPT_NUM = 6410
OR DEPT_NUM = 6470
OR DEPT_NUM = 6480
Therefore, the IN predicate that uses a list of values provides a convenient way for
formulating OR predicates.
The second type of IN predicate is provided by using a subquery, instead of a list of
values, as follows: