[sqlite] Query Optimisation Question?

[Da Martian]

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,