I have two tables with the same variables referring to attributes of a person.
How can I combine data from two such tables picking the best available value for each column from each table for each field?
Requirements:
Example:
Table 1:
user_id | age | income
1 | NULL| 58000
2 | 22 | 60000
4 | 19 | 35000
Table 2:
user_id | age | income
1 | 55 | 55000
2 | 19 | NULL
3 | 22 | 33200
Desired output:
user_id | age | income
1 | 55 | 58000
2 | 22 | 60000
3 | 22 | 33200
4 | 19 | 35000
I think that's a full join
and priorization logic with colaesce()
:
select user_id,
coalesce(t1.age, t2.age) as age,
coalesce(t1.income, t2.income) as income
from table1 t1
full join table2 t2 using(user_id)