What is the use of Coalesce in SQL Server?

In this article, We will see how to use Coalesce in SQL Server with examples. It is one of the most important functions in SQL. It seems to be a simple function but what it does is a lot more.

The dictionary meaning of Coalesce is combine (elements ) in a mass or whole.

According to the MSDN books online Coalesce in SQL Server returns first non null value.

Let us understand what we mean by this.

If you look at the customer table, we got the ID, Custer First Name, Middle Name and Last Name.

If you just look at the customer name some of them have their first name and some of them have their middle and last name and some of them have just their last name.

Now we want to write a query which pulls out the ID and name of the person as shown below.

Let us see our action item here.

When you pull out the name if the customer has got the first name you pull that out.

But whereas if the customer does not have the first name and if he has middle and last name then we want to pull their middle name.

If an employee does not have first name and middle name and only if he has last name and pull the last name.

Whereas if the customer has first, middle name we just want their first name.

Similarly if they have all of their names first, middle and last name still we just want their first name.

The priority should first go to their first name. If that presents pull it out if not go the second name if first and last name not there then only pull it out the last name.

So that is how our output has to be.

In order to do that we can use the Coalesce function.

Coalesce Function Syntax

Select Coalesce (Column 1, Column 2,... Column N) 
From Table Name

Coalesce SQL Query

SELECT ID, Coalesce 
(Customer First Name, Customer Middle Name, 
Customer Last Name) As Name 
FROM Customers;

Now if you look at the query here to the Coalesce function we are passing in ID and name of the person.

So obviously in the select clause we have the ID and to pull out the name we are using the Coalesce function.

We are not saying the first name, middle name, and the last name directly. We are passing those column names into the Coalesce function.

Look at the definition of the Coalesce function it returns the first non-null value.

What’s going to happen when you execute this query this Coalesce function will check the first row and the first name is available then it immediately returns the name.

Whereas in the second row, Is this first name available in the first column?

The answer is no.

What happens it checks the middle name, if middle name presents then it is going to return that.

Lets see the query and its result

Results for this query will be

Coalesce in sql server is the same as Coalesce oracle. It gives the same results.

Is this article useful to you?

Please let me know your feedback as comment.

You May Also Like

2 thoughts on “What is the use of Coalesce in SQL Server?”

  1. I like what you guys are up also. Such smart work and reporting! Carry on the excellent works guys I’ve incorporated you guys to my blogroll. I think it’ll improve the value of my website 🙂

    Reply

Leave a comment