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/ .
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
