Search code examples
sqlprestofull-outer-joinsql-null

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


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

Solution

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