Return all the columns from either of the table in left join

Question

I am trying to return the rows from table1 if left join table doesn't have value else I am returning left join's table value for all the columns like this

select 
 (case when u2.Id is null then u1.Name else u2.Name end) as Name,
 (case when u2.Id is null then u1.Department else u2.Department end) as Department,
 (case when u2.Id is null then u1.Office else u2.Office end) as Office
from user u1
left join user u2 on u1.TerminationDate = u2.TerminationDate

This is a working solution but isn't there any better approach where I do not have to put so many case... when... statements, because I have only included few columns in the example but there are many columns and I feel there should be some better approach to achieve this.

Answer 1

Use COALESCE

select 
coalesce(u2.Name, u1.Name) as Name,
coalesce(u2.Id, u1.Department) as Department,
coalesce(u2.Id, u1.Office) as Office,
from user u1
left join user u2 on u1.TerminationDate = u2.TerminationDate

Answer 2

Using coalesce() or isnull().

coalesce() is ansi (standard) sql and supports more than two parameters, where as isnull() is sql server specific and only supports two parameters. There are other differences as well.

select 
    coalesce(u2.Name, u1.Name) as Name
  , ...

alternatively (non-ansi syntax for sql server):

select 
    Name = isnull(u2.Name, u1.Name)
  , ...