Hi I have found that using an in clause with a subquery can be twice as fast as a straght join. Can enyone explain the logic of this to me? I am curious to understand it so I can optimise other queries for better performance.
I have included the queries below: OT_TARGETS has 200000 rows for regionid = 1 OT_PRODUCTS has 201 rows for regionid = 1 select distinct RegionID, ProductID, ProductName, ProductShortName, ProductRank from OT_PRODUCTS p Where RegionID = 1 and ProductID in (select distinct productid from ot_targets where regionid = 1) order by ProductRank, ProductName, ProductID; 2-3 seconds slower than above: select distinct t.RegionID, t.ProductID, p.ProductName, p.ProductShortName, p.ProductRank from OT_TARGETS t, OT_PRODUCTS p Where t.ProductID = p.ProductID and t.RegionID = p.RegionID and t.RegionID = 1 order by p.ProductRank, p.ProductName, p.ProductID; Thanks,