How to join oracle tables

Question

I have two tables.

Table 1

ID        STRING
1          ABC
2          CDE
3          FGH

Table 2

ID        STRING
1          xyz
2          uvw
4          abc

I want the output as

ID    STRING       STRING2
1      ABC          xyz
2      CDE          uvw
3      FGH          null
4      null         abc

which join should I use. Is it possible to do this in simple SQL query?

Which part of the [documentation](http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm) needs to be clarified? — Pred, Jun 24 '14 at 13:42
You need a full outer join [here are examples](http://www.techonthenet.com/oracle/joins.php) — Jens, Jun 24 '14 at 13:47

score 2 · Answer 1 · answered Jun 24 '14 at 14:30

with 
t1 as
  (select 1 id, 'ABC' string from dual
   union 
   select 2, 'CDE' from dual
   union
   select 3, 'FGH' from dual
  ),
t2 as
  (select 1 id, 'xyz' string from dual
   union 
   select 2, 'uvw' from dual
   union
   select 4, 'abc' from dual)
select COALESCE(t1.id,t2.id) id, t1.string, t2.string string2 
from t1 full outer join t2 on (t1.id = t2.id)
order by 1

Satwik Nadkarny · Answer 2 · 2014-06-24T16:40:34.120

1

What you can do is use Union to combine two different result sets. That will give you exactly what you're looking for:

SELECT     tab1.ID,
           tab1.name,
           tab2.name2
FROM       tab1 tab1
LEFT JOIN tab2 tab2 ON tab1.ID = tab2.ID
UNION

SELECT     tab2.ID,
           tab1.name,
           tab2.name2
FROM       tab1 tab1
RIGHT JOIN tab2 tab2 ON tab1.ID = tab2.ID

You can see that here-> http://sqlfiddle.com/#!4/cf9e2/10

Hope this helps!!!

edited Jun 24 '14 at 16:40

answered Jun 24 '14 at 13:53

Satwik Nadkarny

5,086
2
23
41

1

Nice brother... you r faster then me... i was also trying the same . – Nisar Jun 24 '14 at 14:13
1

@Nisar Hehe..Post it. Don't let the effort go waste!! Also it would make it clear to people that this is not just a "let's do a full outer join" thing.!!C'mon post it!! – Satwik Nadkarny Jun 24 '14 at 14:15
@satwik , brother one more thing , if there is 3 tables do we need to write six unions? – user2914613 Jun 24 '14 at 14:52
@user2914613 If you have 3, 4 or more such tables, you'd be better off creating a view or using temporary tables!! – Satwik Nadkarny Jun 24 '14 at 14:57
I'm sorry Satwik but the general rule in Oracle is to [_never_ use temporary tables](http://stackoverflow.com/questions/2918466/ways-to-avoid-global-temp-tables-in-oracle) if you can avoid it, cc @user2914613. And this is why FULL OUTER JOINS can be useful. – Ben Jun 24 '14 at 15:33
Why the downvote? This answer is correct. You can see that in the fiddle? So what's the reason for the downvote? – Satwik Nadkarny Jun 24 '14 at 15:39
I've already told you; you're giving out incorrect advice _"A Full Outer Join won't give you the desired results"_ (see [Sergey's answer](http://stackoverflow.com/a/24389286/458741) or [this fiddle](http://sqlfiddle.com/#!4/5e541/3)) and bad advice as I explained in [my comment](http://stackoverflow.com/questions/24388186/how-to-join-oracle-tables/24388465#comment37724822_24388465). – Ben Jun 24 '14 at 16:13
@Ben Incorrect advice? I asked you how would achieve it? And you answered that in the next comment. Anyone who's reading it will know now how you would achieve it. So my incorrect advice (or rather lack of knowing) is invalidated there. That's the first thing! – Satwik Nadkarny Jun 24 '14 at 16:17
@Ben Secondly, the answer above DOES solve the OP's issue (irrespective of whether you like it or not). If there is one sentence in the answer which is not correct, you have the edit button to make the change (and that's not a pain for you considering that you edited the other answer). Certainly, the whole answer is not wrong or deserving of a downvote!!! – Satwik Nadkarny Jun 24 '14 at 16:23
@Ben Also, the answer that you believe is absolutely correct DOES NOT WORK as its stands. You'd need to use `COALESCE` as you stated in the comment. So as its stands (unless you add `COALESCE`), your answer is wrong (and that too by your admission). – Satwik Nadkarny Jun 24 '14 at 16:28
@Ben And instead of correcting these answers (mine as well as yours), both in need of only minor editing, mind you, in order to have two 'good complete answers', you seem to be more interested in 'voting' and downvoting answers, when in fact, you should be more interested in improving the quality of answers here!! – Satwik Nadkarny Jun 24 '14 at 16:34
I haven't written an answer @satwik. I was going to rewrite the answer I first commented on but there's already a completely correct answer here and I have pointed out where you're going wrong, complete with links to a lot more information and examples. As it stood your answer _was_ wrong and you've been giving bad advice in the comments (I assume you're more used to SQL Server?). This is what down votes are for, you hadn't paid attention until I did. However, as you've corrected it I'm removing my down vote. – Ben Jun 24 '14 at 17:12
hi guys, how a full outer join internally works, why it is not displaying the ID unless we are not using COALESCE ? If you can provide some good reference it will be great!! – user2914613 Jun 25 '14 at 05:49

score 0 · Answer 3 · edited Jun 24 '14 at 14:07

0

I guess a full join would be correct

select * from tab1 t1 full join tab2 t2 on t1.id = t2.id

edited Jun 24 '14 at 14:07

Ben

51,770
36
127
149

answered Jun 24 '14 at 13:42

Iommi

30
4

2

A FULL OUTER JOIN would be correct. A RIGHT OUTER won't return data from both sides of the join. – Ben Jun 24 '14 at 13:44
@Ben A FULL OUTER JOIN won't do. You need the ID values coming from two different columns in different tables. You see the ID value 3 is not present in Table2 and similarly ID value 4 is not present in table 1. How exactly would you combine them in a single result set? – Satwik Nadkarny Jun 24 '14 at 14:12
@Ben Also, as I suggested, the aforementioned query doesn't give the desired result. You can see that here->http://sqlfiddle.com/#!4/cf9e2/12 – Satwik Nadkarny Jun 24 '14 at 14:13
You'd use `COALESCE()` @Satwik... this is what a FULL OUTER JOIN is here to do and will be significantly better than 2 joins and a distinct sort in some situations, especially in later versions. – Ben Jun 24 '14 at 14:34

How to join oracle tables

Table 1

Table 2

3 Answers3