2010-03-05

not in null countdown

Just a reminder about not in and nulls. Maybe consider using not exists or anti join if any of the columns in not in list may be null. Or maybe one of the following might be the result you want.


SQL> create table nm as
2 with le as (select level ev from dual connect by level<4)
3 select l.ev e,e.ev v from le l, le e;

Table created.

SQL> select count(*) from nm;

COUNT(*)
----------
9

SQL> select count(*) from nm where (e,v) not in ((1,1));

COUNT(*)
----------
8

SQL> select count(*) from nm where (e,v) not in ((1,1),(2,2));

COUNT(*)
----------
7

SQL> select count(*) from nm where (e,v) not in ((1,null));

COUNT(*)
----------
6

SQL> select count(*) from nm where (e,v) not in ((1,1),(2,null));

COUNT(*)
----------
5

SQL> select count(*) from nm where (e,v) not in ((null,1),(2,null));

COUNT(*)
----------
4

SQL> select count(*) from nm where (e,v) not in ((1,null),(2,null));

COUNT(*)
----------
3

SQL> select count(*) from nm where (e,v) not in ((1,null),(2,null),(3,3));

COUNT(*)
----------
2

SQL> select count(*) from nm where (e,v) not in ((null,1),(null,2),(1,3),(2,3));

COUNT(*)
----------
1

SQL> select count(*) from nm where (e,v) not in ((null,null),(1,1));

COUNT(*)
----------
0

SQL> select count(*)
2 from nm m
3 where not exists (select n
4 from (select null n from dual) d
5 where m.v = d.n
6 and m.e = d.n);

COUNT(*)
----------
9

SQL> select count(*)
2 from nm m
3 left outer join (select 10 e, null n from dual) d
4 on m.e=d.e
5 where d.n is null;

COUNT(*)
----------
9

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.