T-SQL coding

Various Uses for COALESCE

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 ;
Advertisements

2 thoughts on “Various Uses for COALESCE

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s