Received: from mail1.hub.org (webmail.hub.org [216.126.85.1]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8HJDad30263 for ; Mon, 17 Sep 2001 15:13:36 -0400 (EDT) (envelope-from ericnielsen@pop.ne.mediaone.net) Received: from relay1.softcomca.com (relay.softcomca.com [168.144.1.67] (may be forged)) by mail1.hub.org (8.11.3/8.11.4) with ESMTP id f8HIhwD01404 for ; Mon, 17 Sep 2001 14:43:58 -0400 (EDT) (envelope-from ericnielsen@pop.ne.mediaone.net) Received: from M2W018 ([168.144.108.18]) by relay1.softcomca.com with Microsoft SMTPSVC(5.0.2195.3779); Mon, 17 Sep 2001 14:44:33 -0400 X-Originating-IP: 129.53.219.254 X-URL: http://www.mail2web.com/ Subject: Implicit v Explicit joins... From: "ericnielsen@pop.ne.mediaone.net" Date: Mon, 17 Sep 2001 14:41:47 -0400 To: "pgsql-sql@postgresql.org" Reply-To: ericnielsen@mediaone.net X-Priority: 3 X-MSMail-Priority: Normal MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Mailer: JMail 3.7.0 by Dimac (www.dimac.net) Message-ID: X-OriginalArrivalTime: 17 Sep 2001 18:44:33.0241 (UTC) FILETIME=[CACA8490:01C13FA8] Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from Quoted-Printable to 8bit by postgresql.org id f8HJDbd30288 X-Archive-Number: 200109/134 X-Sequence-Number: 4812 So I wasn't paying enough attention to postgres upgrades and missed that 7.1 actually has outer joins, so I'm currently happily rebuilding, but I had a few questions about the explicit join syntax and preformance issues. 1. Say i have a tables called "married" (marriedid, husband, wife), "people" (peopleid, firstname, lastname, townid), and "towns" (townid, townname) (not the exact exaple, but should be close enough). I want to get a list of all couples (fullname of both husband/wife with hometown) where the hometown of one equals 'foo'; Would this be the proper way of setting up the query? SELECT h.firstname, h.lastname, h.hometown, w.firstname, w.lastname, w.hometown FROM married m JOIN people h ON (m.husband=h.peopleid) JOIN people w ON (m.wife=w.peopleid) LEFT OUTER JOIN towns ht (h.townid=ht.townid) LEFT OUTER JOIN towns wt (w.townid=wt.townid) WHERE ht.townname='foo' OR wt.townname='foo'; 2. In general is explicit outer join more efficient than the old union select syntax? Is the outer join syntax just syntacic sugar (does it decode into the union selects)? 3. I think I saw someone that explicit joins occur in order, giving the planner less room to optimize, is this correct? I've often heard that you want to preform your inner joins before the other joins in order to limit the size of the tables being used. Will the planner consider putting implicit inner joins before the explicit outers or do all explicits occur first? Thank you. Eric Nielsen -------------------------------------------------------------------- mail2web - Check your email from the web at http://mail2web.com/ .