Re: OUTER JOIN with filter

On Sat, Mar 01, 2003 at 19:53:27 +0100,
Nicolas Fertig <nfertig(at)swissnow(dot)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.