SQL - How to pick the best available value for each column for each ID from multiple tables?

Question

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:

1. For each field, I would like to fill it with a value from either one of the tables, giving a preference to table 1.
2. Values can be NULL in either table
3. In the combined table, the value for column 1 could come from table 2 (in case table 1 is missing a value for that person) and the value for column 2 could from table 1 (because both tables had a value, but the value from table 1 is preferred).
4. In my real example, I have many columns, so an elegant solution with less code duplication would be preferred.
5. Some users may exist in only one of the tables.

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

Answer 1

Use full outer join if user_id in each table is unique.

SELECT
  COALESCE(t1.user_id, t2.user_id) AS user_id,
  GREATEST(t1.age, t2.age) AS age,
  GREATEST(t1.income, t2.income) AS income
FROM t1
FULL OUTER JOIN t2 ON t1.user_id = t2.user_id

Answer 2

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)

Answer 3

try like below using coalesce()

select t1.user_id, coalesce(t1.age,t2.age),
t1.income>t2.income then t1.income else t2.income end as income

table1 t1 join table2 t2 on t1.usesr_id=t2.user_id

Answer 4

You can use below code:

   With TableA(Id,age,income) as
(   --Select Common Data

    select table_1.id,
    --Select MAX AGE
    case 
        when table_1.age> table_2.age or table_2.age is null then table_1.age else table_2.age 
    end,
    --Select MAX Income
    case 
        when table_1.income>table_2.income or table_2.income is null then table_1.income  else table_2.income  
    end
    from table_1 inner join table_2 on table_2.id=table_1.id 

union all

    -- Select Specific Data of Table 2
    select table_2.id,table_2.age,table_2.income
    from table_2 
    where table_2.id not in (select table_1.id from table_1)

union all

    -- Select Specific Data of Table 1
    select table_1.id,table_1.age,table_1.income
    from table_1
    where table_1.id not in (select table_2.id from table_2)

)select * from TableA