How can I override rows from another table?

I have two tables:

TableA

ID  Name
--  ----
1   aaa
2   bbb
3   ccc
4   ddd

TableB

ID  Name
--  --------
3   WWXXYYZZ

I want to select from both tables, but skip the rows which exist in TableB. The result should look like this:

ID  Name
--  --------
1   aaa
2   bbb
3   WWXXYYZZ
4   ddd

I have tried union and join but did not figure out how to achieve this.

-- Did not work
select *
from TableA
union
select *
from TableB

-- Did not work
select *
from
(
    select *
    from TableA
) x
join
(
    select *
    from TableB
) y
on x.ID = y.ID

Answer

You could left join b on to a, and use coalesce to prefer b‘s rows:

SELECT    a.id, COALESCE(b.name, a.name) AS name
FROM      a
LEFT JOIN b ON a.id = b.id

Advertisement

Answer