Click here to Skip to main content
15,867,308 members
Articles / Programming Languages / T-SQL
Tip/Trick

COALESCE in SQL Server

Rate me:
Please Sign up or sign in to vote.
5.00/5 (4 votes)
15 Nov 2015CPOL1 min read 16.1K   5  
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

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

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

SQL
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.

SQL
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:

SQL
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.

SQL
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.

SQL
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --