The “NOT IN” operator can cause confusion – especially when you have nulls lurking in critical columns, and most particularly where subqueries are involved
You can avoid most of the problems if you just remember that “NOT IN” is not the opposite of “IN”. Here’s an example to explain why.
colX in (value1, value2, value3)
colX = value1 or colX = value2 or colX = value3
When Oracle is evaluating this predicate, it only takes one of the three components to evaluate to true for the entire statement to evaluate to true.
colX NOT in (value1, value2, value3)
colX != value1 AND colX != value2 AND colX != value3
When Oracle is evaluating this predicate, every single one of the components must evaluate to true for the entire predicate to evaluate to true.
But if one of the values involved is a NULL, then that component evaluates to neither true nor false, it evaluates to null – so the whole expression evaluates to null.
So NULL values are allowed and don’t affect the outcome of IN predicates, but do affect the outcome of “NOT IN” predicates. You’ll also notice that in general IN predicates can short-circuit – the tests can stop on the first success (true), but the same is not true for NOT IN. [But see comment 2 and comment 4 below]
For a completely different version of the same argument, including symbolic logic, you might want to check a page on the Co-operative FAQ written in 2003.