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

From: "Mercadante, Thomas F \(LABOR\)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
To: <jaykash@xxxxxxxxxxx>, "oracle-l-freelists" <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 1 Feb 2007 15:26:34 -0500

Jay,

 

This looks perfectly correct to me.

 

"NOT IN" attempts to perform a join and null does not equal null so you
get no rows.

 

"NOT EXISTS" is a logical check that returns true if the sub query
returns no rows, thus you get a true count.

 

Say Good Night Gracy.

 

Tom

 

 

 


--------------------------------------------------------
This transmission may contain confidential, proprietary, or privileged 
information which is intended solely for use by the individual or entity to 
whom it is addressed.  If you are not the intended recipient, you are hereby 
notified that any disclosure, dissemination, copying or distribution of this 
transmission or its attachments is strictly prohibited.  In addition, 
unauthorized access to this transmission may violate federal or State law, 
including the Electronic Communications Privacy Act of 1985.  If you have 
received this transmission in error, please notify the sender immediately by 
return e-mail and delete the transmission and its attachments.


________________________________


From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of JayDBA
Sent: Thursday, February 01, 2007 2:47 PM
To: oracle-l-freelists
Subject: 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

References:
- Is this a bug, or am I loosing it
  - From: JayDBA

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

Other related posts: