November 29, 2006: Beware of NOT IN and NULLS

Beware of select statements with a NOT IN where nulls are part in the NOT IN clause.

create table not_in_and_null_t (
   col_n1 number,
   col_n2 number
);

The table is filled:

insert into not_in_and_null_t values (    4,    7);
insert into not_in_and_null_t values (    7,    1);
insert into not_in_and_null_t values (    8,    2);

I want to find all col_n1 values in not_in_and_null_t that don't exist in col_2:

select col_n1 from not_in_and_null_t
  where col_n1 not in (select col_n2 from not_in_and_null_t);

    COL_N1
----------
         4
         8

Correctly, 4 and 8 are returned. 7 is not because there is a record whose col_n2 value is 7.

I insert another record into not_in_and_null_t, this time with col_n2 being null.

insert into not_in_and_null_t values (   42, null);

The same SQL statement:

select col_n1 from not_in_and_null_t
  where col_n1 not in (select col_n2 from not_in_and_null_t);

no rows selected

That's certainly not what I expected when I first came across a similar query, but still correct considering that null = some_value is always false. This query returns a row if col_n1 != 7 and col_n1 != 1 and col_n1 != 2 and col_n1 != null. The last condition can never be met, so no row can be returned.

In order to return rows, the query should be changed to

select col_n1 from not_in_and_null_t
  where col_n1 not in (select col_n2 from not_in_and_null_t
                        where col_n2 is not null
                      );

    COL_N1
----------
         4
         8
        42

The IN operator ors the conditions rather than ands them, so no special check for not in needs be done (one true is enough for the entire condition chain to be true):

select col_n1 from not_in_and_null_t
  where col_n1 in (select col_n2 from not_in_and_null_t);

    COL_N1
----------
         7


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger

November 29, 2006: Beware of NOT IN and NULLS

More on Oracle