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

  1. select coalesce(p1, p2, p3.....n)
Let's take an example to understand more clearly how coalesce function works :
  1. select coalesce(null, 1)
  2. select coalesce(null, null, 1)
  3. select coalesce(null, null, 1, null)
  4. select coalesce(1, 2)
OUTPUT

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.
  1. select coalesce(null, null)
OUTPUT

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
  1. declare @i int
  2. select coalesce(null, @i)
OUTPUT

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:
  1. case when expression1 is not null then expression1
  2. when expression1 is not null then expression1
  3. ...
  4. when expressionN is not null then expressionN
  5. end
Let's take an example to show how coalesce can be used in place of case expression:
  1. declare @tab1 table(id int, value varchar(10))
  2. insert into @tab1 values (1, 'val1')
  3. insert into @tab1 values (2, null)
  4. insert into @tab1 values (3, null)
  5. insert into @tab1 values (4, null)
  6. declare @tab2 table(id int, value varchar(10))
  7. insert into @tab2 values (1, null)
  8. insert into @tab2 values (2, 'val2')
  9. insert into @tab2 values (3, null)
  10. insert into @tab2 values (4, null)
  11. declare @tab3 table(id int, value varchar(10))
  12. insert into @tab3 values (1, null)
  13. insert into @tab3 values (2, null)
  14. insert into @tab3 values (3, 'val3')
  15. insert into @tab3 values (4, null)
  17. select t1.id
  18. , case when t1.value is not null then t1.value
  19. when t2.value is not null then t2.value
  20. when t3.value is not null then t3.value
  21. end as [value using case]
  22. , coalesce(t1.value, t2.value, t3.value) as [value using coalesce]
  23. from @tab1 t1
  24. inner join @tab2 t2 on t1.id = t2.id
  25. inner join @tab3 t3 on t1.id = t3.id
OUTPUT

output