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@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

Other related posts: