UNION based on just one column

Question

I want to UNION two tables based on a single column.

Assume that, I have a table called t1:

Id |  Name  
------------
1  |    A   
2  |    B  
3  |    C   

And a second table called t2:

Id |  Name  
------------
1  |    B   
3  |    B  
5  |    B

I want to UNION them like this:

SELECT * FROM T1
UNION
SELECT * FROM T2
BASED ON ID 

And I expect a result like:

Id |  Name  
------------
1  |    A   
2  |    B  
3  |    C 
5  |    B

If IDs are equal then pick the row from the first table:

Actually, I am working with tables which have 20+ columns. These tables are for demonstration.

Answer 1

One option uses not exists:

select id, name
from t1
union all
select id, name
from t2
where not exists (select 1 from t1 where t1.id = t2.id)

You can also use conditional aggregation - although this is more cumbersome, and probably a little less efficient:

select
    id,
    coalesce(
        max(case when which = 1 then name end),
        max(case when which = 2 then name end)
    ) name
from (
    select id, name, 1 which from t1
    union all
    select id, name, 2 from t2
) t
group by id