|Oracle9i SQL Reference
Release 1 (9.0.1)
Part Number A90125-01
Conditions, 5 of 11
A membership condition tests for membership in a list or subquery.
Table 5-7 lists the membership conditions.
"Equal to any member of" test. Equivalent to "
Equivalent to "!=
If any item in the list following a
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:
The above example returns no rows because the
WHERE clause condition evaluates to:
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
IN operator references a subquery.