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

Fri, 11 Dec 1998 10:26:19 +0100
Zeugswetter Andreas IZ5 <Andreas.Zeugswetter@telecom.at>
AW: [HACKERS] JOIN syntax. Examples?
> 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


Fri, 11 Dec 1998 15:24:57 +0100
"Maarten Boekhold" <boekhold@tibco.com>
RE: [HACKERS] JOIN syntax. Examples?