SQL Server – What Exactly is COALESCE Function?

CodeProject

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.

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:

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.

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.

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