How can I make one table that "overrides" another?

Question

Suppose I have two tables:

CREATE TABLE A(
    id INT PRIMARY KEY,
    x INT,
    y INT
)

CREATE TABLE B(
    id INT PRIMARY KEY,
    x INT,
    y INT,
)

Table A contains data brought in from another vendor while table B is our data. For simplicity, I've made these tables be exactly the same in terms of schema, but table B would likely be a superset of table A (it would contain some columns that table A wouldn't in other words).

What I would like to do is create a view C with columns id, x, and y such that the values come from table B unless they're NULL in which case they would come from table A. For instance, suppose I had the following:

INSERT INTO A (id, x, y)
VALUES (1, 2, 3);

INSERT INTO B (id, x, y)
VALUES (1, NULL, NULL);

INSERT INTO A (id, x, y)
VALUES (2, 3, 4);

INSERT INTO B (id, x, y)
VALUES (2, 5, 6);

INSERT INTO A(id, x, y)
VALUES (3, 4, 5);

INSERT INTO B(id, x, y)
VALUES (3, 5, NULL);

So that if I select * from C, I'd get the following rows:

(1, 2, 3)
(2, 5, 6)
(3, 5, 5)

How could I create such a view?

Answer 1

You could join the tables together with a left join, and then select the right columns with a case:

select  case when A.x is null then B.x else A.x end
,       case when A.y is null then B.y else A.y end
from    A
left join
        B
on      A.id = b.id

Answer 2

Try:

Create view C as
select  B.ID,
        coalesce(B.x,A.x) x,
        coalesce(B.y,A.y) y
from    B
left join A
on   B.ID = A.ID