Re: JOIN vs. WHERE ... IN (subselect)

On Fri, 16 May 2003, Dean Gibson (DB Administrator) wrote:

> Using PostgreSQL 7.3.2 on Linux.
>
> One of the types of queries that I like to do on our database is:
>
> SELECT * FROM table1
> WHERE indexed_column1 IN
> (SELECT column2 FROM table2 WHERE <condition>);
>
> However, my third-party SQL book says that the above is equivalent to:
>
> SELECT table1.* FROM table1, table2
> WHERE indexed_column1 = column2 AND <condition>;
>
> And indeed, the above query executes virtually instantaneously if
> "<condition>" results in a small number of rows from table2.
>
> I'm fairly new to SQL; are they really equivalent? If so, is there
> some reason that the first form is not optimized/transformed into the
> second form, or is this a planned enhancement?

7.4 already handles IN differently than previous versions and should
runs queries like the first one better than current versions.

And those two queries are not equivalent in any case unless the subselect
is guaranteed to only have one matching row (or you use a distinct in the
second query).