SQL Server: basic functions (part 6) - Coalesce

Tweet

Have you ever thought "what is COALESCE afterall?". The first time I encountered it, I thought it was a terrible name for a function. But, considering that a normal dictionary will define "coalesce" as "Come together and form one mass or whole" or "Combine (elements) in a mass or whole", that might shed a little light and help us understand what's behind the function.
The function
As a start, let's see what MSDN has to say about it:

Returns the first nonnull expression among its arguments.

So, the function will return one value from a set of items. That's a good start! Moreover, COALESCE will return the first nonnull value. Fascinating - Spock would say.
If that is clear, we can dare to say that:

SELECT COALESCE(Null, Null, Null, 'Hello!') as H FROM your_table

will return a column (H) containing the word "Hello!" for each record in your_table. That is because the function will ignore the first 3 null values and it will use only the fourth value ("Hello!").
Something tells me that you are thinking about the IsNull function... hmmm... It is true that you can think about COALESCE as a function similar to the ISNULL function. But not completely: the result is infact different. We use ISNULL to evaluate null values and replace them with something different. COALESCE will consider null values and ignores them. That is a very basic explanation, however I will explain the ISNULL function in another post (and maybe I will explain the difference between the two functions better!).

Coalesce as the Case function

To explain Coalesce we can imagine that it is working like a set of Case statements.
If the Coalesce syntax is:

COALESCE(exp1, exp2)

then we can transform it in something equivalent:

CASE
    WHEN exp1 is not null THEN exp1
    WHEN exp2 is not null THEN exp2
END

That is like saying: "If exp1 is not null take it, if exp2 is not null take it". -- It really amazes me how I can translate code in words! ... ... ok, forget about it --

Standard use

Let's see a standard use of Coalesce. Let's say we have a table containing a list of employees. Some of them are paid a fixed amount of money, some with a variable amount of money. Remember, this is an example.

Name	Fixed	Variable
Denny Crane	1.000,00	Null
Alan Shore	Null	40%

We want to know the type of payment for each employee

SELECT Name, COALESCE(Fixed, Variable) as payment FROM your_table

will return

Name	Payment
Denny Crane	1.000,00
Alan Shore	40%

I think that the result is quite clear, isn't it?

Advance use

In one of my previous posts I showed how to create T-SQL queries with dynamic columns.
In that case, we used the Coalesce function to store the columns in a variable. I think you should look at the mentioned post to understand what I mean.
Anyway, that's a good and clever way to benefit from the Coalesce function in conjuction with pivoting.

Just the beginning

As you may understand, the Coalesce function is really powerful, it may solve multiple issues and it may meet your needs in many cases. Before starting to code a new query, I always think about Coalesce: will it be of any use? Many times, knowing its power, you will find it will.

Happy programming to all of you.