Horton

Thread

  1. RE: [HACKERS] JOIN syntax. Examples?

    Jackson, DeJuan <djackson@cpsgroup.com> — 1998-12-11T18:28:01Z

    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
>

  2. Re: [HACKERS] JOIN syntax. Examples?

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-12-11T22:11:28Z

    > 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

  3. Re[2]: [HACKERS] JOIN syntax. Examples?

    jose' soares <sferac@bo.nettuno.it> — 1999-01-11T14:45:51Z

    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'-

  4. Re: [HACKERS] JOIN syntax. Examples?

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-01-12T03:05:16Z

    > 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

  5. Re[2]: [HACKERS] JOIN syntax. Examples?

    jose' soares <sferac@bo.nettuno.it> — 1999-01-12T14:15:35Z

    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'-