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.
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:
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!").
SELECT COALESCE(Null, Null, Null, 'Hello!') as H FROM your_table
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 functionTo explain Coalesce we can imagine that it is working like a set of Case statements.
If the Coalesce syntax is:
then we can transform it in something equivalent:
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 --
WHEN exp1 is not null THEN exp1
WHEN exp2 is not null THEN exp2
Standard useLet'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.
We want to know the type of payment for each employee
SELECT Name, COALESCE(Fixed, Variable) as payment FROM your_table
I think that the result is quite clear, isn't it?
Advance useIn 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 beginningAs 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.