SQL 'in' vs join.

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?

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') ;
NOTICE:  QUERY PLAN:
Seq Scan on zsong  (cost=100000000.00..219321449380756.66 rows=2193213
width=4) SubPlan   ->  Materialize  (cost=100000022.50..100000022.50 rows=10 width=4)         ->  Seq Scan on ztitles  (cost=100000000.00..100000022.50
rows=10 width=4) 

cdinfo=# explain select trackid from zsong, ztitles where
ztitles.muzenbr = zsong.muzenbr and title = 'Mulan' ;
NOTICE:  QUERY PLAN:
Merge Join  (cost=0.00..183664.10 rows=219321 width=12) ->  Index Scan using zsong_muznbr on zsong  (cost=0.00..156187.31
rows=2193213 width=8) ->  Index Scan using ztitles_pkey on ztitles  (cost=0.00..61.50
rows=10 width=4)  

cdinfo=# \d zsong                              Table "zsong"Attribute |       Type        |                 Modifier
-----------+-------------------+-------------------------------------------muzenbr   | integer           |disc      | integer           |trk       | integer           |song      | character varying |trackid   | integer           | not null default
nextval('trackid'::text)artistid  | integer           |acd       | character varying |
Indices: zsong_muznbr,        zsong_pkey    

cdinfo=# \d ztitles             Table "ztitles"Attribute  |       Type        | Modifier
------------+-------------------+----------muzenbr    | integer           | not nullartistid   | integer           |cat2       | character varying |cat3       | character varying |cat4       | character varying |performer  | character varying |performer2 | character varying |title      | character varying |artist1    | character varying |engineer   | character varying |producer   | character varying |labelname  | character varying |catalog    | character varying |distribut  | character varying |released   | character varying |origrel    | character varying |nbrdiscs   | character varying |spar       | character varying |minutes    | character varying |seconds    | character varying |monostereo | character varying |studiolive | character varying |available  | character(1)      |previews   | character varying |pnotes     | character varying |acd        | character varying |
Index: ztitles_pkey 

-- 
http://www.mohawksoft.com