From: | John Sidney-Woollett <johnsw(at)wardbrook(dot)com> |
---|---|
To: | Richard Huxton <dev(at)archonet(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Join efficiency |
Date: | 2004-09-01 12:40:40 |
Message-ID: | 4135C348.8020402@wardbrook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Does anyone know if there is a postgres shorthand for Oracle's (+)
notation to denote an outer join?
eg
SELECT * from a, b where a.x = b.x (+)
John Sidney-Woollett
Richard Huxton wrote:
> Russ Brown wrote:
>
>>
>> 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.
>
>
> The first form allows PG to plan however it sees fit. The second will
> force the join order to be the same as you specify in the query. This
> doesn't matter here, but might with a more complicated query.
>
> With v7.4 and higher, I believe this join forcing is configurable
> (join_collapse_limit).
>
>> It also seems to me that the second form is more self-documenting,
>> which is something I'm always in favour of.
>
>
> I tend to prefer the WHERE form, but that might just be me.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2004-09-01 12:58:13 | Re: Join efficiency |
Previous Message | Richard Huxton | 2004-09-01 12:26:37 | Re: Join efficiency |