Re: SQL 'in' vs join.

At 08:37 AM 11/30/00 -0500, mlw wrote:
>> 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"

>Now, given the two components, each with very low costs, it chooses to
>do a sequential scan on the table. I don't get it. I have have been
>having no end of problems with Postgres' optimizer. It just seems to be
>brain dead at times. It is a huge point of frustration to me. I am tied
>to postgres in my current project, and I fear that I will not be able to
>implement certain features because of this sort of behavior.

But but but ...

Not only is the join faster, but it is more readable and cleaner SQL as
well. I would never write the query in its first form. I'd change the
second one slightly to "select table1.* from ...", though, since those
are apparently the only fields you want.

The optimizer should do a better job on your first query, sure, but why
don't you like writing joins?

- Don Baccus, Portland OR <dhogaza(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.