SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-92
IN Predicate
Examples of IN
•
Find those employees whose EMPNUM is 39, 337, or 452:
SELECT last_name, first_name, empnum
FROM persnl.employee
WHERE empnum IN (39, 337, 452);
LAST_NAME FIRST_NAME EMPNUM
-------------------- --------------- ------
CLARK DINAH 337
SAFFERT KLAUS 39
--- 2 row(s) selected.
•
Find those items in PARTS whose part number is not in the PARTLOC table:
SELECT partnum, partdesc
FROM sales.parts
WHERE partnum NOT IN
(SELECT partnum
FROM invent.partloc);
PARTNUM PARTDESC
------- ------------------
186 186 MegaByte Disk
--- 1 row(s) selected.
•
Find those items (and their suppliers) in PARTS that have a supplier in the
PARTSUPP table:
SELECT P.partnum, P.partdesc, S.suppnum, S.suppname
FROM sales.parts P,
invent.supplier S
WHERE P.partnum, S.suppnum IN
(SELECT partnum, suppnum
FROM invent.partsupp);
•
Find those employees in EMPLOYEE whose last name and job code match the list
of last names and job codes:
SELECT empnum, last_name, first_name
FROM persnl.employee
WHERE (last_name, jobcode) IN
(VALUES ('CLARK', 500), ('GREEN', 200));