Skip site navigation (1) Skip section navigation (2)

Re: SQL 'in' vs join.

From: Don Baccus <dhogaza(at)pacifier(dot)com>
To: mlw <markw(at)mohawksoft(dot)com>, Hannu Krosing <hannu(at)tm(dot)ee>
Cc: Hackers List <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: SQL 'in' vs join.
Date: 2000-11-30 15:24:30
Message-ID: 3.0.1.32.20001130072430.01789970@mail.pacifier.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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.

In response to

Responses

pgsql-hackers by date

Next:From: Hannu KrosingDate: 2000-11-30 15:26:02
Subject: Re: SQL 'in' vs join.
Previous:From: Tom LaneDate: 2000-11-30 15:15:31
Subject: Re: compiling pg 7.0.3 on sco 5.0.5

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group