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
>