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