Selecting updated rows between two tables

Good day,

I have to identical table structures. One is base table and second has incremental changes. I wish to have resulting table so that I pick he rows from the update, if present. Otherwise from old table.

Like so

table a: | Table b: | Result table: 
key val  |  key val | key val 
1   a    |  2   bb  |  1   a   
2   b    |  4   dd  |  2   bb
3   c    |  5   ee  |  3   c
4   d    |          |  4   dd
                       5   ee

I tried selecting B table values on inner join and full outer join where a.key is NULL or b.key is NULL and union. (Code not tested to functionality yet.)

select * from 
(
  (select table_B.* from 
    TABLE_A
      inner join 
    TABLE_B
    on TABLE_A.key = TABLE_B.key 
  )
  UNION 
  (select * from 
    TABLE_A
     full outer join
    TABLE_B
  on TABLE_A.key = TABLE_B.key 
  where TABLE_A.key is NULL or TABLE_A.key is NULL
  )
)

Is there an easier way to do this? I got the feeling that there has to be a more robust way to do this.

EDIT: I’m currently implementing this in the SPARK SQL context, but have had similar issues in the SQL server environment. Yet, I’m looking for a general idea.

Answer

From my understanding this would help

select key, val from Tableb
union
select key, val from Tablea a 
where not exists(select 1 from Tableb where a.key = b.key)