SQL: COALESCE() Function

Introduction

When developing queries in SQL Server, dealing with nulls can often be challenging. The COALESCE function was included in SQL 2005 to help deal with these issues. First let’s take a look at what COALESCE can offer. Generally, COALESCE function receives a sequence of values and a default value to used when all of items in the sequence of values are null. From here the function returns the first not-null in the sequence of values list.

We chose Server Intellect for its dedicated servers, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.

Scenario

There is a requirement of showing a user’s full name and how much money they get paid per week. This scenario will be divided up into two segments, displaying the user’s full name and then computing and showing their weekly earnings. Please feel free to use the attached database to follow along.

Implementation

In this first example, suppose there is a table of users that have the columns FirstName, MiddleName and LastName. The table holds the following values:

In many applications, the requirement of welcoming the user is often needed. So, to put the user’s full name on the screen, a stored procedure using the COALESC function properly format all three fields into one field. So the query using COALESCE looks like this:

We migrated our web sites to Server Intellect over one weekend and the setup was so smooth that we were up and running right away. They assisted us with everything we needed to do for all of our applications. With Server Intellect's help, we were able to avoid any headaches!

The results will look like this:

For the next example, let’s extend the previous example by adding some information on how much the users get paid per week. Some users get paid by the hour while others get paid on a salary with a commission, so the table reflects this information.

For this example the table looks like this:

We moved our web sites to Server Intellect and have found them to be incredibly professional. Their setup is very easy and we were up and running in no time.

So now there needs to be a way to list the amount a user is paid per week for whether they are paid hourly or by salary. The query from the previous example will be modified so that the person’s name and their weekly pay can be displayed together. The final query looks like this:

When the query is run, the results will come out like this:

So, with these to short examples, the COALESCE function is an easy way to filtering out null values. Also, when using the COALESCE function, it will most often be used in a very specific instance, like in a view or stored procedure.

Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!

What have we learned?

• How to use the COALESCE function to filter out null values using string values.
• How to use the COALESCE function in conjunction using math operators on money values.

Attachments