Top Answers to 5 Burning Questions on COALESCE Function in SQL Server

How cool is COALESCE function in SQL?

It’s cool enough to be so important to me. And I’ll be more than happy to hire a new guy who doesn’t have a bad habit of ignoring the goal of COALESCE. That includes other expressions and functions for handling similar situations.

Today, you will find the answers to the five most-asked questions about SQL COALESCE expression. One of these is being debated over and over again.

Shall we begin?

What is the Use of COALESCE Function in SQL?

It can be answered in 2 words: handling nulls.

Null is a void of values. In other words, unknown. It is different from an empty string or a zero number. Handling nulls requires the usage of expressions and functions. One of them is COALESCE.

To understand what I mean, see the statements below:

DECLARE @number INT

SELECT @number + 33587

Will it run fine? It will.

Is there a problem? None, at the moment.

But the statements will result in NULL because adding null to a number equals NULL.

If all your queries only fall to this level, you can stop reading. But of course, they aren’t. We’re getting paid for more than to produce this kind of code.

Now, let’s add a little bit of ‘disaster’:

DECLARE @number INT

SET @number = @number + 33587

UPDATE myTable
set col1 = @number   -- Surprise! col1 is NOT NULLABLE
where ID = 1

PRINT 'Success!'

The execution of the code above will be at the dead-end when it reaches the UPDATE statement. It won’t print ‘Success!’ because you cannot put a null on a non-nullable column. Does this statement speak clear why we need to handle nulls?

Let’s change the code to add a safety net:

DECLARE @number INT

SET @number = COALESCE(@number,0) + 33587     -- our safety net. Thanks to COALESCE.

UPDATE myTable
set col1 = @number               -- Disaster averted!
where ID = 1

PRINT 'Success!'

COALESCE will change the null value to zero, and a sum won’t be null.

The lesson is, COALESCE is one of the safety nets against nulls. Even better, handling nulls properly in your SQL code reduces your headaches and lets you go home early. That’s for sure.

Now you understand why I want in my team someone diligent in handling nulls.

More Practical Examples in the Use of SQL COALESCE

Let’s refer to more practical examples.

Suppose you live in a region where some people have middle names, but others don’t. How will you form the full name out of the first name, middle name, and last name without falling into the null trap?

Here’s one possible solution using COALESCE:

USE AdventureWorks
GO

SELECT
p.LastName + ', ' + p.FirstName + ' ' + COALESCE(p.MiddleName + ' ','') AS FullName
FROM Person.Person p

Another example: suppose you’re an employee in a company where gross pay is computed differently for each employee. For some of them, there are hourly rates. Others get paid at weekly or monthly rates.

Here’s a table sample together with a query solution using COALESCE:

-- STEP 1: Create the table
CREATE TABLE EmployeeWages (
    employee_id INT PRIMARY KEY,
    hourly_rate SMALLMONEY,
    weekly_rate SMALLMONEY,
    monthly_rate MONEY,
    CHECK(
        hourly_rate IS NOT NULL OR
        weekly_rate IS NOT NULL OR
        monthly_rate IS NOT NULL)
);

-- STEP 2: Insert data
INSERT INTO
    EmployeeWages(
        employee_id,
        hourly_rate,
        weekly_rate,
        monthly_rate
    )
VALUES
    (1,60, NULL,NULL),
    (2,40, NULL,NULL),
    (3,NULL, 1000,NULL),
    (4,NULL, NULL,7000),
    (5,NULL, NULL,5000);

-- STEP 3: Query the monthly salary.
SELECT
    employee_id,
    COALESCE(
        hourly_rate*22.00*8.00,
        weekly_rate*4.00,
        monthly_rate
    ) AS monthly_salary
FROM
    EmployeeWages;

The table contains different pay modes per employee ID. The query requires you to output a monthly salary for all.

This is where COALESCE will shine: it accepts a list of values, and there can be any number of items. COALESCE will pick the first one that is not null:

Neat, isn’t it?

How Does COALESCE Work in SQL?

The definition of COALESCE is an expression that returns the first non-null value from a list of values. The COALESCE syntax is:

COALESCE ( expression [ ,…n ] ) 

Our previous example with different payment modes for wages illustrates this.

What’s Under the Hood

Under the hood, COALESCE function in SQL is a sugar-coated expression for a much longer CASE expression. It eliminates the need of typing an equivalent CASE, which is longer (and tiring, for lazy typists like me). The result will be the same.

Can we prove it? Yes! For one, Microsoft admits it.

But good for us, Microsoft included it in the Execution Plan. Thus, we know what is going on.

Let’s try it using a previous example with wages. But before you re-run the query below, turn on Include Actual Execution Plan or just press CTRL-M.

SELECT
    employee_id,
    COALESCE(
        hourly_rate * 22.00 * 8.00,
        weekly_rate * 4.00,
        monthly_rate
    ) AS monthly_salary
FROM
    EmployeeWages;

Click the Execution Plan tab in the results. It looks simple, but our hidden gem lies in the Compute Scalar node. When you hover the mouse over it, you see an expression named Expr1002 (Figure 2). What could it be?

Let’s dig deeper. Right-click it and select Show Execution Plan XML. A new window will appear. Have a look at Figure 3 below:

There’s your CASE statement. Below is the whole thing formatted and indented for readability:

CASE WHEN CONVERT_IMPLICIT(numeric(10,4),[TestDatabase].[dbo].[EmployeeWages].[hourly_rate],0)
                                            *(22.00)*(8.00) IS NOT NULL
     THEN CONVERT_IMPLICIT(numeric(23,8),CONVERT_IMPLICIT(numeric(10,4),
                       [TestDatabase].[dbo].[EmployeeWages].[hourly_rate],0)*(22.00)*(8.00),0)
     ELSE CASE WHEN    
          CONVERT_IMPLICIT(numeric(10,4),[TestDatabase].[dbo].[EmployeeWages].[weekly_rate],0)
                                            *(4.00) IS NOT NULL
          THEN CONVERT_IMPLICIT(numeric(23,8),CONVERT_IMPLICIT(numeric(10,4),                                                         
                       [TestDatabase].[dbo].[EmployeeWages].[weekly_rate],0)*(4.00),0)
          ELSE CONVERT_IMPLICIT(numeric(23,8),[TestDatabase].[dbo].[EmployeeWages].[monthly_rate],0)
          END
END

That’s quite long compared to

COALESCE(
        hourly_rate * 22.00 * 8.00,
        weekly_rate * 4.00,
        monthly_rate
        )

That’s what SQL Server did with our query with COALESCE. All is for getting the first value that is not null in a list of values.

Can It Be Shorter?

I know what you’re thinking. If SQL Server does that during query processing, COALESCE must be slow. Not to mention the multiple appearances of CONVERT_IMPLICIT. Will you rather use alternatives?

For one, you can type a shorter CASE statement yourself. Or, you can use ISNULL. More on that later. Speaking of being slow, I had that covered before this post ends.

What are the Differences Between COALESCE and ISNULL in SQL?

One of the alternatives to COALESCE is ISNULL. Using COALESCE with 2 values makes it similar to ISNULL. At least, the results look similar. Still, there are notable differences. You can use it as a guide to decide if you will use COALESCE or ISNULL.

(1) ISNULL Accepts 2 Arguments. COALESCE Accepts a List of Arguments

It is the most obvious difference. From the syntax, it’s sure is different.

ISNULL ( check_expression , replacement_value )
COALESCE ( expression [ ,…n ] )

When you use both with 2 arguments, the results are the same. The 2 statements below will result in 1:

SELECT ISNULL(NULL, 1)
SELECT COALESCE(NULL, 1)

Although the results are the same, they are meant differently:

• ISNULL(NULL, 1) returned 1 because the first argument is NULL.
• COALESCE(NULL, 1) returned 1 because 1 is the first non-null value in the list.

(2) COALESCE is SQL-92 Standard

That’s right. You can check it. For instance, if you want to port your SQL code to MySQL from SQL Server, COALESCE will function the same. See Figure 4 and compare the result from Figure 1:

Using ISNULL in MySQL, however, will trigger an error if you use SQL Server’s syntax.

e.g., Run the following in SQL Server Management Studio and MySQL Workbench:

SELECT ISNULL(null,1)

What happened? In SQL Server, the output is 1. But in MySQL, the output is an error:

06:36:52 SELECT ISNULL(null,1) Error Code: 1582. Incorrect parameter count in the call to native function ‘ISNULL’

The thing is, ISNULL in MySQL accepts 1 argument and returns 1 if the argument is null. Otherwise, it returns 0.

(3) Passing 2 Nulls in COALESCE Triggers an Error. It’s Fine with ISNULL

This will trigger an error:

SELECT COALESCE(NULL, NULL)

The error is: ‘At least one of the arguments to COALESCE must be an expression that is not the NULL constant.’

This will be just fine:

SELECT ISNULL(NULL, NULL)

Changing the COALESCE code to something similar below will not trigger an error:

DECLARE @value INT = NULL
SELECT COALESCE(@value,null)

That happened because @value is not a null constant.

(4) SQL COALESCE is Converted to CASE. ISNULL Stays ISNULL

We’ve seen this in the ‘How Does COALESCE Work in SQL?’ section. Here, let’s examine another example:

SELECT
P.LastName + ', ' + P.FirstName + ' ' + COALESCE(P.MiddleName + ' ','') AS FullName
FROM Person.Person p

Inspection of the Execution Plan XML for the scalar operator reveals the conversion to CASE:

 [AdventureWorks].[Person].[Person].[LastName] as [p].[LastName]+N', '
+[AdventureWorks].[Person].[Person].[FirstName] as [p].[FirstName]+N' '
+CASE WHEN ([AdventureWorks].[Person].[Person].[MiddleName] as [p].[MiddleName]+N' ') IS NOT NULL
      THEN [AdventureWorks].[Person].[Person].[MiddleName] as [p].[MiddleName]+N' '
      ELSE N''
 END

Now, run an equivalent query using ISNULL:

SELECT
P.LastName + ', ' + P.FirstName + ' ' + ISNULL(P.MiddleName + ' ','') AS FullName
FROM Person.Person p

Then, inspect the Execution Plan XML for the scalar operator:

 [AdventureWorks].[Person].[Person].[LastName] as [p].[LastName]+N', '
+[AdventureWorks].[Person].[Person].[FirstName] as [p].[FirstName]+N' '
+isnull([AdventureWorks].[Person].[Person].[MiddleName] as [p].[MiddleName]+N' ',N'')

ISNULL is still ISNULL.

(5) The Data Type of Resulting Expression is Different

Data type determination of the resulting expression is also different between COALESCE and ISNULL:

• ISNULL uses the data type of the first parameter.
• COALESCE returns the data type of the value with the highest precedence.

For a list of data type precedence, check out this link.

Let’s have an example:

SELECT
 employee_id
,COALESCE(CAST(weekly_rate * 4 AS MONEY),0.0000) AS monthly_rate
FROM EmployeeWages

Then, inspect the conversion to CASE in the Execution Plan XML:

CASE WHEN CONVERT(money,[TestDatabase].[dbo].[EmployeeWages].[weekly_rate]
                                           *CONVERT_IMPLICIT(smallmoney,[@1],0),0) IS NOT NULL
     THEN CONVERT_IMPLICIT(numeric(19,4), CONVERT(money,[TestDatabase].[dbo].[EmployeeWages].[weekly_rate]
                                           *CONVERT_IMPLICIT(smallmoney,[@1],0),0),0)
     ELSE (0.0000)
END

In the CASE expression above, the data type of the result will be numeric(19,4). 

Why? It has higher precedence than money and smallmoney even if you CAST it to money. Why numeric and not money? Because of the constant 0.0000.

In case you’re wondering what @1 is, the Execution Plan XML has the answer. It is the constant number 4.

<ParameterList>
 <ColumnReference Column="@1" ParameterDataType="int" ParameterCompiledValue="(4)"  
       ParameterRuntimeValue="(4)" />
</ParameterList>

Let’s try it with ISNULL:

SELECT
 employee_id
,ISNULL(CAST(weekly_rate * 4 AS MONEY),0.0000) AS monthly_rate
FROM EmployeeWages

Again, look for the Scalar Operator‘s ScalarString:

ISNULL(CONVERT(MONEY,[TestDatabase].[dbo].[EmployeeWages].[weekly_rate]*($4.0000),0),($0.0000))

Finally, the data type of the resulting expression will be money. It’s the data type of the first argument.

How to Outsmart Data Precedence

You can ‘outsmart’ data precedence by adding a few changes to your code. Earlier, the result had a numeric data type. If you want the result to be a money data type and get rid of the CONVERT_IMPLICIT, do the following:

SELECT
 employee_id
,COALESCE(CAST(weekly_rate AS MONEY) * ($4.0000),($0.0000)) AS monthly_rate
FROM EmployeeWages

Did you notice the ($4.000) and ($0.0000) constants? Those are money constants. What happens next appears in the Execution Plan XML‘s ScalarString:

CASE WHEN CONVERT(money,[TestDatabase].[dbo].[EmployeeWages].[weekly_rate],0)*($4.0000) IS NOT NULL 
     THEN CONVERT(money,[TestDatabase].[dbo].[EmployeeWages].[weekly_rate],0)*($4.0000) 
     ELSE ($0.0000) 
END

That’s much better. It’s shorter, and the CONVERT_IMPLICIT is gone. And the resulting data type is money.

(6) The NULLability of the Resulting Expression is Different

ISNULL(NULL, 1) and COALESCE(NULL, 1) have similar results, but their nullability values are different. COALESCE is nullable. ISNULL is not. You can see this when using it on computed columns.

Let’s refer to an example. The statement below will trigger an error because the PRIMARY KEY cannot accept NULL values. At the same time, the nullability of the COALESCE expression for column2 evaluates to NULL. 

CREATE TABLE NullabilityDemo  
(  
  column1 INTEGER NULL,  
  column2 AS COALESCE(column1, 0) PRIMARY KEY,  
  column3 AS ISNULL(column1, 0)  
);

This statement succeeds because the nullability of the ISNULL function evaluates AS NOT NULL. 

CREATE TABLE NullabilityDemo  
(  
  column1 INTEGER NULL,  
  column2 AS COALESCE(column1, 0),  
  column3 AS ISNULL(column1, 0) PRIMARY KEY  
);

(7) ISNULL’s Left Argument is Evaluated Once. It’s the Opposite with COALESCE

Consider the previous example again:

SELECT
    employee_id,
    COALESCE(
        hourly_rate*22.00*8.00,
        weekly_rate*4.00,
        monthly_rate
    ) AS monthly_salary
FROM
    EmployeeWages;

Then, inspect the ScalarString for this:

CASE WHEN CONVERT_IMPLICIT(numeric(10,4),[TestDatabase].[dbo].[EmployeeWages].[hourly_rate],0)
                                              *(22.00)*(8.00) IS NOT NULL 
     THEN CONVERT_IMPLICIT(numeric(23,8),CONVERT_IMPLICIT(numeric(10,4),
                                              [TestDatabase].[dbo].[EmployeeWages].[hourly_rate],0)
                                              *(22.00)*(8.00),0) 
     ELSE CASE WHEN CONVERT_IMPLICIT(numeric(10,4),[TestDatabase].[dbo].[EmployeeWages].[weekly_rate],0)
                                              *(4.00) IS NOT NULL 
               THEN CONVERT_IMPLICIT(numeric(23,8),CONVERT_IMPLICIT(numeric(10,4),
                                        [TestDatabase].[dbo].[EmployeeWages].[weekly_rate],0)*(4.00),0) 
               ELSE CONVERT_IMPLICIT(numeric(23,8),[TestDatabase].[dbo].[EmployeeWages].[monthly_rate],0) 
          END 
END

When COALESCE function in SQL is converted to a CASE, each expression is evaluated twice (except for the last one). As you can see above, hourly_rate*22.00*8.00 appeared twice. Same thing with weekly_rate*4.00. The last expression, monthly_rate, appeared once.

Since COALESCE will evaluate expressions twice, there can be a performance penalty. More on this later.

However, check out the ISNULL equivalent:

SELECT
 employee_id,
 ISNULL(hourly_rate * 22.00 * 8.00,ISNULL(weekly_rate * 4.00,monthly_rate)) AS  
                                                       monthly_salary
FROM EmployeeWages

Then, we inspect the ScalarString in the Execution Plan XML:

isnull(CONVERT_IMPLICIT(numeric(10,4),[TestDatabase].[dbo].[EmployeeWages].[hourly_rate],0)*(22.00)*(8.00),
       CONVERT_IMPLICIT(numeric(19,8),
isnull(CONVERT_IMPLICIT(numeric(10,4),[TestDatabase].[dbo].[EmployeeWages].[weekly_rate],0)*(4.00),
CONVERT_IMPLICIT(numeric(14,6),[TestDatabase].[dbo].[EmployeeWages].[monthly_rate],0)),0))

As you can see above, hourly_rate, weekly_rate, and monthly_rate appeared only once. So, you don’t need to worry about expressions being evaluated twice with ISNULL.

Can We Use COALESCE in a WHERE Clause?

Sure thing. There’s no better way than to show an example to prove this.

-- Query all the names in Person table with no middle name

USE AdventureWorks
GO

SELECT
 p.LastName
,p.FirstName
FROM person.Person p
WHERE COALESCE(p.MiddleName,'') = ''

COALESCE will return a blank string if MiddleName is NULL. Of course, there’s a shorter way to produce the result. But this shows COALESCE works in a WHERE clause.

Which is Faster: COALESCE or ISNULL?

Finally, we have come to a hot topic: Performance!

You’ll get many pages with tests and comparisons, but there will be a battle of COALESCE and ISNULL proponents in comments. We’ll clear out the dust and smokes of those wars.

Which is faster: COALESCE or ISNULL? Let me begin by saying that the answer is:

(drums rolling)

IT DEPENDS!

(jaw dropped)

Disappointed? I’ll explain it in a moment.

First, I agree that both seem to have performance differences if you use elapsed time as your metric. Some people supported this fact when SQL Server translates COALESCE to CASE statements. Meanwhile, ISNULL stays as is.

Others may reason differently because of the varying results. Also, for them, converting COALESCE to CASE is faster than we blink our eyes. Just like what’s pointed out in this thread, the performance difference ‘is miniscule.’ I agree. Here’s another article that said that the difference ‘is minor.’  

However, here’s a big deal: can we trust a miniscule elapsed time as a metric? What really matters is how much logical reads the query has. It’s what we are going to point out in our next examples.

(Check out my other article about logical reads and why this factor that lags your queries.)

Example 1

Let’s examine the same example and compare their logical reads and execution plans. Before running this, make sure STATISTICS IO is ON and Include Actual Execution Plan is enabled.

SET STATISTICS IO ON

SELECT
P.LastName + ', ' + P.FirstName + ' ' + COALESCE(P.MiddleName + ' ','') AS FullName
FROM Person.Person p

SELECT
P.LastName + ', ' + P.FirstName + ' ' + ISNULL(P.MiddleName + ' ','') AS FullName
FROM Person.Person p

Here are the facts:

Logical reads for both queries are the same. Both are 107 * 8KB of pages. If we have a million records, logical reads will increase, of course. But the logical reads for both queries will be equal. That’s the case even if COALESCE gets converted to CASE:

Let’s inspect the execution plan. Here’s how we are going to do it:

1. Execute the 1st SELECT statement with the COALESCE expression.
2. Click the Execution Plan tab in results. Right-click it and select Save Execution Plan As. And name the file plan1.sqlplan.
3. Execute the 2nd SELECT statement with the ISNULL function.
4. Click the Execution Plan tab in results.
5. Right-click it and select Compare Showplan.
6. Select the file plan1.sqlplan. A new window will appear.

That’s how we are going to inspect the execution plan for all examples.

Getting back to our first example, see Figure 6 to see the execution plan comparison:

Did you notice these important points in Figure 6?

• The shaded portion of the 2 plans (the Index Scans) means SQL Server used the same operations for the 2 queries.
• The QueryPlanHash for the 2 plans is 0x27CEB4CCE12DA5E7, meaning the plan is the same for both.
• The few millisecond differences for elapsed time is negligible.

Takeaways

It’s like comparing apples to apples, but of different types. One is a Fuji apple from Japan, another is a red apple from New York. Still, they are both apples.

Similarly, SQL Server requires the same resources and the chosen execution plan for both queries. The only difference is the use of COALESCE or ISNULL. Minor differences, as the final result is the same.

Example 2

The big difference appears when you use a subquery as an argument to both COALESCE and ISNULL:

USE AdventureWorks
GO

SELECT COALESCE(
       (SELECT
        SUM(th.ActualCost)
        FROM Production.TransactionHistory th
        WHERE th.ProductID = 967)
       ,0) 

SELECT ISNULL(
       (SELECT
        SUM(th.ActualCost)
        FROM Production.TransactionHistory th
        WHERE th.ProductID = 967)
       ,0)

The above code will have the same result, but the inside is very different.

Let’s start with the logical reads:

The SELECT statement with the COALESCE expression has double the logical reads of the one that used ISNULL.

But why double the logical reads? The execution plan comparison will reveal even more:

Figure 8 explains why the logical reads are double using COALESCE. See the 2 Stream Aggregate nodes in the bottom plan: they are duplicates. The next question is, why was it duplicated?

Let’s recall the point relates to when COALESCE is converted to CASE. How many times are the expressions evaluated in the arguments? TWICE!

So, the subquery is evaluated twice. It shows in the execution plan with duplicate nodes.

This also explains double logical reads using COALESCE compared to ISNULL. If you plan to look at the execution plan XML of the query with COALESCE, it’s rather long. But it reveals that the subquery will be executed twice.

Now what? Can we outsmart this? Of course! If you ever faced anything like this, which I believe is rare, the possible fix is to divide and conquer. Or, use ISNULL if it’s only one subquery.

How to Avoid Evaluating the Subquery Expression Twice

Here’s how to avoid evaluating the subquery twice:

• Declare a variable and assign the result of the subquery to it.
• Then, pass the variable as an argument to COALESCE.
• Repeat the same steps depending on the number of subqueries.

As I said, it should be rare, but if it happens, you know what to do now.

A Few Words on Isolation Level

Evaluating the subquery twice may cause another problem. Depending on your query’s isolation level, the result of the first evaluation may be different from the second in a multi-user environment. It’s crazy.

To ensure the stable results returning, you may try using a SNAPSHOT ISOLATION. Also, you can use ISNULL. Or, it can be a divide-and-conquer approach, as pointed above.

Takeaways

So, what’s the essence?

• Always check the logical reads. It matters more than elapsed time. Use elapsed time and take away your sanity. Your machine and the production server will always have varying results. Give yourself a break.
• Always check the execution plan, so you know what’s happening under the hood.
• Be aware that when COALESCE is translated to CASE, the expressions are evaluated twice. Then, a subquery or something similar can be a problem. Assigning the subquery result to a variable before using it in COALESCE can be a solution.
• What’s faster depends on the logical reads’ results and execution plans. There’s no general rule to determine if COALESCE or ISNULL is faster. Otherwise, Microsoft might inform about that, as they did in HierarchyID and SQL Graph.

Eventually, it really depends.

Conclusion

I hope you had a worthwhile reading this article. Here are the points we discussed:

• COALESCE is one of the ways to handle nulls. It’s a safety net to avoid errors in code.
• It accepts the arguments list and returns the first non-null value.
• It gets converted to a CASE expression during query processing, but it does not slow the query.
• While there are a few similarities to ISNULL, there are 7 notable differences to it.
• It can be used with the WHERE clause.
• Finally, it’s not faster or slower than ISNULL.

If you like this post, the social media buttons are waiting for your click. Sharing is caring.

Thank you.

Read also

Handling the NULL Values Effectively with the SQL COALESCE Function for Beginners

A Practical Use of the SQL COALESCE Function

Tags: sql coalesce, sql functions, sql server Last modified: September 16, 2022