RE: NOT IN with NULL-values
Title: RE: NOT IN with NULL-values Yes I had a null value in am91. rgds amar -Original Message- From: Kuijten, F. (Frank) [mailto:[EMAIL PROTECTED]] 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 __ 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- mailto:[EMAIL PROTECTED] ] 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 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com 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). -- 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).
RE: NOT IN with NULL-values
Title: 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:[EMAIL PROTECTED]] 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 __ 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- mailto:[EMAIL PROTECTED] ] 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 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com 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). -- 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).
RE: NOT IN with NULL-values
Title: RE: NOT IN with NULL-values 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:[EMAIL PROTECTED]] 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 [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).
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 __ 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- mailto:[EMAIL PROTECTED] ] 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 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com 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). -- 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).
RE: NOT IN with NULL-values
Frank, It sound like you are confusing the value and concepts of what 'null' means. For example, in your fk_ldm2 table, the record that contains a null value in the first column: If you try and select the 'null' value and compare it with something, it will not work because a 'null' does not equal anything, including another null. In your three selects: (1)SQL select * from fk_ldm1 2 where id not in (select * from fk_ldm2); no rows selected (2)SQL select * from fk_ldm1 2 where not exists (select * from fk_ldm2); no rows selected (3)SQL select * from fk_ldm1 2 where id not in (select * from fk_ldm2 where id is not null); ID - 3 #1 does not work because the where clause will not return a value of true - the 'id' (value 3) cannot be compared to the 'null' value and return a 'true' value. Because the 'null' exists, it throws the whole query away - as evidenced by #3 where you discard null values. #2 does not work because you did not co-relate the two queries together. Change it as suggested to: select * from fk_ldm1 where not exists (select * from fk_ldm2 where fk_ldm1.id = fk_ldm2.id); Notice that this now looks only for values that exist in the fk_ldm1 table, thus it will not search and select the null record in fk_ldm2 table causing the same null comparison problem as above. Remember, a Null does not equal anything, and when compared with something, produces undesirable results (mostly confusing). You need to always provide for null processing either by using the NVL function or some other way to make sure things work properly. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, October 29, 2001 9:35 AM To: Multiple recipients of list ORACLE-L 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- mailto:[EMAIL PROTECTED] ] 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 [EMAIL PROTECTED] -- Please see the official ORACLE-L
RE: NOT IN with NULL-values
Frank, A NOT IN evaluates to FALSE if *any* member of the set is NULL. http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/ a85397/operator.htm#1003933 Also scroll up just a tad on that link and look at the comments for NOT IN in table 3.4. This is a subtlety that many folks overlook until it bites them on the tail. With regards to your NOT EXISTS, you didn't include correlation between the two tables (which Amar pointed out with the example). Try this: SQL select * from fk_ldm1 a 2 where not exists (select * from fk_ldm2 b where a.id = b.id); ID -- 3 For each row in fk_ldm1 the sub-query is evaluated. And since you did not include correlation, the sub-query will always return a row(s) (there's no criteria on it), thus making the NOT EXISTS condition FALSE for each row in fk_ldm1. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Kuijten, F. (Frank) Sent: Monday, October 29, 2001 8:35 AM 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins 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).
