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

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