Coalesce Function 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 an 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 the 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 the 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