Take care with NOT IN Oracle

Below data copied from (http://jonathanlewis.wordpress.com/2007/02/25/not-in/)



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)
means

      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)
means

      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.