SQL Server – What exactly is COALESCE function?

In the last blog post, we have 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 is 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)

3 thoughts on “SQL Server – What exactly is COALESCE function?

  1. Woah! I’m really loving the template/theme of this website.
    It’s simple, yet effective. A lot of times it’s tough to get that “perfect balance” between usability and appearance.

    I must say you’ve done a superb job with this.
    Also, the blog loads very fast for me on Firefox. Outstanding Blog!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s