AW: [HACKERS] JOIN syntax. Examples?

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