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

From: Mercadante, Thomas F \(LABOR\) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Thu, 1 Feb 2007 15:26:34 -0500
Message-ID: <ABB9D76E187C5146AB5683F5A07336FFE08AD9@EXCNYSM0A1AJ.nysemail.nyenet>

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

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,

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

Received on Thu Feb 01 2007 - 14:26:34 CST