COALESCE in SQL Server

Introduction

In this tip, we will learn about COALESCE function and how it works.

Background

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.

Using the Code

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 the 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 the following case expression.

case when expression1 is not null then expression1
     when expression2 is not null then expression2
     ...
     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