... - Mailing list pgsql-general

Hi, thanks for your reply,

On Wed, 1 Sep 2004 08:10:52 -0400, <terry@ashtonwoodshomes.com> wrote:

> NOTE: The first way cannot support OUTER joins, the second way can.
> Hence sometimes one has to use
> the second way for at least some of the joins.
>

Yes, I've always done OUTER joins the second way. I suppose it's just the
way I was taught SQL: I was initially taught now to do 'ordinary' joins
using the first syntax, and then taught 'LEFT' joins using the second
syntax when I came to need to use them (I very much leaned SQL 'on the
job', though I know of people who *always* use OUTER joins in their
queries). I'd never considered that there was another syntax!

> PREVIOUSLY: The second way can allow one to tell the planner a "better
> way" to join the tables.
> Likewise it can also enable the programmer to force the planner into a
> worse way.  Oops!
> NOW: I believe that the latest version of postgres (7.4.x) the planner
> will override the 2nd methods
> requested join method if it knows of a better way and can do the better
> way.  (Outer joins need to
> be done last, by the nature of them, and so cannot be changed much,
> there may be other cases where
> the planner cannot change the requested plan).
>

That being the case, would it be true to say that with recent versions of
PostgreSQL they both perform identically, meaning the second could be
considered preferable due to its self-documenting nature (and consistency
with the OUTER JOIN syntax)?

> I am not an expert, but this is what I recall from following the list.
>
> Terry Fielder
> Manager Software Development and Deployment
> Great Gulf Homes / Ashton Woods Homes
> terry@greatgulfhomes.com
> Fax: (416) 441-9085
>
>
>> -----Original Message-----
>> From: pgsql-general-owner@postgresql.org
>> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Russ Brown
>> Sent: Wednesday, September 01, 2004 7:55 AM
>> To: pgsql-general@postgresql.org
>> Subject: [GENERAL] Join efficiency
>>
>>
>> Hello all,
>>
>> Recently a post on this list made me think a bit about the
>> way in which I
>> write my queries.
>>
>> I have always written queries with ordinary joins in this manner:
>>
>> SELECT * FROM a, b WHERE a.x=b.x;
>>
>> However I recently saw an laternative syntax:
>>
>> SELECT * FROM a JOIN b ON a.x=b.x;
>>
>> Is there any difference between these queries in terms of the
>> speed of
>> planning or the quality of the plan untimately used? I'd
>> imagine that the
>> second form provides more information that the planner may be
>> able to use
>> to make a better plan (or make a good plan more easily), but
>> I've never
>> had any problems with the first form.
>>
>> It also seems to me that the second form is more
>> self-documenting, which
>> is something I'm always in favour of.
>>
>> I'd appreciate anyone's thought/insight.
>>
>> Thanks.
>>
>> --
>>
>> Russell Brown
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 9: the planner will ignore your desire to choose an index
>> scan if your
>>       joining column's datatypes do not match
>>
>



--

Russell Brown