FreeLists / oracle-l / RE: Is this a bug, or am I loosing it

["Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>]

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:36834798025416
 
Tom Kyte says: with the null values included, it is "not known" if the values 
are in the table or not. His explanation never really convinced me that this is 
the "correct" way to handle the query, but who am I to criticize Tom Kyte?

________________________________

De : oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] De la 
part de JayDBA
Envoyé : jeudi, 1. février 2007 11:47
À : oracle-l-freelists
Objet : Is this a bug, or am I loosing it


I am getting weird results from a NOT IN and NOT EXISTS query. Could this be 
because of NULL's or is it a bug?
 

XX is the child table. YY is the parent table. There are some NULL's in XX.PH 
column whereas there are no NULL's in YY.PHONE_NUMBER.

DEVDB 1{MYACCT}> desc xx
 Name                         Null?    Type
 ---------------------------- -------- ------------------
 PH                                    NUMBER(10)
 
DEVDB 1{MYACCT}> desc yy
 Name                         Null?    Type
 ---------------------------- -------- ------------------
 PHONE_NUMBER                 NOT NULL NUMBER(10)
 
DEVDB 1{MYACCT}> select count(*) from yy;
JAY SAYS {ENTER} ..
 
  COUNT(*)
__________
    688431
 
1 row selected.
 
Elapsed: 00:00:00.54
DEVDB 1{MYACCT}> select count(*) from xx;
JAY SAYS {ENTER} ..
 
  COUNT(*)
__________
    152892
 
1 row selected.
 
Elapsed: 00:00:00.36
DEVDB 1{MYACCT}> SELECT phone_number FROM yy WHERE phone_number NOT IN ( SELECT 
ph FROM xx);
 
no rows selected
 
Elapsed: 00:00:28.93
DEVDB 1{MYACCT}> 
DEVDB 1{MYACCT}> SELECT COUNT(phone_number) FROM yy WHERE NOT EXISTS (SELECT 1 
FROM xx WHERE ph = phone_number);
JAY SAYS {ENTER} ...
 
COUNT(PHONE_NUMBER)
___________________
             666936
 
1 row selected.
 
Elapsed: 00:00:01.46
DEVDB 1{MYACCT}> SELECT count(phone_number) FROM yy WHERE phone_number NOT IN ( 
SELECT ph FROM xx);
JAY SAYS {ENTER} ..
 
COUNT(PHONE_NUMBER)
___________________
                  0
 
1 row selected.
 
Elapsed: 00:00:31.83
 
DEVDB 1{MYACCT}> SELECT count(phone_number) FROM yy WHERE phone_number NOT IN ( 
SELECT ph FROM xx WHERE ph IS NOT NULL);
JAY SAYS {ENTER} ...
 
COUNT(PHONE_NUMBER)
___________________
             666936

 
Regards,
J