Coalesce in SQL Server

Coalesce function accepts "n" number of arguments and returns the first non-null expression of the arguments. If all the arguments are null then it returns null.

Syntax

select coalesce(p1, p2, p3.....n)

Let's take example to understand more clearly how coalesce function works

select coalesce(null, 1)
select coalesce(null, null, 1)
select coalesce(null, null, 1, null)
select coalesce(1, 2)

OUTPUT

Look at the output, we are getting value as "1" in each output because in all select statements "1" is first non-null value in the arguments.

NOTE: At least one of the null values must be a typed NULL.

select coalesce(null, null)

OUTPUT

In the above select statement we are passing NULL as value in all arguments and NULL are not typed, so we are getting the error.

Now, let's try with NULL values as typed

declare @i int
select coalesce(null, @i)

OUTPUT

In this Example, it worked fine without any error because values of the argument are still NULL but at least one of them is typed.

Coalesce can be used in place of following case expression

case when expression1 is not null then expression1
     when expression1 is not null then expression1
     ...
     when expressionN is not null then expressionN
end

Let's take an example to show how coalesce can be used in place of case expression

declare @tab1 table(id int, value varchar(10))
insert into @tab1 values (1, 'val1')
insert into @tab1 values (2, null)
insert into @tab1 values (3, null)
insert into @tab1 values (4, null)
declare @tab2 table(id int, value varchar(10))
insert into @tab2 values (1, null)
insert into @tab2 values (2, 'val2')
insert into @tab2 values (3, null)
insert into @tab2 values (4, null)
declare @tab3 table(id int, value varchar(10))
insert into @tab3 values (1, null)
insert into @tab3 values (2, null)
insert into @tab3 values (3, 'val3')
insert into @tab3 values (4, null)

select  t1.id
        , case when t1.value is not null then t1.value
               when t2.value is not null then t2.value
               when t3.value is not null then t3.value
          end as [value using case]
        , coalesce(t1.value, t2.value, t3.value) as [value using coalesce]
from    @tab1 t1
inner join @tab2 t2 on t1.id = t2.id
inner join @tab3 t3 on t1.id = t3.id

OUTPUT