pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: ericnielsen@pop.ne.mediaone.net <ericnielsen@pop.ne.mediaone.net>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Implicit v Explicit joins...
Date: Mon, 17 Sep 2001 14:41:47 -0400
Message-ID: <RELAY1tPyOFkXIA5yS700002af2@relay1.softcomca.com> (raw)

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/ .




Message-ID: <RELAY1tPyOFkXIA5yS700002af2@relay1.softcomca.com>
Permalink:  ../RELAY1tPyOFkXIA5yS700002af2@relay1.softcomca.com/
Also on:    postgresql.org/message-id/RELAY1tPyOFkXIA5yS700002af2@relay1.softcomca.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: ericnielsen@pop.ne.mediaone.net
  Subject: Re: Implicit v Explicit joins...
  In-Reply-To: <RELAY1tPyOFkXIA5yS700002af2@relay1.softcomca.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox