Re: FULL OUTER JOIN for oracle8(i)

• Use something like this:
• First, the table definitions (so you can test this): create table tab_a (key number primary key, nonkey number); create table tab_b (key number primary key, nonkey number);
• Then, populate them: begin for i in 1 .. 20 loop insert into tab_a values (2*i, 2*i); insert into tab_b values (3*i, 3*i); end loop; commit; end; /
• Finally, run the query: select * from tab_a, tab_b where tab_a.key = tab_b.key union select a.key, a.nonkey, null, null from tab_a a where not exists (select * from tab_b where tab_b.key = a.key) union select null, null, b.key, b.nonkey from tab_b b where not exists (select * from tab_a where tab_a.key = b.key);
• Of course, this can be reduced to something like this:

select *
from tab_a, tab_b
where tab_a.key = tab_b.key (+)
union
select null, null, b.key, b.nonkey
from tab_b b
where not exists
(select * from tab_a
 where tab_a.key = b.key);

• Or this:

select *
from tab_a, tab_b
where tab_a.key (+) = tab_b.key
union
select a.key, a.nonkey, null, null
from tab_a a
where not exists
(select * from tab_b
 where tab_b.key = a.key);

• I posted the *long* version (the first one) since the last two are not "standard SQL," as you requested in your post.

-- 
Cheers,
Chris

___________________________________

Chris Leonard, The Database Guy
http://www.databaseguy.com

Brainbench MVP for Oracle Admin
http://www.brainbench.com

MCSE, MCDBA, OCP, CIW
___________________________________

"DMOK" <member31876_at_dbforums.com> wrote in message
news:3040398.1056530505_at_dbforums.com...


>

> Dear all,

>

> I need some help. How do I full join two tables (with two columns each)

> together using the standard sql way on oracle 8?

>

> David

>

> --

> Posted via http://dbforums.com

>