ISNULL vs. COALESCE: What The XML?

This isn’t about performance

If you’re interested in performance tests, you can get in the way back machine and read a couple posts by Adam Machanic here and here. I’m also not talking about the difference between them. There are a million articles about that. They’re obviously different, but how does SQL handle that internally?

We’re going to run the four queries below separately, and look at the XML of the execution plan for each.

Even though COALESCE is the ANSI standard, I still see most people using ISNULL. Spelling? Laziness? Who knows? Under the covers, it’s just a CASE expression. You could write the same thing yourself.

No, I don't think JSON is a good idea either.

No, I don’t think JSON is a good idea either.

The second query gives you almost the same thing, but the columns are reversed. What’s the point, then?

ISNULL ISDIFFERENT

SQL does something rather smart here. The Id column is NOT NULL, so when ISNULL is applied to it, it doesn’t bother evaluating anything.

That seems reasonable.

That seems reasonable.

Reversed, ISNULL, and whatever magical behind-the-scenes code Microsoft has thrown in there, runs against the Age column, which is NULLable.

Buzzkill.

Buzzkill.

Pros and Cons

ISNULL Pros

  • Easy to spell
  • Shortcuts if you do something dumb
  • Not the name of a horrible band

ISNULL Cons

  • Only two inputs
  • Not ANSI standard
  • Maybe doesn’t shower regularly

Coalesce Pros

  • ANSI Standard
  • Multiple inputs
  • Drinks coffee black

Coalesce Cons

  • Spelling
  • Just a case expression
  • Is the name of a horrible band

Thanks for reading!

Previous Post
SQL Server 2005 support ends next week. Here’s my tribute to it.
Next Post
For Technical Interviews, Don’t Ask Questions. Show Screenshots.

14 Comments. Leave new

  • I always love a little 90’s hardcore/metalcore/whatevercore reference mixed in with SQL knowledge.

    Reply
  • Much better post than Mladen and Stanislas Biron’s posts. COALESCE until Microsoft ends our support (or until we die)!

    Reply
  • The data type of the output is also determined differently. ISNULL returns the data type of the first argument, whereas COALESCE returns the highest precedence data type of all of the arguments. Not an insignificant difference.

    Reply
    • I agree. The article said “I’m also not talking about the difference between them. There are a million articles about that” but, personally, I think those differences need to be considered in the Pros and Cons list. I default to using COALESCE specifically because of the potential subtle bug caused by ISNULL silently adopting the data type of the first parameter.

      Reply
      • Erik Darling
        April 5, 2016 6:11 pm

        The differences aren’t necessarily good or bad, just things to consider. I’m also not sure I’d call documented behavior a bug.

        Reply
  • We ran across an issue yesterday where SQL server decided it would only use an index on a joined table if we used IsNull() instead of Coalesce():

    Joining like this caused SQL Server to do a table scan on ThirdTable (query ran for hours before we killed it):
    left join ThirdTable tbl3 on tbl3.KeyCol = coalesce(tbl1.KeyCol,tbl2.KeyCol)

    Joining like this caused SQL Server to use the KeyCol-based index on ThirdTable (query finished in 19 seconds):
    left join ThirdTable tbl3 on tbl3.KeyCol = isnull(tbl1.KeyCol,tbl2.KeyCol) — use tbl3.KeyCol-b

    Data types on KeyCol are identical across all three tables.

    Any ideas what’s going on here?

    We always use coalesce() in our code since it’s more portable. We have other views with similar joins using coalesce() and it’s not a problem. This particular instance was working fine since we put the view in place 4 weeks ago, and only became problematic yesterday. ThirdTable has just under 1 million rows and increased by about 2200 rows yesterday. Updating statistics on all tables involved made no difference.

    Reply
    • Bill – your best bet is probably to post the execution plans using SQL Sentry Plan Explorer. It’s got an Anonymizer feature that will anonymize your plans and post them to SQLPerformance.com where folks can review them in more detail.

      Reply
    • Joe O'Connor
      April 8, 2016 8:12 am

      Any chance the fields you’re joining on have variable precision? If so, you may want to try explicitly casting it to the exact column definition to see if it changes any behavior.

      Reply
  • Perhaps it is worthwhile also surfacing the difference in behavior between ISNULL and COALESCE mentioned here:
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2b751e93-c7ae-48bc-9289-3015d3c37972/bug-in-isnull-function?forum=sqltools

    Reply
  • For the main developer use case I think ISNULL is more readable and implies the intent better. i.e. Yes the field is from a LEFT OUTER JOIN’ed table but I don’t want a null in my returned results.

    Reply
  • A little bit of history about coalesce (). The ANSI X3H2 committee was considering this, we look for a name for this function. Phil Shaw from IBM was one of the early committee members and one of the smartest guys we had. He was good on technical issues, ISO procedural issues, and the general functioning of the committee. He always carried a copy of Roberts rules of order a dictionary and thesaurus with him.

    Phil pulled pulled out his thesaurus, and looked for a synonym that was the least likely to ever be used as a column name and a real table (honestly!). That candidate word was “coalesce”, and that is why we picked it.

    Reply
    • Erik Darling
      April 7, 2016 10:50 am

      I bet if he didn’t have that thesaurus with him, it would have been called MUSH() or something. Which, maybe…

      Reply
  • In the case of such a pattern
    Select
    Coalesce ((Select Value From Test Where ID = @ID, -1)
    it makes sense to use
    Select
    IsNull ((Select Value From Test Where ID = @ID, -1)
    because of the number of seeks

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}