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.
membership_condition::=
membership_condition
Table 5-7 lists the membership conditions.
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.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|