From: | Zeugswetter Andreas IZ5 <Andreas(dot)Zeugswetter(at)telecom(dot)at> |
---|---|
To: | "Thomas G(dot) Lockhart" <lockhart(at)alumni(dot)caltech(dot)edu> |
Cc: | Postgres Hackers List <hackers(at)postgreSQL(dot)org> |
Subject: | AW: [HACKERS] JOIN syntax. Examples? |
Date: | 1998-12-11 09:26:19 |
Message-ID: | 219F68D65015D011A8E000006F8590C60180FABE@sdexcsrv1.sd.spardat.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
>> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Hannu Krosing | 1998-12-11 11:07:10 | Re: [HACKERS] JOIN syntax. Examples? |
Previous Message | Michael Meskes | 1998-12-11 08:24:01 | Re: [HACKERS] JOIN syntax. Examples? |