Re: SQL 'in' vs join.

mlw wrote:
> 
> Why is a "select * from table1 where field in (select field from table2
> where condition )"
> 
> is so dramatically bad compared to:
> 
> "select * from table1, table2 where table1.field = table2.field and
> condition"
> 
> I can't understand why the first query isn't optimized better than the
> second one. The 'in' query forces a full table scan (it shouldn't) and
> the second one uses the indexes. Does anyone know why?

Its not done yet, and probably hsomewhat difficult to do in a general
fashion

> I know I am no SQL guru, but my gut tells me that the 'in' operator
> should be far more efficient than a join.
> 
> Here are the actual queries:
> 
> cdinfo=# explain select trackid from zsong where muzenbr in (select
> muzenbr from ztitles where title = 'Mulan') ;

try

explainselect trackid  from zsong where muzenbr in (        select muzenbr          from ztitles         where title =
'Mulan'          and ztitles.muzenbr=zsong.muzenbr );
 

this should hint the current optimizer to do the right thing;

-----------------
Hannu