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(at)avantec(dot)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(at)alumni(dot)caltech(dot)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
>