Wednesday, 8 December 2010

SQL Server: basic functions (part 6) - Coalesce


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:
    WHEN exp1 is not null THEN exp1
    WHEN exp2 is not null THEN exp2
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.
Denny Crane1.000,00Null
Alan ShoreNull40%

We want to know the type of payment for each employee
SELECT Name, COALESCE(Fixed, Variable) as payment FROM your_table
will return
Denny Crane1.000,00
Alan Shore40%

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.

0 thoughts:

Post a Comment

Comments are moderated. I apologize if I don't publish comments immediately.

However, I do answer to all the comments.