From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Dean Gibson (DB Administrator) <dba-sql@ultimeth.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: JOIN vs. WHERE ... IN (subselect)
Date: Sat, 17 May 2003 09:13:20 -0700 (PDT)
Message-ID: <20030517090929.M99020-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <5.1.0.14.2.20030516231811.02deac70@imaps.ultimeth.net>
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).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, dba-sql@ultimeth.net
Subject: Re: JOIN vs. WHERE ... IN (subselect)
In-Reply-To: <20030517090929.M99020-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox