RE: Is this a bug, or am I loosing it

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 1 Feb 2007 12:08:58 -0800
Message-ID: <22EC50E60931494FA666A8CF8260C45B5D9BED@ALVMBXW05.prod.quest.corp>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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)

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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 01 2007 - 14:08:58 CST