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 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
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).
