sqlselectunioncoalescefull-outer-join

Join or Union table with same Characteristics and different Measures


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

Solution

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