Received: from mail1.waterford.org (mail1.waterford.org [205.124.117.6]) by postgresql.org (Postfix) with ESMTP id 57613475E27 for ; Fri, 1 Mar 2002 17:22:52 -0500 (EST) Received: from waterford.org ([205.124.138.93]) by mail1.waterford.org (Netscape Messaging Server 3.56) with ESMTP id 416 for ; Fri, 1 Mar 2002 15:10:52 -0700 Message-ID: <3C8004D1.40DEF8A1@waterford.org> Date: Fri, 01 Mar 2002 15:46:41 -0700 From: Oleg Lebedev X-Mailer: Mozilla 4.72 [en] (Windows NT 5.0; U) X-Accept-Language: en MIME-Version: 1.0 Cc: Postgres SQL Mailing List Subject: Re: sequential joins References: Content-Type: text/plain; charset=koi8-r Content-Transfer-Encoding: 7bit X-Archive-Number: 200203/14 X-Sequence-Number: 6896 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)