Join or Union table with same Characteristics and different Measures

Question

I would like to understand the easy/better way to join 2 tables with same characteristics and different measures as an example described below:

tab1

Col1	Col2	Measure1
1	1	10
1	2	5

tab2

Col1	Col2	Measure2
1	1	20
2	1	25

Expected Result

Col1	Col2	Measure1	Measure2
1	1	10	20
1	2	5	0
2	1	0	25

Questions:

How to avoid message: Ambiguous column name col1?
How to create a correct Join?

I have tried:

select col1, col2, t1.Measure1, t2.Measure2
from tab1 t1
full outer jon tab2 t2
on t1.col1 = t2.col1
and t1.col2 = t2.col2

I have tried a Union and it works, but i am looking a easy way using joins:

Select col1, col2, Measure1, 0 as Measure2 From tab1
Union
Select col1, col2, 0 as Measure1, Measure2 From tab2

Please tag your question with the database you are running : oracle, postgresql, sql-server...? — GMB, Dec 07 '22 at 22:34

score 3 · Accepted Answer · answered Dec 07 '22 at 22:36

The full join is the correct approach. But you need to disambiguate col1 and col2 in the select clause: both tables have both columns, so it is unclear to which column an unprefixed col1 refers.

A typical approach uses coalesce():

select 
    coalesce(t1.col1, t2.col1) col1,
    coalesce(t1.col2, t2.col2) col2,
    coalesce(t1.measure1, 0) measure1, 
    coalesce(t2.measure2, 0) measure2
from tab1 t1
full outer jon tab2 t2 
    on t1.col1 = t2.col1 and t1.col2 = t2.col2

Note that you also need coalesce() around the measures to return 0 instead of null on "missing" values.

In some databases (eg Postgres), you can use the using syntax to declare the join conditions for columns that have the same name across the tables ; this syntax automagically disambiguates the unprefixed column names, so:

select 
    col1,
    col2,
    coalesce(t1.measure1, 0) measure1, 
    coalesce(t2.measure2, 0) measure2
from tab1 t1
full join tab2 t2 using (col1, col2)

score 0 · Answer 2 · answered Dec 07 '22 at 22:37

You should reference source tables for col1 and col2 as well.

As you're using FULL OUTER JOIN I'd recommend using COALESCE statement.

SELECT COALESCE(t1.col1, t2.col1) as col1,
       COALESCE(t1.col2, t2.col2) as col2, 
       t1.Measure1, 
       t2.Measure2
FROM tab1 t1
FULL OUTER JOIN tab2 t2
on t1.col1 = t2.col1
and t1.col2 = t2.col2

Join or Union table with same Characteristics and different Measures

2 Answers2