In the last blog post, we discussed about different Element Operations in LINQ. You can read that article here. In this article, we will go over
COALESCE function in SQL Server.
According to MSDN books online, COALESCE function returns the first Non NULL value. What is meant by this? Let’s try to understand it through this article.
If we look at the
tblEmployee table which is shown above, we can see that
tblEmployee table has 4 columns –
LastName. If we look at these employees, we can see that some of them have just First Name, some of them have Middle and Last Names and some of them have just the Last Names.
Now we want to write a query which pulls out the
Name of the
Employee. The criteria for retrieving
Names are following:
- If the
Employee has got the First Name, we have to pull out that.
- If the
Employee doesn’t have a First Name and he has a Middle and a Last Name, then we want to pull out his Middle Name.
- If the
Employee doesn’t have First Name and Middle Name and he has only the Last Name, then we want to pull his Last Name.
- But if an Employee has both First Name and Middle Name, then we just want his First Name.
- Similarly, if an Employee has all of the Names – First, Middle and Last Names, still we want his First Name.
- So the priority should first go to First Name, then to Middle Name and finally to Last Name.
So our output should be like below:
In order to do that, we can use
SELECT Id, COALESCE(FirstName,MiddleName,LastName) AS Name
Here we are passing the column names to the
COALESCE function. So what is happening while executing the query? Let’s examine this row by row.
- In the first row,
COALESCE function will check whether First Name is available. Yes, the First Name is available and is not
null. So it will immediately return
- When it comes to the second row, it will check whether the First Name is available or not. No, it is
null. Then it will check the Middle Name. Is the Middle Name present? Yes, so the Middle Name,
Thomas is retrieved.
- In the third row,
COALESCE function will check whether the First Name is available or not. No, it is
null. Then it will check for the Middle Name. It is still
null. So it will go to the Last Name which is not
null and will return
- In the fourth row, First Name itself is present. So it does not even bother to check the Middle Name.
- In the fifth row as well, First Name itself is present. So the
COALESCE function will immediately return the First Name which is
Let’s quickly write the query in SQL Server Management Studio(SSMS) and see the results in action.
We exactly have the same table,
tblEmployee with same names.
Then we write the query to get
tblEmployee table using
COALESCE function. While executing the query, we will get the same output as expected.
Reference: Arun Ramachandran (http://BestTEchnologyBlog.Com)