From f.kuijten@organonteknika.nl Mon, 29 Oct 2001 07:54:34 -0800 From: "Kuijten, F. (Frank)" Date: Mon, 29 Oct 2001 07:54:34 -0800 Subject: RE: NOT IN with NULL-values Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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 __________ 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----- ] 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 --------- 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@organonteknika.nl -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kuijten, F. (Frank) INET: f.kuijten@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@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@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@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).