Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

closed as off topic by jcolebrand May 17 '12 at 22:34

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

2  
Why the downvotes? It's a little basic, probably better-suited for SO, but specific and clearly expressed. –  Jon of All Trades May 17 '12 at 17:27
    
@JonofAllTrades Can't speak for everyone, but suspect "lack of research" –  Aaron Bertrand May 17 '12 at 17:39

2 Answers 2

Use this function:

COALESCE( a.id, b.id )
share|improve this answer
SELECT A.ID, B.ID, AB=ISNULL(A.ID,B.ID)
FROM A
FULL OUTER JOIN B ON A.ID = B.ID
share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.