Re: Is this a bug, or am I loosing it

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: jaykash@xxxxxxxxxxx
  • Date: Thu, 01 Feb 2007 14:20:49 -0700

A more detailed explanation of what is happening:

create table parent( n1 number not null);
create table child(n1 number);
insert into parent values(1);
insert into parent values(2);
insert into child values(1);
commit;
SQL> select * from parent;

        N1
----------
         1
         2

SQL> select * from child;

        N1
----------
         1

SQL> select * from parent p where p.n1 not in (select c.n1 from child c);

        N1
----------
         2
SQL> select * from parent p where not exists (select 1 from child c where c.n1 = p.n1); 

        N1
----------
         2

insert into child values(NULL);

SQL> select * from parent p where p.n1 not in (select c.n1 from child c);

no rows selected

-- the subquery "select c.n1 from child c"  returns 1 and NULL.
-- 1 not in (1,NULL)  <=>  not (1 = 1 or 1 = NULL)  <=> (1 <> 1 and 1 <> NULL)
-- So for 1 the predicate is false and thus 1 is not in the resultset - same as above before the insert of NULL. 
-- 2 not in (1,NULL)  <=>  not (2 = 1 or 2 = NULL)  <=> (2 <> 1 and 2 <> NULL)
-- So for 2 the predicate is now false as well since 2 <> NULL is false and thus 2 will no longer be in the resultset -- neither will any possible value for p.n1 including NULL (if we'd allow nulls).
SQL> select * from parent p where not exists (select 1 from child c where c.n1 = p.n1); 

        N1
----------
         2

Nothing changes here.
As LS Cheng said "If you have nulls dont use NOT IN!"

At 12:47 PM 2/1/2007, JayDBA wrote:
I am getting weird results from a NOT IN and NOT EXISTS query. Could this be because of NULL's or is it a bug? 

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com 
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email ______________________________________________________________________

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 02-2007