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

["LS Cheng" <exriscer@xxxxxxxxx>]

Hi

If you have nulls dont use NOT IN!




On 2/1/07, JayDBA <jaykash@xxxxxxxxxxx> 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