NOT IN with NULL values
Posted by Kamran Agayev A. on February 13th, 2012
I strongly believe that you already know that NOT IN doesn’t take NULL values into account and those values are not displayed in the output
Check the following example:
[php]SQL> create table my_table (id number, name varchar2(10));
Table created.
SQL> insert into my_table values(1,’test1′);
1 row created.
SQL> insert into my_table values(2,null);
1 row created.
SQL> insert into my_table values(3,’test2′);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> select * from my_table;
ID NAME
———- ———-
1 test1
2
3 test2
SQL> select * from my_table where name not in (‘test2’);
ID NAME
———- ———-
1 test1
SQL>[/php]
For more information check the following links:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:442029737684