Using Coalesce

The coalesce function is used to find the first non-null value. The function takes limitless number of parameters in order to evaluate the first non null. If all the parameters are null, then COALESCE will also return a NULL value.

[cc lang=”sql”]
— hard coded example
SELECT MyValue = COALESCE(NULL, NULL, ‘abc’, 123)
[/cc]

The example above returns back ‘abc’ as it is the first non null value.

When would you use COALESCE you ask? Well, the most common scenario I use it in is when I am joining two or more tables together and the tables all contain an acceptable value for, say, firstname. However if firstname is null, in the first table, we will want to use it from the second table, and so forth.

[cc lang=”sql”]
SELECT FirstName = COALESCE (a.FirstName, b.First_Name, c.Fname)
FROM HRUsers a
LEFT JOIN MarketingUsers b
ON b.EmployeeID = a.EmployeeID
LEFT JOIN SalesUsers c
ON c.EmployeeID = a.EmployeeID
[/cc]

This example basically says, if FirstName is populated in the HRUsers table, we want to use that one first, otherwise, use the First_Name field in MarketingUsers, or if that is null, take the FName from SalesUsers.

It should be noted that if only comparing two values, the ISNULL function has been proven to be quicker.