473,323 Members | 1,560 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

FULL OUTER JOIN for oracle8(i)


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

How about:

select *
from tab_a
union all
select *
from tab_b;

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

Thanks to you all!!
:)

David

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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Dave | last post by:
Hi I have the following 4 tables and I need to do a fully outerjoin on them. create table A (a number, b number, c char(10), primary key (a,b)) create table B (a number, b number, c ...
0
by: reneecccwest | last post by:
SELECT d.code, d.description, v.code AS divCode, v.descripton AS divDescript, b.code AS brhCode, b.description AS brhDescript FROM Department d FULL OUTER JOIN Division v
7
by: alexcn | last post by:
I have the following query: SELECT dbo.tSymExch.exCode, dbo.tSymGrp.sgCode, dbo.tSymMain.smCode FROM dbo.tSymExch FULL OUTER JOIN dbo.tSymGrp ON dbo.tSymExch.exID =...
2
by: Lee | last post by:
I have two identical schema tables (one in a production library and another in a development library) and I need to be able to easily compare the data in them for errors and omissions. With...
2
by: Cory | last post by:
i have two tables, Event1 and Event2. They both have fields called contactID and eventID in them. Some of the contactID's between the tables are equal and some are not. I want to do a full outer...
1
by: Martijn van Oosterhout | last post by:
Today I got the error: ERROR: FULL JOIN is only supported with mergejoinable join conditions Which is really annoying since a full join is exactly what I wanted. I guess the alternative is to...
1
by: shilpasharma | last post by:
Hi, Can anybody let me know how I can optimise following Query. Select * from reports where ( exists ( SELECT 1 FROM results_required rr, item_claims_trials ict, results res WHERE...
0
by: brunodamato | last post by:
In the example that follows, I am receiving an incorrect result set in the View. I am looking to get the FULL result set from View_LY and Table_TY. Instead, this View is returning the Common (Equi)...
3
by: DMOK | last post by:
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
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.