Union replacing row if id is the same

Question

I have 2 different tables with same data structure, table A and B, is it possible to get values from A and then B where if the ID entry exists in B it replaces the value got in A?

Example:

select '1' as id, 'Bob' as "user" from dual
union
select '1' as id, 'Alice' as "user" from dual

This returns:

1   Bob
1   Alice

If id is the same in the second select I would like to have only one row:

1   Alice

Answer 1

Rather than a UNION, you need a "FULL OUTER JOIN", which will give you three types of row:

• Rows where both tables have a matching id, with values from both tables (the rows which an INNER JOIN would return)
• Rows where only table A has that id, with null for table B's columns (the additional rows which a LEFT OUTER JOIN would return)
• Rows where only table B has that id, with null for table A's columns (the additional rows which a RIGHT OUTER JOIN would return)

You can then use COALESCE to take the values from B if present (the first and second type of row), and A if not (the third type of row).

So for your example:

Select
    Coalesce(B.id, A.id) as id,
    Coalesce(B."user", A."user") as "user"
From
    (select '1' as id, 'Bob' as "user" from dual) as A
Full Outer Join
    (select '1' as id, 'Alice' as "user" from dual) as B
    On B.id = A.id

Which returns:

id	user
1	Alice

(Note: tested on SQL Server, by removing the "from dual", because I have no Oracle DB to test on.)