Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: NOT IN with NULL-values

RE: NOT IN with NULL-values

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Mon, 29 Oct 2001 08:47:35 -0800
Message-ID: <F001.003B716C.20011029083018@fatcity.com> 






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: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 




        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_at_organonteknika.nl 

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

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

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).


Received on Mon Oct 29 2001 - 10:47:35 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US