Thread:
Hi Volk,
at first sorry for my English. I use postgresql very often and I really love it but the syntax for outer join make me sick.
Oracle short notation (+) is also not a best choice at this place but I recall me, that the Informix have a really good and clear syntax:
select * from a, outer b where a.id = b.id;
select * from a, outer( b, outer c) where a.id = b.id and b.id= c.id;
And surely, I would like to see that also in postgresql.
I hope, I can win you for that.
Sincerely,
LS
at first sorry for my English. I use postgresql very often and I really love it but the syntax for outer join make me sick.
Oracle short notation (+) is also not a best choice at this place but I recall me, that the Informix have a really good and clear syntax:
select * from a, outer b where a.id = b.id;
select * from a, outer( b, outer c) where a.id = b.id and b.id= c.id;
And surely, I would like to see that also in postgresql.
I hope, I can win you for that.
Sincerely,
LS
On Mon, Jun 29, 2009 at 2:08 PM, littlesuspense<littlesuspense@web.de> wrote: > Hi Volk, > > at first sorry for my English. I use postgresql very often and I really > love it but the syntax for outer join make me sick. > Oracle short notation (+) is also not a best choice at this place but I > recall me, that the Informix have a really good and clear syntax: 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 ... and so on. > And surely, I would like to see that also in postgresql. What you get with postgresql is mostly ANSI standard stuff, which left/right/full outer and inner joins are.
On Mon, Jun 29, 2009 at 5:11 PM, Scott Marlowe<scott.marlowe@gmail.com> wrote: > On Mon, Jun 29, 2009 at 2:08 PM, littlesuspense<littlesuspense@web.de> wrote: >> Hi Volk, >> >> at first sorry for my English. I use postgresql very often and I really >> love it but the syntax for outer join make me sick. >> Oracle short notation (+) is also not a best choice at this place but I >> recall me, that the Informix have a really good and clear syntax: > > 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 ... also, select * from a join b using(id) where...; In simple join cases this is usually the best way to go. merlin
> On Mon, Jun 29, 2009 at 2:08 PM, littlesuspense<littlesuspense@web.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; > > and so on. > > > And surely, I would like to see that also in postgresql. > > 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 theRDMS user has a choice, to use such extensions or not. And I think postgres can only win if we it have more and very saneextensions. I think so. ______________________________________________________ GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT! Jetzt freischalten unter http://movieflat.web.de
On Tue, Jun 30, 2009 at 2:00 AM, Waldemar Bergstreiser<littlesuspense@web.de> wrote: >> On Mon, Jun 29, 2009 at 2:08 PM, littlesuspense<littlesuspense@web.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 theRDMS user has a choice, to use such extensions or not. And I think postgres can only win if we it have more and very saneextensions. 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.
> -----Ursprüngliche Nachricht----- > Von: "Scott Marlowe" <scott.marlowe@gmail.com> > Gesendet: 30.06.09 10:17:11 > An: Waldemar Bergstreiser <littlesuspense@web.de> > CC: pgsql-general@postgresql.org > Betreff: Re: [GENERAL] > On Tue, Jun 30, 2009 at 2:00 AM, Waldemar > Bergstreiser<littlesuspense@web.de> wrote: > >> On Mon, Jun 29, 2009 at 2:08 PM, littlesuspense<littlesuspense@web.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. Sothe RDMS user has a choice, to use such extensions or not. And I think postgres can only win if we it have more and verysane 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
On Tue, Jun 30, 2009 at 9:58 AM, Waldemar Bergstreiser<littlesuspense@web.de> wrote: >> > -- 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 I don't get it either. by *= do you mean the Oracle-style outer join? in which case why is this not just select * from a, left outer join b on (a.b_id = b.id) left outer join c on (b.c_id = c.id) left outer join d on (a.d_id = d.id) left outer join f on (d.f_id = f.id) You can parenthesize it different ways but I think the result in this case is actually the same. > 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. Well that would be the default since if you get no row from b b.c_id will be null. -- greg http://mit.edu/~gsstark/resume.pdf
> >> > -- 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 > > I don't get it either. by *= do you mean the Oracle-style outer join? > in which case why is this not just > > select * from a, > left outer join b on (a.b_id = b.id) > left outer join c on (b.c_id = c.id) > left outer join d on (a.d_id = d.id) > left outer join f on (d.f_id = f.id) > The equal expression would be like this: select * from a left outer join (b left outer join c on (b.c_id = c.id) ) on (a.b_id = b.id) left outer join (d left outer join f on (d.f_id = f.id) ) on (a.d_id = d.id) and that is a double length of original SQL and has not very clear syntax in my opinion. Additionally it can be very funny if we try to append a join conditions like "c.iso = d.iso" > You can parenthesize it different ways but I think the result in this > case is actually the same. > I'm not sure. > > 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. > > Well that would be the default since if you get no row from b b.c_id > will be null. > and if I have a row in table C where c.id is null? A don't know. I found a good explanation about informix outer joins. http://savage.net.au/SQL/outer-joins.html Please take a look at that. ________________________________________________________________ Neu: WEB.DE Doppel-FLAT mit Internet-Flatrate + Telefon-Flatrate für nur 19,99 Euro/mtl.!* http://produkte.web.de/go/02/
Waldemar Bergstreiser <littlesuspense@web.de> writes:
> Just try to rewrite query below with left outter joins. I had not found any compact syntax.
> 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;
This has got pretty much the same problem as Oracle's syntax: there's no
principled way to decide what it *means*. Which join is each of the
WHERE conditions supposed to be attached to, and why? What do you do if
you want a behavior slightly different from whatever the engine decides
it means?
The standard's syntax is a bit more verbose, but at least it's perfectly
clear which conditions are outer-join conditions and which are filters.
regards, tom lane
On Jun 30, 2009, at 4:14 AM, Waldemar Bergstreiser wrote: >> I don't get it either. by *= do you mean the Oracle-style outer >> join? >> in which case why is this not just >> >> select * from a, >> left outer join b on (a.b_id = b.id) >> left outer join c on (b.c_id = c.id) >> left outer join d on (a.d_id = d.id) >> left outer join f on (d.f_id = f.id) >> <snip> > >>> 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. >> >> Well that would be the default since if you get no row from b b.c_id >> will be null. >> > > and if I have a row in table C where c.id is null? A don't know. No, it's perfectly clear as 'NULL = NULL' evaluates to false: postgres=# select null = null; ?column? ---------- (1 row) Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k
On Tue, Jun 30, 2009 at 10:22:23AM -0700, Erik Jones wrote: > > postgres=# select null = null; > ?column? > ---------- > > (1 row) Actually, it's NULL. shackle@postgres:5432=# SELECT (NULL = NULL) IS NULL; ?column? ---------- t (1 row) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tue, Jun 30, 2009 at 1:22 PM, Erik Jones<ejones@engineyard.com> wrote: >> and if I have a row in table C where c.id is null? A don't know. > > No, it's perfectly clear as 'NULL = NULL' evaluates to false: > > postgres=# select null = null; > ?column? > ---------- > you can test for that with 'is distinct from': select null is distinct from null; -- false select null is distinct from 1; -- true
On Jun 30, 2009, at 11:25 AM, David Fetter wrote: > On Tue, Jun 30, 2009 at 10:22:23AM -0700, Erik Jones wrote: >> >> postgres=# select null = null; >> ?column? >> ---------- >> >> (1 row) > > Actually, it's NULL. > > shackle@postgres:5432=# SELECT (NULL = NULL) IS NULL; > ?column? > ---------- > t > (1 row) Er, yeah, I mispoke, my point was that it doesn't evaluate to TRUE so the join doesn't hit a match, though... Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k
On Tue, Jun 30, 2009 at 01:14:10PM +0200, Waldemar Bergstreiser wrote: > I found a good explanation about informix outer joins. > > http://savage.net.au/SQL/outer-joins.html > > Please take a look at that. The syntax appears to make the expression of various idioms difficult; for example, how would I express the following: SELECT * FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL OR a.c <> b.d; Admittedly I don't write code like this very often but, yes, I have used it on some occasions. I guess I'd have to resort to a subselect? I believe this is what Tom was referring to when he said that "there's no principled way to decide what it *means*". For example the semantics of the above are very different from either of: SELECT * FROM a LEFT JOIN b ON a.id = b.id OR a.c <> b.d WHERE b.id IS NULL; or: SELECT * FROM a LEFT JOIN b ON a.id = b.id AND (b.id IS NULL OR a.c <> b.d) and I can't think of any other formulations after reading the link you gave---it only seems to talk about binary operators involving columns from two tables. Second shouldn't be allowed, but I included it in case I was missing something. -- Sam http://samason.me.uk/