From: Bruno Wolff III <bruno@wolff.to>
To: Nicolas Fertig <nfertig@swissnow.ch>
Cc: pgsql-sql@postgresql.org
Subject: Re: OUTER JOIN with filter
Date: Sat, 1 Mar 2003 19:54:06 -0600
Message-ID: <20030302015406.GA17308@wolff.to> (raw)
In-Reply-To: <3e61011d$0$1220$5402220f@news.sunrise.ch>
References: <3e61011d$0$1220$5402220f@news.sunrise.ch>
On Sat, Mar 01, 2003 at 19:53:27 +0100,
Nicolas Fertig <nfertig@swissnow.ch> wrote:
>
> I want to have all the row in table "table_main" with the value in the table
> "table_slave" (value or null if not exist)
>
> It is possible to have the same result without sub-select in OUTER JOIN
> (speed problem on big table) ?
Why do you think there will be a speed problem? With proper statistics
a good plan for the query below should be possible. As long as both tables
have indexes on id a merge join with a filter can be used. If there are
only a few rows with c1 = 'myvalue' out of a large number of rows and
there is an index on c1, then an alternative plan that does a merge join
with the sorted output from the subselect (done with an index scan) might
be faster. The planner should be able to choose between these plans as
long as has good statistics for the tables.
> SELECT TM.some_field, TS.name
> FROM table_main TM
> LEFT OUTER JOIN (SELECT name FROM table_slave WHERE c1 = 'myvalue') TS
> USING(id)
Assuming that the above query gives you the results you want, then I expect
that it is the most efficient way to write it. You could use a case
statement to handle the where c1 = 'myvalue' clause, but doing this is probably
going to be slower than doing a join to the subselect.
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: bruno@wolff.to, nfertig@swissnow.ch
Subject: Re: OUTER JOIN with filter
In-Reply-To: <20030302015406.GA17308@wolff.to>
* 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