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

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 01 Feb 2007 14:20:49 -0700
Message-Id: <20070201212143.88B8E5D124C@turing.freelists.org>

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

---

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 01 2007 - 15:20:49 CST