Click here to Skip to main content
Click here to Skip to main content

SQL Server – What Exactly is COALESCE Function?

, 20 Jan 2014
Rate this:
Please Sign up or sign in to vote.
What exactly is COALESCE function in SQL Server?

Introduction

In the last blog post, we discussed about different Element Operations in LINQ. You can read that article here. In this article, we will go over COALESCE function in SQL Server.

According to MSDN books online, COALESCE function returns the first Non NULL value. What is meant by this? Let’s try to understand it through this article.

Names

If we look at the tblEmployee table which is shown above, we can see that tblEmployee table has 4 columns – Id, FirstName, MiddleName and LastName. If we look at these employees, we can see that some of them have just First Name, some of them have Middle and Last Names and some of them have just the Last Names.

Now we want to write a query which pulls out the Id and Name of the Employee. The criteria for retrieving Names are following:

  • If the Employee has got the First Name, we have to pull out that.
  • If the Employee doesn’t have a First Name and he has a Middle and a Last Name, then we want to pull out his Middle Name.
  • If the Employee doesn’t have First Name and Middle Name and he has only the Last Name, then we want to pull his Last Name.
  • But if an Employee has both First Name and Middle Name, then we just want his First Name.
  • Similarly, if an Employee has all of the Names – First, Middle and Last Names, still we want his First Name.
  • So the priority should first go to First Name, then to Middle Name and finally to Last Name.

So our output should be like below:

Output

In order to do that, we can use COALESCE function.

SELECT Id, COALESCE(FirstName,MiddleName,LastName) AS Name
FROM tblEmployee

Here we are passing the column names to the COALESCE function. So what is happening while executing the query? Let’s examine this row by row.

  1. In the first row, COALESCE function will check whether First Name is available. Yes, the First Name is available and is not null. So it will immediately return Smith.
  2. When it comes to the second row, it will check whether the First Name is available or not. No, it is null. Then it will check the Middle Name. Is the Middle Name present? Yes, so the Middle Name, Thomas is retrieved.
  3. In the third row, COALESCE function will check whether the First Name is available or not. No, it is null. Then it will check for the Middle Name. It is still null. So it will go to the Last Name which is not null and will return Priyanka.
  4. In the fourth row, First Name itself is present. So it does not even bother to check the Middle Name.
  5. In the fifth row as well, First Name itself is present. So the COALESCE function will immediately return the First Name which is Simon.

Let’s quickly write the query in SQL Server Management Studio(SSMS) and see the results in action.

We exactly have the same table, tblEmployee with same names.

tblEmployee

Then we write the query to get Id and Name from tblEmployee table using COALESCE function. While executing the query, we will get the same output as expected.

COALESCE function

Reference: Arun Ramachandran (http://BestTEchnologyBlog.Com)


License

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

About the Author

Arun Ramachandran India
Software Developer
India India
Arun Ramachandran is a Software Engineer having hands on experience in different Microsoft Technologies who is presently working in Experion Technologies, India. He has written over 95 articles on the subject on his blog at http://BestTEchnologyBlog.com. Along with 3 years of hands on experience he holds a Master of Computer Applications degree from Cochin University of Science & Technology (CUSAT).

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 20 Jan 2014
Article Copyright 2014 by Arun Ramachandran India
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid