Re: Bug with "NOT IN" subqueries

DOH! I can't believe I didn't figure that out. You'd think after all of these years, I would have run into this one before.

Thanks for you help, especially since it saved me from opening a TAR on it.

Vince

Thomas Kyte wrote:
>
>
>
> this is not a bug. You simply have at least one row with resp_eng is NULL.
> Consider:
>

(posting trimmed)
>
> tkyte_at_8.0> select * from t1 where t1_ename not in ( select t2_ename from t2 );
> no rows selected
>
> All you need is to have one row with a NULL and the NOT IN returns 'UNKNOWN' --
> not true, not false -- UNKNOWN.
>
> Try:
>
> SELECT * FROM user_info WHERE userid NOT IN (SELECT resp_eng FROM milestone
> where resp_eng IS NOT NULL );
>
> or even better:
>
> SELECT *
> FROM user_info
> WHERE NOT EXISTS
> ( select null from milestone where milestone.resp_eng = user_info.userid );
>
> that'll be much faster if there is an index on resp_eng in milestone.
>
Received on Thu Sep 30 1999 - 15:14:22 CDT