A Subtle Difference Between COALESCE and ISNULL

When the answer isn’t just SARGabilty…

Words: 717
Time to read: ~3.5 minutes

Input:

I’m just back from holidays for the last week and a bit, which was helpful as September was a really busy month for me (partly because of this) so I’ve fallen behind on blog posts. I know, I know, I should have had a few ready to fire in the chamber and scheduled but I didn’t.

In fact, I wasn’t even going to post something this week, and was just going to spend the time catching up on reading blogs, catching up on study, and responding to emails (oh god, the amount of emails!), but then my Senior asked me a question about a query and surprised me with the answer. With material like that, how could I not document it!

Process:

Let’s work on a MCVE for everyone.

We have a table called dbo.RunningTotals with two columns, a date column called SpentDate and a smallmoney column called SpentAmount.


CREATE TABLE dbo.RunningTotals (
SpentDate date NOT NULL,
SpentAmount smallmoney NOT NULL
);
GO

view raw

CreateTable_RunningTotals.sql

hosted with ❤ by GitHub

Now let’s throw some sample data in there…


INSERT INTO dbo.RunningTotals (SpentDate, SpentAmount) VALUES (DATEADD(DAY, –1, GETDATE()), 50.00);
INSERT INTO dbo.RunningTotals (SpentDate, SpentAmount) VALUES (DATEADD(DAY, –1, GETDATE()), 50.00);
INSERT INTO dbo.RunningTotals (SpentDate, SpentAmount) VALUES (DATEADD(DAY, –1, GETDATE()), –75.00);
INSERT INTO dbo.RunningTotals (SpentDate, SpentAmount) VALUES (DATEADD(DAY, –1, GETDATE()), 50.00);
INSERT INTO dbo.RunningTotals (SpentDate, SpentAmount) VALUES (GETDATE(), 100.00);
INSERT INTO dbo.RunningTotals (SpentDate, SpentAmount) VALUES (GETDATE(), 100.00);
INSERT INTO dbo.RunningTotals (SpentDate, SpentAmount) VALUES (GETDATE(), –150.00);
INSERT INTO dbo.RunningTotals (SpentDate, SpentAmount) VALUES (GETDATE(), 100.00);
SELECT * FROM dbo.RunningTotals;

view raw

InsertRunningTotals.sql

hosted with ❤ by GitHub

Now, don’t be fooled…

…I know that the main difference that people think of when comparing ISNULL and COALESCE is that the former only allows two values while the later allows multiple i.e. ISNULL(x1, x2) vs COALESCE(x1, x2, ..., xn)

That’s not what I’m trying to point out today. Let’s give you the sample code first.


DECLARE @Date date = DATEADD(DAY, –1, GETDATE());
SELECT SpentDate,
SUM(SpentAmount) AS Total
FROM dbo.RunningTotals
WHERE SpentDate >= ISNULL(@Date, GETDATE())
GROUP BY
GROUPING SETS(
SpentDate, — Per date,
() — To include the overall total as well.
);
GO
DECLARE @Date date = NULL;
SELECT SpentDate,
SUM(SpentAmount) AS Total
FROM dbo.RunningTotals
WHERE SpentDate >= ISNULL(@Date, GETDATE())
GROUP BY
GROUPING SETS(
SpentDate,
()
);
GO
DECLARE @Date date = DATEADD(DAY, –1, GETDATE());
SELECT SpentDate,
SUM(SpentAmount) AS Total
FROM dbo.RunningTotals
WHERE SpentDate >= COALESCE(@Date, GETDATE())
GROUP BY
GROUPING SETS(
SpentDate,
()
);
GO
DECLARE @Date date = NULL;
SELECT SpentDate,
SUM(SpentAmount) AS Total
FROM dbo.RunningTotals
WHERE SpentDate >= COALESCE(@Date, GETDATE())
GROUP BY
GROUPING SETS(
SpentDate,
()
);
GO

view raw

IsnullCoalesceDiff.sql

hosted with ❤ by GitHub

What we have here are 4 tests; the first 2 are with ISNULL with and without a variable supplied while the last 2 use COALESCE, again with and without a variable supplied.

Test 1: ISNULL with variable

First, we are going to pass in yesterday’s date…

test1isnullwithvar
I just like GROUPING SETS…

…and we have a total for both days along with a overall total. Next let’s see what happens if we pass in a NULL.

Test 2: ISNULL without variable

When we pass a NULL into the variable, the ISNULL is going to ignore our variable and use the GETDATE() function instead.

test1isnullwithoutvar
I suppose “ignore” is the wrong word…

So, overall great results. When we have a non null value, we’re good; when we have a null value, we’re still good!

Now for COALESCE…

Test 3: COALESCE with variable

All we are changing now is using COALESCE instead of ISNULL and we’re going to pass in yesterdays date.

test2coalescewithvar
Results seem familiar…

 

No change, nothing to worry about! Now let’s pass in a null variable.

Test 4: COALESCE without variable

When we pass a NULL into the variable, the COALESCE is going to bypass our variable and use the GETDATE() function instead.

test2coalescewithoutvar
…this isn’t familiar though…where’s my results?

You may be asking yourself “What the…?!”.

I asked myself the same question but then I thought of a better idea and asked my Senior the question instead (he usually has better answers than I do), who proceeded to tell me that the final test would only work exactly at midnight!

….so I repeated my question to him.

Output:

Microsoft docs helped shed some light on this:

Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

So with our ISNULL example, when we passed in a null, the ISNULL saw that our variable was a date data type and implicitly converted the GETDATE() function into a date.

This was not the case with our COALESCE test!

When we passed in our null value, the COALESCE saw that GETDATE() returns a datetime, it measured that data type against the date data type of our variable and checked which one came first in our data type precedence (I must bookmark that page).

This means that our WHERE clause is looking for any values which match the exact date and time! Since we have no values greater than the start of today in our table, we return no results.

A picture says a thousand words though (and I’m nearly at that now)…

AlltogetherNow
Takes very little space for 1,000 words tbh…

 

 

All Together Now!

Before now, I would have said that the main difference between ISNULL and COALESCE would just have been the number of arguments that they accept. I’m going to have to watch out for the data types as well now!

It’s still nice to know how surprising SQL Server can be. 🙂

Author: Shane O'Neill

DBA, T-SQL and PowerShell admirer, Food, Coffee, Whiskey (not necessarily in that order)...

12 thoughts on “A Subtle Difference Between COALESCE and ISNULL”

  2. On a tangent, a few months ago I indirectly discovered that the ISNULL function evaluates the second expression unconditionally even if the first expression turns out to be non-null, while the COALESCE evaluates the second expression if, and only if, the first turns out to be null. If the expressions are themselves subqueries, this has potentially significant performance implications.

    Reply
  3. Pingback: Coalesce Returning Unexpected Results? | Second Life Programming

  4. I was a little surprised to find out the datatype rules of both ISNULL and COALESCE. The latter is closer to meeting my expectation, but still different. Thanks for enlightening me!

    Reply
  6. Pingback: COALESCE, ISNULL, And Data Types – Curated SQL

  7. This is interesting. I find the subtle bug fascinating, but can I ask which version? I find this behavior with ISNULL, not COALESCE. Here’s my repro on SQL 2016, SP1

    CREATE TABLE MySales
    ( SaleDate DATETIME
    , SaleAmount MONEY
    )
    INSERT MySales (SaleDate, SaleAmount)
    VALUES
    (‘20171002 9:00am’, 2000),
    (‘20171002 10:00am’, 1000),
    (‘20171003 2:00pm’, 1200),
    (‘20171004 4:59pm’, 1800)
    GO

    — Get an outlier today
    INSERT dbo.MySales (SaleDate, SaleAmount) VALUES (GETDATE(), 25000)

    GO

    CREATE OR ALTER PROCEDURE GetMyReport @dt DATE = NULL
    AS
    SELECT
    [Coalesce] = ‘Coalesce’
    , ms.SaleDate
    , ms.SaleAmount
    FROM dbo.MySales AS ms
    WHERE saledate <= COALESCE(@dt, GETDATE())
    ;

    GO

    GO
    EXEC GetMyReport
    GO
    CREATE OR ALTER PROCEDURE GetMyReport @dt DATE = NULL
    AS
    SELECT
    IsNull = ‘Isnull’
    , ms.SaleDate
    , ms.SaleAmount
    FROM dbo.MySales AS ms
    WHERE saledate <= ISNULL(@dt, GETDATE())
    ;
    GO

    EXEC GetMyReport
    ;

    DECLARE @dt DATE = NULL
    ;
    SELECT
    ms.SaleDate
    , ms.SaleAmount
    , ‘Coalesce’ = COALESCE(@dt, GETDATE())
    , ‘isnull’ = ISNULL(@dt, GETDATE())
    FROM dbo.MySales AS ms
    WHERE saledate <= ISNULL(@dt, GETDATE())
    ;

    Reply

    1. Hi Steve,
      Originally this was run on SQL Server 2014, but just ran it on SQL Server 2016 SP1-CU13 and works there.
      As far as I can tell, if you switch the operator to be greater than it should work…provided you throw some data after GETDATE() in there that will get spit out.
      Let me know!

      Reply

Leave a Reply

%d bloggers like this: