Skip site navigation (1) Skip section navigation (2)

Re:

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: Waldemar Bergstreiser <littlesuspense(at)web(dot)de>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re:
Date: 2009-06-30 08:17:01
Message-ID: dcc563d10906300117s6f3e58b3n7ed7556a3d7b7bfd@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
On Tue, Jun 30, 2009 at 2:00 AM, Waldemar
Bergstreiser<littlesuspense(at)web(dot)de> wrote:
>> On Mon, Jun 29, 2009 at 2:08 PM, littlesuspense<littlesuspense(at)web(dot)de> wrote:
>> > Hi Volk,
>> >
>> Note that the word outer is just noise in pgsql, i.e. it's not needed.
>>  What you've got are left outer, right outer, and full outer joins.
>> All can be called just left, right, or full joins.  Note that inner
>> joins are just called joins.
>>
>> > select * from a, outer b where a.id = b.id;
>>
>> select * from a full join b on (a.id=b.id) where ...
>> select * from a left join b on (a.id=b.id) where ...
>> select * from a join b on (a.id=b.id) where ...
>
> this is only a simple case, but outer can make syntax more clean in complicated joins.
> Just try to rewrite query below with left outter joins. I had not found any compact syntax.
>
> -- c *= b *= a =* d =* f
> select * from a, outer( b, outer c), outer (d, outer f )
> where a.b_id = b.id and b.c_id = c.id and a.d_id = d.id and d.f_id = f.id;

from a full join b on (a.id=b.id)
full join c on (b.id=c.id)
full join d

and so on.  Doesn't seem any lestt compact or readable to me.
>> What you get with postgresql is mostly ANSI standard stuff, which
>> left/right/full outer and inner joins are.
>>
> And I like that, but each SQL RDMS system and each SQL dialect de facto provide also a lot of standard extensions. So the RDMS user has a choice, to use such extensions or not. And I think postgres can only win if we it have more and very sane extensions.

If the standard SQL syntax does the job, I see no reason to include
non-standard syntax from other dbs unless they're obviously cleaner
and simpler, and I really don't see that here.  Every thing you add to
the query planner / executor costs something in planning and / or
execution times.

Further, most database engines now support sql standard join syntax,
so there's no great reason to support it for compatibility reasons.

In response to

  • Re: at 2009-06-30 08:00:04 from Waldemar Bergstreiser

pgsql-general by date

Next:From: durumdaraDate: 2009-06-30 08:21:26
Subject: Re: Python client + select = locked resources???
Previous:From: Richard HuxtonDate: 2009-06-30 08:02:39
Subject: Re: Unexpected behaviour of date_part

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group