RE: NOT IN with NULL-values

From: Amar Kumar Padhi <TS2017_at_emirates.com>
Date: Mon, 29 Oct 2001 05:37:55 -0800
Message-ID: <F001.003B6D90.20011029055017@fatcity.com>

I tried your example at my end. The both the NOT IN stmts do give me the result. NOT EXITS is functioning properly as there are records existing in sub-query. check the last stmt below for  this.  I see no bugs or issues. check this:

    -select * from am90;  ---same as fk_ldm1         ID

---

         1
         2
         3
         4

    -select * from am91;  ---same as fk_ldm2         ID

---

         1
         2
         4


    -select * from am90

  2  minus
  3  select * from am91;

        ID

---

         3

    -select * from am90
  2  where id not in (select * from am91);

        ID

---

         3

    -select * from am90
  2  where not exists (select * from am91);

no rows selected

    -select * from am90
  2  where id not in (select * from am91 where id is not null);

        ID

---

         3

    -select * from am90 a
  2  where not exists (select * from am91 where id = a.id);

        ID

---

         3

rgds
amar

-----Original Message-----
From: Kuijten, F. (Frank) [mailto:f.kuijten_at_organonteknika.nl] Sent: Monday, October 29, 2001 12:45 PM
To: Multiple recipients of list ORACLE-L Subject: NOT IN with NULL-values

Hello,

I'm running the SQL-statements below :

SQL> create table fk_ldm1 (id number(10) not null); Table created.
SQL> insert into fk_ldm1 values(1);
1 row created.
SQL> insert into fk_ldm1 values(2);
1 row created.
SQL> insert into fk_ldm1 values(3);
1 row created.
SQL> insert into fk_ldm1 values(4);
1 row created.

SQL> create table fk_ldm2 (id number(10)); Table created.
SQL> insert into fk_ldm2 values(1);
1 row created.
SQL> insert into fk_ldm2 values(2);
1 row created.
SQL> insert into fk_ldm2 values(4);
1 row created.
SQL> insert into fk_ldm2 values(NULL);
1 row created.

As you can see, value '3' is not in 'fk_ldm2' and value 'NULL' is not in 'fk_ldm1'.

Now for the following selects :
SQL> select * from fk_ldm1
  2  minus
  3  select * from fk_ldm2;

       ID

---

        3

SQL> select * from fk_ldm1
  2  where id not in (select * from fk_ldm2);
no rows selected

SQL> select * from fk_ldm1
  2  where not exists (select * from fk_ldm2);
no rows selected

SQL> select * from fk_ldm1
  2  where id not in (select * from fk_ldm2 where id is not null);
       ID

---

        3

I had expected to see value '3' appear in all selects. It has something to do with the NULL value in 'fk_ldm2', but I've got no explanation for this.

Why won't value '3' appear as soon as a NULL value is used ???

(It's not a bug as this behaviour is the same in 7.1.5, 8.0.6 and 8.1.6)

Greetings,
Frank

---

Frank Kuijten
DBA/Developer
Organon Teknika bv
P.O. Box 84
5280 AB  Boxtel
Republic of The Netherlands
+31 411 654265
f.kuijten_at_organonteknika.nl

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kuijten, F. (Frank)


  INET: f.kuijten_at_organonteknika.nl



Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Oct 29 2001 - 07:37:55 CST