On Sat, Mar 01, 2003 at 19:53:27 +0100, Nicolas Fertig <[EMAIL PROTECTED]> 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. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
