SQL SERVER – Uses of COALESCE() Function

Coalesce()  can be used to return the first Non-Null argument value among a series of values specified. If all the parameter/arguement values are null, then this  function will return the NULL value.

Examples:

Select Coalesce(NULL,NULL,34,NULL,23)  'First Non-Null Value'

Output :

Select Coalesce('',NULL,'','XYZ')  'First Non-Null Value'

Output :

Declare @Str varchar(10)
Select Coalesce(NULL,@Str,NULL)  'First Non-Null Value'

Output :

Let us consider a table which contains the marks of all the students who belongs to either Engineering or Medical streams.

If the student belongs to the Engineering stream, then the last two columns will have NULL values  where as if he belongs to Medical streams then the Mathematics column will have a null value.

Now Let us see how the query follows.

Create Table  StudentMarks(Id int identity(1,1), GroupId int , Mathematics int , Physics int ,Chemistry int , Zoology int ,Botany int )

Insert Into StudentMarks (GroupId, Mathematics, Physics, Chemistry, Zoology, Botany)
Select 1,100,96,98,NULL,NULL
Union All
Select 1,90,95,94,NULL,NULL
Union All
Select 2,NULL,94,99,89,97
Union All
Select 2,NULL,99,99,99,97

Now here is the query to calculate the total marks that each student, by using the Coalesce() 

Select ID, GroupId, Coalesce(Mathematics,0) + Coalesce(Physics,0) + Coalesce(Chemistry,0) + Coalesce(Zoology,0) + Coalesce(Botany,0) 'Total Marks'
From StudentMarks

Output :

Again Coalesce() can be used to Build Comma-Delimited String.

Here is an example to Build a Comma-Delimited String that contains all the columns of a table using the Coalesce() function.