full outer join where join key in result always has value [closed]

Question

I wish to do a full outer join between two tables A, B joining on A.ID = B.ID. Now, it is quite easy to include A.ID and B.ID in a result set. this will include rows where:

• A.ID is null, B.ID is not null
• B.ID is null, A.ID is not null
• A.ID and B.ID have the same value.

I want to have a third column which be equal to:

• A.ID and B.ID when they are the same value
• A.ID when A.ID has a value and B.ID is null
• B.ID when B.ID has a value and A.ID is null

How do I do this?

Answer 1

Use this function:

COALESCE( a.id, b.id )

Answer 2

SELECT A.ID, B.ID, AB=ISNULL(A.ID,B.ID)
FROM A
FULL OUTER JOIN B ON A.ID = B.ID