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:
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
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)