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.
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
from t1
union
select id, name
from t2
select id, name
from t1
union all
select id, name
from t2
where not exists (select 1 from t1 where t1.id = t2.id)