Re: sequential joins

Oleg,

> I wonder if there is a better way to do this. Maybe using CASE WHEN
>  THEN
> ELSE END clause to avoid multiple scans?
> thanks,

No, not really.  PostgreSQL is pretty good about detecting multiplereferences to the same table in subselects and
optimizingyour queryappropriately.  On occassion, I've had to do this with the same tablesub-selected 50 seperate times
andPostgres handles it OK.
 

Sometimes you can use a straight LEFT OUTER JOIN instead of asubselect.  This depends entirely on whether you are
planningon doingany GROUPing or totals on the main query.  If NOT, then:
 

SELECT tablea.f1, tablea.f2, alias1.f3, alias2.f3
FROM tablea LEFT OUTER JOIN (SELECT f2, f3         FROM tableb WHERE f4 = "1") alias1    ON tablea.f1 = alias1.f2LEFT
OUTERJOIN (SELECT f2, f3         FROM tableb WHERE f4 = "2") alias2    ON tablea.f1 = alias2.f2;
 

Is equivalent to:

SELECT tablea.f1, tablea.f2, alias1.f3, alias2.f3
FROM tablea LEFT OUTER JOIN tableb AS alias1    ON (tablea.f1 = alias1.f2 AND alias1.f4 = "1")LEFT OUTER JOIN tableb AS
alias2   ON (tablea.f1 = alias2.f2 AND alias2.f4 = "2") ;
 

And the second should run a bit faster.


(FYI:  MS SQL Server 7.0 does *not* optimize for multiple subselects onthe same table.  I recently found this out the
hardway, and crashedan MS SQL Server despite 1gb of memory in the machine.  The same querydoes OK in Postgres on less
hardware)

-Josh Berkus