Thread: RE: [HACKERS] JOIN syntax. Examples?
Microsoft SQL Server v6.5 have SQL92 join syntax. I don't have the standard in front of me but here's what I remember. join_clause :table_name|view_name|join_clause [alias ][LEFT |RIGHT |CROSS ] JOIN table_name|view_name|join_clause [alias ]ON join_tatements The allows for neat little tricks like (hope you can follow it): SELECT a3.name, a3.address, a3.city, a4.state_abbrev, a6.postal_code, a9.country_code FROM ( ( (states_list a5 JOIN postal_codes a6 ON (a5.stateid = a6.stateid) ) a4 RIGHT JOIN (clients a1 LEFT JOIN addresses a2 ON (a1.clientid =a2.clientid AND a2.prefered = 1) ) a3 ON (a3.stateid = a4.stateid) ) a7 LEFT JOIN countries a8 ON (a7.countryid= a8.countryid) ) a9 I'm not sure if Microsoft implemented it but I believe that subselects would be a great addition the above. I can load up a Microsoft SQL server for any testing you need done. I'm pretty sure that the Help files have a run down of their supported syntax but I never trust Microsoft to stick to a standard (even their own). > -----Original Message----- > From: Dan Gowin [mailto:DGowin@avantec.net] > Sent: Friday, December 11, 1998 7:26 AM > To: 'Thomas G. Lockhart'; PGSQL HACKERS (E-mail) > Subject: RE: [HACKERS] JOIN syntax. Examples? > > > I run three HP minicomputers and two Sun Ultra 3000 all with > Oracle 7.3 and one with Oracle 8.0 . > > Send it to me. > > D. > > > -----Original Message----- > From: Thomas G. Lockhart [mailto:lockhart@alumni.caltech.edu] > Sent: Friday, December 11, 1998 1:36 AM > To: Postgres Hackers List > Subject: [HACKERS] JOIN syntax. Examples? > > > Well, I've started looking through my books for info on > joins. The cross > join was pretty easy: > > postgres=> select * from (a cross join b); > i| j|i| k > -+----+-+-- > 1|10.1|1|-1 > 2|20.2|1|-1 > 4| |1|-1 > <snip> > > which I've put into my copy of the parser. > > Does anyone have a commercial installation which has good support for > SQL92 joins? I'd like to send some small test cases to verify that I > understand what the behavior should be. > > Also, if anyone has worked with join syntax, outer joins > especially, it > would be great to get some test case contributions... > > - Tom >
> Microsoft SQL Server v6.5 have SQL92 join syntax. I don't have the
> standard in front of me but here's what I remember.
OK, it's pretty clear that Oracle doesn't implement SQL92-syntax on
outer joins (unless they support it as an alternative; does anyone find
"OUTER JOIN" in the syntax docs?).
Let's assume that M$ may be close to standard, but given that they don't
bother following standards in other areas (WHERE x = NULL, etc) we can't
use them as a truth generator.
We are looking for a system which supports syntax like DeJuan gave:
SELECT * FROM (A LEFT OUTER JOIN B USING (X));
or
SELECT * FROM (A LEFT OUTER JOIN B ON (A.X = B.X));
etc. if we are going to try for the SQL92 standard,
rather than the Oracle form:
SELECT * FROM A, B WHERE A.X = (+) B.X;
or the Informix form:
SELECT * FROM A, OUTER B WHERE A.X = B.X; (is the WHERE clause required here?)
Does anyone have a non-M$ RDBMS which implements SQL92 joins?
otoh, any system which can test the results of a query, even if the
query needs to be translated first, has some benefit. As/if I progress
I'll take some of you up on the offer to run queries.
- Tom
"Thomas G. Lockhart" wrote: >Does anyone have a non-M$ RDBMS which implements SQL92 joins? The book "The Practical SQL Handbook", which is often recommended on these lists, uses the syntax `*=' and `=*' for left and right outer joins (page 211). I think we ought to support this syntax as well, since it will save new users from confusion. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "The spirit of the Lord GOD is upon me; because the LORD hath anointed me to preach good tidings unto the meek; he hath sent me to bind up the brokenhearted, to proclaim liberty to the captives, and the opening of the prison to them that are bound." Isaiah 61:1
On Fri, 11 Dec 1998, Oliver Elphick wrote: > The book "The Practical SQL Handbook", which is often recommended on > these lists, uses the syntax `*=' and `=*' for left and right outer > joins (page 211). I think we ought to support this syntax as well, > since it will save new users from confusion. 'A Guide to The SQL Standard" (4th Ed.) seems to indicate that the MS syntax is fairly close. ISBN 0-201-96426-0 -- | Matthew N. Dodd | 78 280Z | 75 164E | 84 245DL | FreeBSD/NetBSD/Sprite/VMS | | winter@jurai.net | This Space For Rent | ix86,sparc,m68k,pmax,vax | | http://www.jurai.net/~winter | Are you k-rad elite enough for my webpage? |
> The book "The Practical SQL Handbook", which is often recommended on
> these lists, uses the syntax `*=' and `=*' for left and right outer
> joins (page 211). I think we ought to support this syntax as well,
> since it will save new users from confusion.
This one conflicts with Postgres' operator extensibility features, since
it would look just like a legal operator.
The two books I have at hand (besides my old Ingres docs) are A Guide to
the SQL Standard by Date and Darwen and Understanding the New SQL by
Melton and Simon. Both focus on SQL standard syntax, and neither mention
the various outer join syntaxes accepted by Oracle, Informix, or Sybase.
An explanation for the lack of standards compliance by the big three
probably involves the fact that they predate the standard by a
significant number of years.
- Tom
Thomas G. Lockhart wrote: > > > The book "The Practical SQL Handbook", which is often recommended on > > these lists, uses the syntax `*=' and `=*' for left and right outer > > joins (page 211). I think we ought to support this syntax as well, > > since it will save new users from confusion. > > This one conflicts with Postgres' operator extensibility features, since > it would look just like a legal operator. so does = Could it be possible to extend the operator extensibility features to achieve the behaviour of outer/cross joins ? > The two books I have at hand (besides my old Ingres docs) are A Guide to > the SQL Standard by Date and Darwen and Understanding the New SQL by > Melton and Simon. Both focus on SQL standard syntax, and neither mention > the various outer join syntaxes accepted by Oracle, Informix, or Sybase. Has anybody tried out DB2 ? I have downloaded it (for linux) but have not yet tried it. > An explanation for the lack of standards compliance by the big three > probably involves the fact that they predate the standard by a > significant number of years. Not to mention that both =* and =(+) are more concise and easier to follow, at least for one with my headshape. The standard is probably the 'worst common denominator' or something like that :( ----------------- Hannu
> > This one conflicts with Postgres' operator extensibility features,
> > since it would look just like a legal operator.
> so does =
But in fact its usage for joins matches the typical usage elsewhere.
> Has anybody tried out DB2 ?
> I have downloaded it (for linux) but have not yet tried it.
Just downloaded it this morning (and afternoon, it's a thin pipe at home
for 60MB of files :) Have you looked at what it takes to do an
installation yet?
> Not to mention that both =* and =(+) are more concise and easier to
> follow, at least for one with my headshape.
> The standard is probably the 'worst common denominator' or something
> like that :(
DeJuan points out a major strength of the SQL92 syntax, which allows
multiple outer joins in the same query. One of my books shows an
example:
select * from q1 full outer join q2 on (q1.id = q2.id) full outer join q3 on (coalesce(q1.id,q2.id)=q3.id)
fullouter join q4 on (coalesce(q1.id,q2.id,q3.id)=q4.id)
I suppose one can do something similar using a *= operator by using
parentheses? Not sure though...
- Tom
> > Has anybody tried out DB2 ?
> > I have downloaded it (for linux) but have not yet tried it.
> Just downloaded it this morning (and afternoon, it's a thin pipe at
> home for 60MB of files :) Have you looked at what it takes to do an
> installation yet?
Well, I'll have to save it for later, at least at home. It's glibc2
only. Also, the tar file has a bunch of rpms but also other files. Don't
know what's up with that...
- Tom
Hi all, >> > Has anybody tried out DB2 ? >> > I have downloaded it (for linux) but have not yet tried it. >> Just downloaded it this morning (and afternoon, it's a thin pipe at >> home for 60MB of files :) Have you looked at what it takes to do an >> installation yet? Could someone tell me please where I can download DB2? Thanks, -Jose'-
Hello Thomas, venerdì, 11 dicembre 98, you wrote: >> Microsoft SQL Server v6.5 have SQL92 join syntax. I don't have the >> standard in front of me but here's what I remember. TGL> OK, it's pretty clear that Oracle doesn't implement SQL92-syntax on TGL> outer joins (unless they support it as an alternative; does anyone find TGL> "OUTER JOIN" in the syntax docs?). TGL> Let's assume that M$ may be close to standard, but given that they don't TGL> bother following standards in other areas (WHERE x = NULL, etc) we can't TGL> use them as a truth generator. TGL> We are looking for a system which supports syntax like DeJuan gave: TGL> SELECT * FROM (A LEFT OUTER JOIN B USING (X)); TGL> or TGL> SELECT * FROM (A LEFT OUTER JOIN B ON (A.X = B.X)); TGL> etc. if we are going to try for the SQL92 standard, TGL> rather than the Oracle form: TGL> SELECT * FROM A, B WHERE A.X = (+) B.X; TGL> or the Informix form: TGL> SELECT * FROM A, OUTER B WHERE A.X = B.X; TGL> (is the WHERE clause required here?) TGL> Does anyone have a non-M$ RDBMS which implements SQL92 joins? Download OCELOT for Win32 at http://ourworld.compuserve.com/homepages/OCELOTSQL their database implements SQL92 joins. Their home page says: Ocelot makes the only Database Management System (DBMS) that supports the full ANSI / ISO SQL Standard (1992). ... This is also the only place on the Net where you can find documentation that explains and provides examples of the full SQL-92 standard. This is version 1.0. I'm trying it, is very interesting but it is only for M$-win. -Jose'-
> Download OCELOT for Win32...
> their database implements SQL92 joins.
> I'm trying it, is very interesting but it is only for M$-win.
My linux system doesn't know how to boot or run M$ stuff. Funny, but my
Mac before that didn't know how either :)
- Tom
Hello Thomas, martedì, 12 gennaio 99, you wrote: >> Download OCELOT for Win32... >> their database implements SQL92 joins. >> I'm trying it, is very interesting but it is only for M$-win. TGL> My linux system doesn't know how to boot or run M$ stuff. Funny, but my TGL> Mac before that didn't know how either :) TGL> - Tom You are a very puritan, I'm glad for you ;) Unfortunately I can't be 100% puritan like you :( I tried some joins on Ocelot...seems nice. If you want something more significant I can try it for you. table P: PNO PNAME COLOR WEIGHT CITY ----------------------------------------- P1 NUT RED 12 LONDON P4 SCREW RED 14 LONDON P2 BOLT GREEN 17 PARIS table SP: SNO PNO QTY ----------------------- S1 P1 300 S1 P2 200 S1 P2 200 SELECT DISTINCT SP.PNO, P.CITY FROM SP NATURAL JOIN P; PNO CITY --------------- P1 LONDON P2 PARIS SELECT DISTINCT SP.PNO, P.CITY FROM SP LEFT OUTER JOIN P USING (PNO); PNO CITY --------------- P1 LONDON P2 PARIS SELECT DISTINCT SP.PNO, P.CITY FROM SP LEFT OUTER JOIN P ON (P.PNO = sp.pno); PNO CITY --------------- P1 LONDON P2 ? P2 PARIS SELECT DISTINCT SP.PNO, P.CITY FROM SP RIGHT OUTER JOIN P ON (P.PNO = sp.pno); PNO CITY --------------- P1 LONDON P2 PARIS ? PARIS SELECT DISTINCT SP.PNO, P.CITY FROM SP FULL OUTER JOIN P ON (P.PNO = sp.pno); PNO CITY --------------- P1 LONDON P2 ? P2 PARIS ? PARIS SELECT DISTINCT SP.PNO, P.CITY FROM SP INNER JOIN P ON (P.PNO = sp.pno); PNO CITY --------------- P1 LONDON P2 PARIS -Jose'-