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

Hi

If you have nulls dont use NOT IN!

On 2/1/07, JayDBA <jaykash_at_hotmail.com> wrote:
>
> 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 <http://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
>

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