So I wasn't paying enough attention to postgres upgrades and missed that 7.1 actually has outer joins, so I'm currently
happilyrebuilding, 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
JOINtowns 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
syntacicsugar (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
ofthe tables being used. Will the planner consider putting implicit inner joins before the explicit outers or do all
explicitsoccur first?
Thank you.
Eric Nielsen
--------------------------------------------------------------------
mail2web - Check your email from the web at
http://mail2web.com/ .