Kamran Agayev's Oracle Blog

Oracle Certified Master

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

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

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>