The Differences Between COALESCE and ISNULL

Two muskie lures that are both effective and quite similar are the Musky Innovations Bulldawg and Chaos Tackle Medussa. These lures are both hard rubber lures without any natural action; the angler is responsible for making the lure move in a way that will get the fish to bite. They can be swam, jerked, bounced, ripped, or any other action you can think of to try to get fish to bite. Although they look quite similar, the action has subtle differences, and knowing these differences tells you which lure to use, depending on your situation. The bulldawg will cut through the water a bit better, and rolls over on its side as you let it fall. The medussa will almost hovers when you let it fall, slowly sinking with a back-and-forth wobble. Though similar, knowing these subtle differences can mean the difference between catching or not catching a fish.
Two SQL Server functions that also have rather subtle but important differences are the ISNULL() and COALESCE() functions. Both functions are used to return another value if the first evaluates to NULL. This can be useful for making sure your query is always returning a result and not grabbing those meaningless NULL values.
Let’s take a quick look at when this might be useful. Let’s say we’re running a bait shop, Super SQL Fishing Baits! To keep track of the baits we sell and what price and quantity they come in, we’ll create a table called dbo.LiveBait, and insert some data:

CREATE TABLE dbo.LiveBait
    (
      ID INT IDENTITY(1, 1) ,
      BaitName VARCHAR(255) NOT NULL ,
      Quantity INT ,
      Price MONEY NOT NULL ,
      SalePrice MONEY NULL
    )
INSERT  INTO dbo.LiveBait
        ( BaitName, Quantity, Price, SalePrice )
VALUES  ( 'Nightcrawlers', 24, 3.99, NULL ),
        ( 'Redworms', 48, 2.50, NULL ),
        ( 'Crappie Minnows', 12, 3.50, 2.49 ),
        ( 'Redtail Chubs', 12, 4.00, NULL ),
        ( 'Musky Suckers', 1, 8.00, 5.99 )

Now our table has a Price and a SalesPrice field. When we want to put a product on sale, we put in a sales price, otherwise we leave it NULL and charge the regular price. To get what the price will be, we can use ISNULL or COALESCE. Both of these queries return the same results:

SELECT ID ,
       BaitName ,
       Quantity ,
       ISNULL(SalePrice,Price) AS CurrentPrice
FROM dbo.LiveBait

SELECT ID ,
       BaitName ,
       Quantity ,
       COALESCE(SalePrice,Price) AS CurrentPrice
FROM dbo.LiveBait

20150105 Coalesce Isnull Same Results

Now let’s discuss some of the subtle differences between ISNULL and COALESCE. First, ISNULL can only accept two arguments. If the first argument is NULL, the other is returned, even if it too is NULL. COALESCE can accept any number of arguments, and will return NULL only if every one of those arguments are NULL.

Secondly, the data type of the result will be different depending on which is used. ISNULL uses the data type of the first parameter, whereas COALESCE returns the data type of value with the highest precedence. Let’s look at an example from our previously created table. If we write:

SELECT  ISNULL(SalePrice, 'Muskie')
FROM    dbo.LiveBait

We’re going to get an error. The first argument’s data type is money, so ISNULL tries to change the string ‘Muskie’ into a money, and when it can’t, it spits out an error:

20150105 Coalesce Isnull Isnull data type

Coalesce will find the data type with the highest precedence. Here is a list of the data type precedence from Microsoft. Money is higher than char, so we’d still get an error.

The last main difference is that COALESCE is actually rewritten by the query optimizer as a CASE statement. This means that our statement earlier

SELECT ID ,
       BaitName ,
       Quantity ,
       COALESCE(SalePrice,Price) AS CurrentPrice
FROM dbo.LiveBait

Is actually rewritten by the optimizer as:

SELECT  ID ,
        BaitName ,
        Quantity ,
        CASE WHEN SalePrice IS NOT NULL THEN Salesprice
             ELSE Price
        END AS CurrentPrice
FROM    dbo.LiveBait

This may seem like a rather small detail, but does cause a few side affects. The first is that using a subquery in the case statement will cause the subquery to be evaluated multiple times. Consider this statement:

SELECT  COALESCE(( SELECT TOP 1
                            SalePrice
                   FROM     dbo.LiveBait
                   ORDER BY Price ASC
                 ), Price) AS field
FROM    dbo.LiveBait

If we look at the execution code, we will see that the subquery, shown by a LiveBait tables scan with a sort, has been performed twice; once to see if it returns NULL and once to return the value.

20150105 Coalesce Isnull Coalesce Case Subquery

If we add the same subquery again we will see the execution plan now shows it being executed four times:

20150105 Coalesce Isnull Coalesce Case Subquery 2

The next affect involves what can possibly happen, based on the database’s transaction isolation level, if someone is making changes to the table involved in the subquery during the execution of the COALESCE statement. Consider that COALESCE executes the subquery and finds a non-NULL value. It now needs to execute it again to return that value as the result. However, another statment has come in and updated the table so now the subquery does return a NULL value. You can get around this by changing the isolation level to at least SNAPSHOT ISOLATION, or rewriting the subquery as a subselect like this:

SELECT  CASE WHEN X IS NOT NULL THEN X
             ELSE Price
        END AS CurrentPrice
FROM    ( SELECT    ( SELECT TOP 1
                                SalePrice
                      FROM      dbo.LiveBait
                      ORDER BY  Price ASC
                    ) AS X ,
                    Price
          FROM      dbo.LiveBait
        ) AS T

The last difference is the NULLability of the resulting expression is different for ISNULL and COALESCE. The ISNULL return value is always assumed to be NOT NULL, whereas the COALESCE return value is NULL. This makes a difference in the NULLABILITY of computed columns and key constraints. As an example, let’s add a computed column to our table to hold our current price:

ALTER TABLE dbo.LiveBait ADD CurrentPrice AS (COALESCE(SalePrice,Price))

This new computed column is considered NULLable, so when we try to make it our primary key:

20150105 Coalesce Isnull Coalesce Results NULLABLE

However, let’s drop that column and re-add it using ISNULL instead of COALESCE, and we can see we are successful in creating the primary key constraint:

20150105 Coalesce Isnull ISNULL Results NONNULLABLE

And here is the view of the table in SQL Server Management Studio:

20150105 Coalesce Isnull ISNULL Results NONNULLABLE table view

So there are the main differences between ISNULL and COALESCE. I hope this post helped you understand which to use and when.

Advertisements