Chirag Gandhi Chirag Gandhi - 5 months ago 9
SQL Question

Joining tables with common columns in Oracle SQL

I have 2 tables as follows.
Table A:

Col1 Col2 Col3
3 6 300
2 3 400
3 5 200


and this is Table B:

Col1 Col2 Col4
3 5 500
3 7 900
2 4 300
2 3 100


The output I want is: Table C

Col1 Col2 Col3 Col4
3 5 200 500
3 7 0 900
2 4 0 300
2 3 400 100
3 6 300 0


Values in col3 and col4 are a function of values in col1 and col2. I wish to join Tables A and B as shown in the example below. I have tried full outer join but it does not return zeros where there are no records and it returns all columns from both the tables. How do I get the output as shown? Thank you.

Answer

Full outer join is a good idea, use NVL to sobstitute null values with 0

select NVL(t1.col1,t2.col1), NVL(t1.col2,t2.col2), NVL(col3,0), NVL(col4,0)
from t1
full outer join t2
on t1.col1 = t2.col1 and t1.col2 = t2.col2

With another table:

select NVL(NVL(t1.col1,t2.col1),t3.col1), 
       NVL(NVL(t1.col2,t2.col2),t3.col2), 
       NVL(col3,0), NVL(col4,0), NVL(col5,0)
from t1
full outer join t2
on t1.col1 = t2.col1 and t1.col2 = t2.col2
full outer join t3
on t1.col1 = t3.col1 and t1.col2 = t3.col2
   and t2.col1 = t3.col1 and t2.col2 = t3.col2

Sorry I've not tried it ... tell me if there is some problem.

Comments