| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: NOT IN with NULL-values
Sorry Frank,
I tried that without null. I cross checked and found results for not in similar to yours, as explained by other list members. null is not equal to another value, or null itself.
rgds
amar
-----Original Message-----
From: Kuijten, F. (Frank) [mailto:f.kuijten_at_organonteknika.nl]
Sent: Monday, October 29, 2001 6:35 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: NOT IN with NULL-values
Amar,
Thanks.
One question : Do you have a NULL value in the 'am91' table ?
Because that's where my problem is.
Without the NULL value, all queries are giving the expected results.
Greetings,
Frank
-----Original Message-----
Sent: maandag 29 oktober 2001 14:50
To: Multiple recipients of list ORACLE-L
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
-select * from am91; ---same as fk_ldm2
ID
-select * from am90
2 minus
3 select * from am91;
ID
-select * from am90
2 where id not in (select * from am91);
ID
-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
-select * from am90 a
2 where not exists (select * from am91 where id = a.id);
ID
rgds
amar
-----Original Message-----
<mailto:f.kuijten_at_organonteknika.nl> ]
Sent: Monday, October 29, 2001 12:45 PM
To: Multiple recipients of list ORACLE-L
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
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
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
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com <http://www.orafaq.com> -- Author: Kuijten, F. (Frank)Received on Tue Oct 30 2001 - 06:13:43 CST
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). -- 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).
 |
 |