CODEWITHSUNDEEP
sqldatabaseoracle-databasejoin
user2914613
user2914613

Reputation: 55

How to join oracle tables

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?

Upvotes: 1

Views: 101

Answers (3)

Satwik Nadkarny
Satwik Nadkarny

Reputation: 5143

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!!!

Upvotes: 1

Sergey Dmitriev
Sergey Dmitriev

Reputation: 91

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

Upvotes: 2

Iommi
Iommi

Reputation: 30

I guess a full join would be correct

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

Upvotes: 0

Related Questions