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