Using IN

Go Up to Using Comparison Operators in Expressions

IN tests that a known value equals at least one value in a list of values. A list is a set of values separated by commas and enclosed by parentheses. The values in the list must be parenthesized and separated by commas. If the value being compared to a list of values is NULL, IN returns Unknown.

The syntax for IN is:

<value> [NOT] IN (<value> [, <value> ...])

For example, the following cursor declaration retrieves the names of all employees in the accounting, payroll, and human resources departments:

EXEC SQL
DECLARE ACCT_PAY_HR CURSOR FOR
SELECT DEPARTMENT, LAST_NAME, FIRST_NAME, EMP_NO
FROM EMPLOYEE EMP, DEPTARTMENT DEP
WHERE EMP.DEPT_NO = DEP.DEPT_NO AND
 DEPARTMENT IN ('Accounting', 'Payroll', 'Human Resources')
GROUP BY DEPARTMENT;

Use NOT IN to test that a value does not occur in a set of specified values. For example, the following cursor declaration retrieves the names of all employees not in the accounting, payroll, and human resources departments:

EXEC SQL
DECLARE NOT_ACCT_PAY_HR CURSOR FOR
SELECT DEPARTMENT, LAST_NAME, FIRST_NAME, EMP_NO
FROM EMPLOYEE EMP, DEPTARTMENT DEP
WHERE EMP.DEPT_NO = DEP.DEPT_NO AND
DEPARTMENT NOT IN ('Accounting', 'Payroll',
'Human Resources')
GROUP BY DEPARTMENT;

IN can also be used to compare a value against the results of a subquery. For example, the following cursor declaration retrieves all cities in Europe:

EXEC SQL
DECLARE NON_JFG_CITIES CURSOR FOR
SELECT C.COUNTRY, C.CITY, C.POPULATION
FROM CITIES C
WHERE C.COUNTRY NOT IN (SELECT O.COUNTRY FROM COUNTRIES O
WHERE O.CONTINENT <> 'Europe')
GROUP BY C.COUNTRY;

For more information about subqueries, see Using Subqueries.