What is the use of Coalesce in SQL Server?

Coalesce function in SQL server Infographic
Coalesce function Infographic

In this article, We will understand how to use Coalesce in SQL Server with examples.

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.

IDCustomer First NameCustomer Middle NameCustomer Last Name
1RajNULLNULL
2NULLPeterNULL
3NULLNULLThomas

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.

IDCustomer Name
1Raj
2Peter
3Thomas

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 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 sitll 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 SQL Query 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 and for the name we are using the Coalesce function.

We are not saying first name, middle name and 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.

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

Whereas in the second row, Is this first name available in the firsr 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

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

Results for this query will be

IDName
1Raj
2Peter
3Thomas

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

I hope this article is useful to you. Please leave your comment below in case of any queries or suggestion.

Leave a comment