What is the COALESCE function and how can we use it? I’ll try to explain using examples that show some of the various ways you can use this function in your code.
First, a definition. Simply put, the COALESCE function is a “shortcut” for the CASE expression when you want to check for nulls. In fact, the COALESCE function is re-written by the query optimizer as a CASE statement like this:
CASE WHEN (expression1 IS NOT NULL) THEN expression1 WHEN (expression2 IS NOT NULL) THEN expression2 ... ELSE expression END
The COALESCE function picks the first non-null value in your list. Let’s explore a bit more by looking at several uses for this function.
Use #1 – find the first non-null value. Say you have a table that contains several columns for a phone number and you want to select the first one that is populated. Do it like this:
SELECT FullName, COALESCE(MobilePhoneNumber, WorkPhoneNumber, HomePhoneNumber) as Phone FROM Customer;
Use #2 – make a delimited list. There are several different methods for creating a comma delimited list, and COALESCE is just one of those methods (There are several posts on the internet that discuss the pros and cons of each method; that’s not my intent today.). Here is an example from AdventureWorks; note that it does a nifty job of keeping the last comma off the list:
DECLARE @MyList varchar(1000); SELECT @MyList = COALESCE(@MyList + ', ', '') + CAST(BusinessEntityID as varchar) FROM HumanResources.Employee WHERE OrganizationLevel = 3; SELECT @MyList;
Use #3 – execute multiple T-SQL Statements. This is really just a variation of Use #2 above. You can use T-SQL to generate multiple T-SQL statements and delimit each by a semi-colon. In this code the only real difference is that the last delimiter is included (i.e. we put a semi-colon at the end of the string of statements). Here is a simple example that generates multiple INSERT statements and then executes them (you can run this in any database):
-- create a table for this example CREATE TABLE #MyTempTable (Table_Name VARCHAR(100), Column_Name VARCHAR(100)) ; go DECLARE @SQL VARCHAR(MAX); -- use the COALESCE function to generate multiple INSERT statements SELECT @SQL=COALESCE(@SQL,'')+CAST('INSERT INTO #MyTempTable VALUES(''' + t.TABLE_NAME + ''',''' + c.COLUMN_NAME + ''');' AS VARCHAR(MAX)) FROM INFORMATION_SCHEMA.TABLES t Join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME ; -- print out the statements so that you can see them PRINT @SQL; -- execute the statements that were generated EXEC(@SQL); -- examine the results SELECT * FROM #MyTempTable; DROP TABLE #MyTempTable; go
Use #4 – examine optional parameters. I see many stored procedures that perform searches based on an optional list of parameters. Using COALESCE is one way to examine the parameters, as shown in this example (again, from AdventureWorks). Each of the following SELECT statements obtains the same result. Note that the query optimizer interprets these two SELECTS exactly the same, so there is no performance difference between them:
DECLARE @fname VARCHAR(50) = null, @lname VARCHAR(50) = 'Torres'; -- the "old fashioned" way SELECT * FROM Person.Person p WHERE ( @FName IS NULL OR @FName = p.FirstName) AND ( @LName IS NULL OR @LName = p.LastName) ; -- using COALESCE instead SELECT * FROM Person.Person p WHERE COALESCE(@FName, p.FirstName) = p.FirstName AND COALESCE(@LName, p.LastName) = p.LastName ;