Re: sequential joins - Mailing list pgsql-sql

Josh,
the sub-select statement I was using were not for filtering data (in which
case you can move filtering condition in ON part of the join), but rather
renaming the column 'username' with the name I need in the current join
statement. In this case, columns with new names ('artist', 'designer',
'programmer') would be appended to the overall query result and can be
selected by top-level select statement.
This looks like a very clumsy way to do what I need.
Any ideas?
thanks,

Oleg

Josh Berkus wrote:

> 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 multiple
>  references to the same table in subselects and optimizing your query
>  appropriately.  On occassion, I've had to do this with the same table
>  sub-selected 50 seperate times and Postgres handles it OK.
>
> Sometimes you can use a straight LEFT OUTER JOIN instead of a
>  subselect.  This depends entirely on whether you are planning on doing
>  any 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.f2
>         LEFT OUTER JOIN (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 on
>  the same table.  I recently found this out the hard way, and crashed
>  an MS SQL Server despite 1gb of memory in the machine.  The same query
>  does OK in Postgres on less hardware)
>
> -Josh Berkus
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)