Re: newbie : getting rid of IN clause

dn.perl_at_gmail.com wrote:
> I have two tables :
> table t1; fields : name old_qty new_qty
> Entries : ('aa', 100, 115) and ('aa', 122, 222)
> table t2; fields name qty_change
> Entries : (aa, 15) (aa, 100) (aa, 92)
>
> I want to find entries in t2 whose qty_change is not 'reflected'
> in table t1.
>
> Wrong query :
> select t2.* from t2, t1 t1
> where t1.part = t2.part and new_qty - old_qty <> qty_change
> -- and not exists (
> -- select null from t2 aa where t1.part = aa.part and new_qty - old_qty
> = aa.qty_change)
> -- )
> (Even when the above 3 lines are uncommented, the
> query is still wrong.) It's obvious why the query is wrong
> but I somehow thought that a clause on the lines of
> select ... FROM t2 t2, t1 aa, t1 bb where ...
> (with t1 used twice in FROM list) would be the best
> way to write the query.
>
> Correct query :
> select * from t2
> where (part, qty_change)
> not in (select part, new_qty - old_qty from t1
> where t1.part = t2.part)
>
> But does a better way to write the query exist?
> (Both tables have a 'timestamp' field which serves as primary key.)
> Please advise. TIA.

I can think of at least 9 ways to write that query. The way you tell the difference between them is with EXPLAIN PLAN.

Go to Morgan's Library at www.psoug.org and look up EXPLAIN PLAN. You will also see the nine different ways (just need to reverse the logic).

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org