From: | Waldemar Bergstreiser <littlesuspense(at)web(dot)de> |
---|---|
To: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: |
Date: | 2009-06-30 08:58:47 |
Message-ID: | 1744702512@web.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> -----Ursprüngliche Nachricht-----
> Von: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
> Gesendet: 30.06.09 10:17:11
> An: Waldemar Bergstreiser <littlesuspense(at)web(dot)de>
> CC: pgsql-general(at)postgresql(dot)org
> Betreff: Re: [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
>
I guess, you don't get it. Probably so
select * from a left outer join b on (a.b_id=b.id) ....
But I don't see any clear way to specify that table C should be outer joined only if we got a row from table B.
> 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.
I am fully agreed with you. My point is, that the syntax with outer is much clearer
and can't be so easy rewritten in standard ANSI SQL.
And I hope this can be implemented in SQL parser so the planner is not affected, and on the other side,
the reduction of overall SQL statement's length can have a positive impact on network traffic and speed of SQL parsing.
>
> Further, most database engines now support sql standard join syntax,
> so there's no great reason to support it for compatibility reasons.
>
This suggestion is not caused by compatibility consideration. It's just a try to take over a good things from oldies RDMS.
______________________________________________________
GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://movieflat.web.de
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Stark | 2009-06-30 10:09:20 | Re: |
Previous Message | valgog | 2009-06-30 08:46:31 | Re: Terrible Write Performance of a Stored Procedure |