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
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kuijten, F. (Frank)
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
