-- 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" <me*********@dbforums.com> wrote in message
news:30****************@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
