Membership Conditions

A membership condition tests for membership in a list or subquery.

membership_condition::=

Text description of membership_condition

Table 5-7 lists the membership conditions.

Table 5-7 Membership Conditions

Condition	Operation	Example
IN	"Equal to any member of" test. Equivalent to "= ANY".	SELECT * FROM employees WHERE job_id IN ('PU_CLERK','SH_CLERK'); SELECT * FROM employees WHERE salary IN (SELECT salary FROM employees WHERE department_id =30);
NOT IN	Equivalent to "!=ALL". Evaluates to FALSE if any member of the set is NULL.	SELECT * FROM employees WHERE salary NOT IN (SELECT salary FROM employees WHERE department_id = 30); SELECT * FROM employees WHERE job_id NOT IN ('PU_CLERK', 'SH_CLERK');

If any item in the list following a NOT IN operation is null, all rows evaluate to UNKNOWN (and no rows are returned). For example, the following statement returns the string 'TRUE' for each row:

SELECT employee_id FROM employees
   WHERE department_id IN (10, 20);

SELECT employee_id FROM employees
   WHERE department_id NOT IN (10, 20);

However, the following statement returns no rows:

SELECT employee_id FROM employees
    WHERE department_id NOT IN (10, NULL); 

The above example returns no rows because the WHERE clause condition evaluates to:

department_id != 10 AND department_id != 20 AND department_id != 
null 

Because all conditions that compare a null result in a null, the entire expression results in a null. This behavior can easily be overlooked, especially when the NOT IN operator references a subquery.