Kamran Agayev's Oracle Blog

Oracle Certified Master

Archive for February 13th, 2012

NOT IN with NULL values

Posted by Kamran Agayev A. on 13th February 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

http://jonathanlewis.wordpress.com/2007/02/25/not-in/

Posted in Administration | No Comments »