SQL merge duplicate non-key columns, overwriting NULL values-CodePudding

I'm trying to merge columns with the following format:

Table A:

Key Value
1   Null
2   "a"
3   "b"

Table B:

 Key  Value
 1    "c"
 2    NULL
 3    "b"

Output should be:

 Key Value
 1   "c"
 2   "a"
 3   "b"

If you just do a join on Key you get a duplicate Value column for both tables, but I want to merge the two value columns. In all cases they will have either (a) the same value or (b) one will be NULL and the other will have the value.

CodePudding user response：

select a.key,coalesce(a.value,b.value)
from table_a  a
join table_b b on a.key=b.key

CodePudding user response：

The simplest way to do this, given your 2 conditions is UNION:

SELECT key, value FROM TableA WHERE value IS NOT NULL
UNION
SELECT key, value FROM TableB WHERE value IS NOT NULL