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.