JOIN syntax. Examples?

On Fri, 11 Dec 1998, Thomas G. Lockhart wrote:

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

I have access to several different versions of Oracle at work...if you
want to send me your test cases, go for it...work email is
'marc.fournier@acadiau.ca'...

Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

On Fri, Dec 11, 1998 at 06:35:39AM +0000, Thomas G. Lockhart wrote:

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.

Would Oracle qualify? I hope to get the Linux trial version next week.

Also, if anyone has worked with join syntax, outer joins especially, it
would be great to get some test case contributions...

Hmm Oracle's version of outer joins is completely different from Informix.

Michael
--
Dr. Michael Meskes, Manager of the Western Branch Office, Datenrevision GmbH
work: Cuxhavener Str. 36, D-21149 Hamburg, Michael.Meskes@datenrevision.de
home: Th.-Heuss-Str. 61, D-41812 Erkelenz, Michael.Meskes@usa.net
Go SF49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

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.

I have access to several different versions of Oracle at work...if

you

want to send me your test cases, go for it...work email is

Oracle is not a good Candidate for SQL92 Syntax.
The outer join is completely different.
A little better is Informix 7.30, and most conformant is probably DB2 V5.
Oracle uses (+) in the join quals like a.id (+) = b.id where table a has
missing entries.
In Informix you write outer before each table that can have missing entries.
DB2 has the left outer, right outer, and full outer join syntax.

Example:
Oracle: /* size and free space of all tablespaces (no row in dba_free_space
if nothing free for this tablespace) */
select s.tablespace_name as "tbsname", sum(s.bytes)/1024 as "size",
nvl(sum(f.bytes)/1024,0) as "free"
from dba_data_files s, dba_free_space f
where f.TABLESPACE_NAME (+) = s.TABLESPACE_NAME
group by s.TABLESPACE_NAME;

Informix: /* all tables + indexes if available */
select tabname, idxname
from systables t, outer sysindexes i
where t.tabid = i.tabid

DB2: /* all tables + indexes if available */
SELECT T.NAME, T.CREATOR , I.NAME, I.CREATOR
FROM SYSIBM.SYSTABLES T LEFT OUTER JOIN SYSIBM.SYSINDEXES I
ON T.NAME = I.TBNAME AND T.CREATOR = I.TBCREATOR

Andreas

Thomas G. Lockhart wrote:

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

You could use MS Access.

It has terrible outer join syntax (compared to Oracle), but I quess it
is more or less what SQL92 standard prescribes ;(

To make the queries, just open the query builder and join the tables,
then double-click on the join and set the property not to require all
from one of the tables and only matching from the other.

Here is whar it produced for me:

SELECT account.account_nr, domestic_po.bank_order_id
FROM account LEFT JOIN domestic_po ON account.account_nr =
domestic_po.account_nr;

this requires all fields from account and matching fields from
domestic_po

the oracle equivalent would be:

SELECT account.account_nr, domestic_po.bank_order_id
FROM account
WHERE account.account_nr = (+) domestic_po.account_nr;

BTW, I do think Oracle syntax to be much clearer, but I'm not sure
if we should allow it as and additional syntax.

----------------
Hannu

BTW, I do think Oracle syntax to be much clearer, but I'm not sure
if we should allow it as and additional syntax.

Do you know of any place I can look up Oracle's syntax? Or if not, could
I send you a (small) regression test for joins and have you translate
that to Oracle's syntax?

This brings up a question: I would guess that Oracle claims to conform
to SQL92 (or to be compliant, or to be an extended subset, or ??). afaik
this means for many companies that they conform to the "Entry Level"
part of SQL92. What are Oracle's claims on the subject? I'd like to know
where we stand on this relative to them; I've claimed that we are an
"extended subset" but perhaps we are closer than we think...

- Tom

Oracle is not a good Candidate for SQL92 Syntax.
The outer join is completely different.
A little better is Informix 7.30, and most conformant is probably DB2 V5.

You can now download DB2 5.2 beta for linux from the IBM web-site. Somebody
with a big enough computer (and probably net connection, I suppose it's a long
download:) could pick it up to do some testing.

Maarten

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

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).

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

"Thomas G. Lockhart" wrote:

Does anyone have a non-M$ RDBMS which implements SQL92 joins?

The book "The Practical SQL Handbook", which is often recommended on
these lists, uses the syntax `*=' and `=*' for left and right outer
joins (page 211). I think we ought to support this syntax as well,
since it will save new users from confusion.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"The spirit of the Lord GOD is upon me; because the
LORD hath anointed me to preach good tidings unto the
meek; he hath sent me to bind up the brokenhearted, to
proclaim liberty to the captives, and the opening of
the prison to them that are bound."
Isaiah 61:1

On Fri, 11 Dec 1998, Oliver Elphick wrote:

The book "The Practical SQL Handbook", which is often recommended on
these lists, uses the syntax `*=' and `=*' for left and right outer
joins (page 211). I think we ought to support this syntax as well,
since it will save new users from confusion.

'A Guide to The SQL Standard" (4th Ed.) seems to indicate that the MS
syntax is fairly close.

ISBN 0-201-96426-0

--
| Matthew N. Dodd | 78 280Z | 75 164E | 84 245DL | FreeBSD/NetBSD/Sprite/VMS |
| winter@jurai.net | This Space For Rent | ix86,sparc,m68k,pmax,vax |
| http://www.jurai.net/~winter | Are you k-rad elite enough for my webpage? |

The book "The Practical SQL Handbook", which is often recommended on
these lists, uses the syntax `*=' and `=*' for left and right outer
joins (page 211). I think we ought to support this syntax as well,
since it will save new users from confusion.

This one conflicts with Postgres' operator extensibility features, since
it would look just like a legal operator.

The two books I have at hand (besides my old Ingres docs) are A Guide to
the SQL Standard by Date and Darwen and Understanding the New SQL by
Melton and Simon. Both focus on SQL standard syntax, and neither mention
the various outer join syntaxes accepted by Oracle, Informix, or Sybase.

An explanation for the lack of standards compliance by the big three
probably involves the fact that they predate the standard by a
significant number of years.

- Tom

Thomas G. Lockhart wrote:

The book "The Practical SQL Handbook", which is often recommended on
these lists, uses the syntax `*=' and `=*' for left and right outer
joins (page 211). I think we ought to support this syntax as well,
since it will save new users from confusion.

This one conflicts with Postgres' operator extensibility features, since
it would look just like a legal operator.

so does =

Could it be possible to extend the operator extensibility features
to achieve the behaviour of outer/cross joins ?

The two books I have at hand (besides my old Ingres docs) are A Guide to
the SQL Standard by Date and Darwen and Understanding the New SQL by
Melton and Simon. Both focus on SQL standard syntax, and neither mention
the various outer join syntaxes accepted by Oracle, Informix, or Sybase.

Has anybody tried out DB2 ?

I have downloaded it (for linux) but have not yet tried it.

An explanation for the lack of standards compliance by the big three
probably involves the fact that they predate the standard by a
significant number of years.

Not to mention that both =* and =(+) are more concise and easier to
follow, at least for one with my headshape.

The standard is probably the 'worst common denominator' or something
like that :(

-----------------
Hannu

-----Original Message-----
Thomas G. Lockhart wrote:

The book "The Practical SQL Handbook", which is often

recommended on

these lists, uses the syntax `*=' and `=*' for left and

right outer

joins (page 211). I think we ought to support this

syntax as well,

since it will save new users from confusion.

This one conflicts with Postgres' operator extensibility

features, since

it would look just like a legal operator.

so does =

Could it be possible to extend the operator extensibility features
to achieve the behaviour of outer/cross joins ?

The two books I have at hand (besides my old Ingres docs)

are A Guide to

the SQL Standard by Date and Darwen and Understanding the New SQL by
Melton and Simon. Both focus on SQL standard syntax, and

neither mention

the various outer join syntaxes accepted by Oracle,

Informix, or Sybase.

Has anybody tried out DB2 ?

I have downloaded it (for linux) but have not yet tried it.

An explanation for the lack of standards compliance by the big three
probably involves the fact that they predate the standard by a
significant number of years.

Not to mention that both =* and =(+) are more concise and easier to
follow, at least for one with my headshape.

The standard is probably the 'worst common denominator' or something
like that :(

Microsoft SQL Server v6.5 also support the Sybase syntax =* and *=, but
I found that syntax for more limiting than the SQL92 Standard syntax.
I'm not sure how it would be implemented in Postgres but in both Sybase
and M$ SQL you couldn't perform an Outer Join on an Outer Joined table
using the =* *= syntax. Also, we'd need an extension for cross join,
*=*.

My vote is to implement the SQL92 Standard, it's far more flexible and
we don't have to shift with the tide when Oracle decides to change to %=
=% to be it's join operators.

And if it makes anyone feel better; the M$ SQL v6.5 docs state that *=
=* would be depreciated in M$ SQL v7.0. Haven't seen 7.0 so don't know
if they went through with it (or will).
-DEJ

This one conflicts with Postgres' operator extensibility features,
since it would look just like a legal operator.

so does =

But in fact its usage for joins matches the typical usage elsewhere.

Has anybody tried out DB2 ?
I have downloaded it (for linux) but have not yet tried it.

Just downloaded it this morning (and afternoon, it's a thin pipe at home
for 60MB of files :) Have you looked at what it takes to do an
installation yet?

Not to mention that both =* and =(+) are more concise and easier to
follow, at least for one with my headshape.
The standard is probably the 'worst common denominator' or something
like that :(

DeJuan points out a major strength of the SQL92 syntax, which allows
multiple outer joins in the same query. One of my books shows an
example:

select * from
q1 full outer join q2 on (q1.id = q2.id)
full outer join q3 on (coalesce(q1.id,q2.id)=q3.id)
full outer join q4 on (coalesce(q1.id,q2.id,q3.id)=q4.id)

I suppose one can do something similar using a *= operator by using
parentheses? Not sure though...

- Tom

Has anybody tried out DB2 ?
I have downloaded it (for linux) but have not yet tried it.

Just downloaded it this morning (and afternoon, it's a thin pipe at
home for 60MB of files :) Have you looked at what it takes to do an
installation yet?

Well, I'll have to save it for later, at least at home. It's glibc2
only. Also, the tar file has a bunch of rpms but also other files. Don't
know what's up with that...

- Tom

Hi all,

Has anybody tried out DB2 ?
I have downloaded it (for linux) but have not yet tried it.

Just downloaded it this morning (and afternoon, it's a thin pipe at
home for 60MB of files :) Have you looked at what it takes to do an
installation yet?

Could someone tell me please where I can download DB2?
Thanks,
-Jose'-

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

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

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