By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,321 Members | 1,206 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,321 IT Pros & Developers. It's quick & easy.

FULL OUTER JOIN for oracle8(i)

P: n/a

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
Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
-- 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

Jul 19 '05 #2

P: n/a

How about:

select *
from tab_a
union all
select *
from tab_b;

--
Posted via http://dbforums.com
Jul 19 '05 #3

P: n/a

Thanks to you all!!
:)

David

--
Posted via http://dbforums.com
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.