SQL QUIZ: The Difference Between ISNULL and COALESCE

sql 0 comments suggest edit

What will the last two lines print. Will they be the same?

DECLARE @test VARCHAR(2)
DECLARE @first VARCHAR(4)
DECLARE @second< VARCHAR(4)

SELECT @first = ISNULL(@test, 'test')
SELECT @second = COALESCE(@test, 'test')

PRINT @first
PRINT @second

What do you think?

Found a typo or error? Suggest an edit! If accepted, your contribution is listed automatically here.

Comments

avatar

28 responses

  1. Avatar for jayson knight
    jayson knight January 21st, 2005

    Very interesting. Personally I'd never heard of the coalesce keyword until reading this post (had to run to BOL to check it out). isnull truncates the replacement value to the size of the expression being checked whereas coalesce does not? Interesting behavior, and not at all what I expected. Good post.

  2. Avatar for Hassan Voyeau
    Hassan Voyeau January 21st, 2005

    Same, here ... I had to test it myself. ISNULL doesn't truncate the value to the size being checked, that is just a side effect of isnull returning using the same type as the first parameter. Had we used DECLARE @test VARCHAR(4) then the result would have been 'test' and 'test'

  3. Avatar for Koba
    Koba January 21st, 2005

    Forget about this SQL stuff... tell me more about these Ads by Goooooogle.

  4. Avatar for Julian
    Julian January 24th, 2005

    Something even more interesting (and disturbing) in SQL:



    What is NULL + 0 (zero)?





  5. Avatar for Haacked
    Haacked January 24th, 2005

    It's NULL. I don't find that disturbing though. NULL is like a black hole. Append anything to NULL and the result is NULL.



    Besides 0 + something always equals something. ;)

  6. Avatar for Julian
    Julian January 24th, 2005

    Ok, maybe it's not disturbing. But imagine inheriting a very large project that the person who originally wrote the code, expected NULL + 0 = 0. Nice one.

  7. Avatar for Haacked
    Haacked January 24th, 2005

    Ha! Yes, that IS disturbing.

  8. Avatar for Sheetal
    Sheetal March 9th, 2005

    the difference between isnull and coalesce is that

    coalesce COALESCE(@test, 'test')

    can be interpreted as

    case

    when @test not null then @test

    else

    'test'

    end

  9. Avatar for kuantanzai
    kuantanzai April 28th, 2005

    Actually, Sheetal's interpretation of COALESCE IS the interpretation of ISNULL. The interpretation of COALESCE is really:



    if @test is not null then @test

    else if 'test' is not null then 'test'



    Coalesce makes an additional nullity check on 'test'.

  10. Avatar for Bobby
    Bobby May 17th, 2005

    The difference between Coalesce and Isnull is that Coalesce can take multiple values (2 or more).... it'll check each value until it finds one that is not null... once it does, it'll return that value...



    IsNull, on the other hand, has 2 arguments and will only check the first value to see if it's null, if it is null, it'll return the 2nd arguement.

  11. Avatar for Sridhara K
    Sridhara K May 29th, 2006

    Should I use COALESCE() or ISNULL()?



    As with many technology questions involving roughly equivalent choices, it depends. There are a variety of minor differences between COALESCE() and ISNULL():
    COALESCE() is ANSI standard, so that is an advantage for the purists out there.

    Many consider ISNULL()'s readability and common sense naming to be an advantage. While I will agree that it easier to spell and pronounce, I disagree that its naming is intuitive. In other languages such as VB/VBA/VBScript, ISNULL() accepts a single input and returns a single boolean output.

    ISNULL() accepts exactly two parameters. If you want to take the first non-NULL among more than two values, you will need to nest your ISNULL() statements. COALESCE(), on the other hand, can take multiple inputs:

    SELECT ISNULL(NULL, NULL, 'foo')

    -- yields:
    Server: Msg 174, Level 15, State 1, Line 1
    The isnull function requires 2 arguments.

    SELECT COALESCE(NULL, NULL, 'foo')

    -- yields:
    ----
    foo


    In order to make this work with ISNULL(), you would have to say:

    SELECT ISNULL(NULL, ISNULL(NULL, 'foo'))


    The result of ISNULL() always takes on the datatype of the first parameter (regardless of whether it is NULL or NOT NULL). COALESCE works more like a CASE expression, which returns a single datatype depending on precendence and accommodating all possible outcomes. For example:

    DECLARE @foo VARCHAR(5)
    SET @foo = NULL
    SELECT ISNULL(@foo, '123456789')

    -- yields:
    -----
    12345

    SELECT COALESCE(@foo, '123456789')

    -- yields:
    ---------
    123456789


    This gets more complicated if you start mixing incompatible datatypes, e.g.:

    DECLARE @foo VARCHAR(5), @bar INT
    SET @foo = 'foo'
    SET @bar = NULL

    SELECT ISNULL(@foo, @bar)
    SELECT COALESCE(@foo, @bar)

    -- yields:

    -----
    foo

    Server: Msg 245, Level 16, State 1, Line 6
    Syntax error converting the varchar value 'foo' to a column of data type int.


    A relatively scarce difference is the ability to apply constraints to computed columns that use COALESCE() or ISNULL(). SQL Server views a column created by COALESCE() as nullable, whereas one using ISNULL() is not. So:

    CREATE TABLE dbo.Try
    (
    col1 INT,
    col2 AS COALESCE(col1, 0)
    PRIMARY KEY
    )
    GO

    -- yields:
    Server: Msg 8111, Level 16, State 2, Line 1
    Cannot define PRIMARY KEY constraint on nullable column in table 'Try'.
    Server: Msg 1750, Level 16, State 1, Line 1
    Could not create constraint. See previous errors.


    Whereas the following works successfully:

    CREATE TABLE dbo.Try
    (
    col1 INT,
    col2 AS ISNULL(col1, 0)
    PRIMARY KEY
    )
    GO


    If you are using COALESCE() and or ISNULL() as a method of allowing optional parameters into your WHERE clause, please see Article #2348 for some useful information (the most common techniques will use a scan, but the article shows methods that will force a more efficient seek).

    Finally, COALESCE() can generate a less efficient plan in some cases, for example when it is used against a subquery. Take the following example in Pubs and compare the execution plans:

    USE PUBS
    GO

    SET SHOWPLAN_TEXT ON
    GO

    SELECT COALESCE
    (
    (SELECT a2.au_id
    FROM pubs..authors a2
    WHERE a2.au_id = a1.au_id),
    ''
    )
    FROM authors a1

    SELECT ISNULL
    (
    (SELECT a2.au_id
    FROM pubs..authors a2
    WHERE a2.au_id = a1.au_id),
    ''
    )
    FROM authors a1
    GO

    SET SHOWPLAN_TEXT OFF
    GO


    Notice the extra work that COALESCE() has to do? This may not be a big deal against this tiny table in Pubs, but in a bigger environment this can bring servers to their knees. And no, this hasn't been made any more efficient in SQL Server 2005, you can reproduce the same kind of plan difference in AdventureWorks:

    USE AdventureWorks
    GO

    SET SHOWPLAN_TEXT ON
    GO

    SELECT COALESCE
    (
    (SELECT MAX(Name)
    FROM Sales.Store s2
    WHERE s2.name = s1.name),
    ''
    )
    FROM Sales.Store s1

    SELECT ISNULL
    (
    (SELECT MAX(Name)
    FROM Sales.Store s2
    WHERE s2.name = s1.name),
    ''
    )
    FROM Sales.Store s1

    GO
    SET SHOWPLAN_TEXT OFF
    GO





  12. Avatar for Mark L
    Mark L July 26th, 2006

    the difference is this:
    ISNULL is a non standard Microsoft VB derived statement, where COALESCE is an ANSI standard function.
    Use ISNULL and your code is likely useless on non MS platforms. Any developer with foresight skips vendor specific additions and sticks to industry standards.

  13. Avatar for Dolan
    Dolan September 8th, 2006

    I am trying to create a formula for a table that will concatenate the first , middle, and last name columns and append the suffix if not null with the comma.
    (coalesce([first_name],'') + ' ' + coalesce([middle_name],'') + ' ' + coalesce([last_name],'') + coalesce([suffix],''))
    The problem is getting the comma only when the suffix is not null.

  14. Avatar for Simon Smith
    Simon Smith December 13th, 2006

    >It's NULL. I don't find that disturbing
    >though. NULL is like a black hole. Append
    >anything to NULL and the result is NULL.
    >Besides 0 + something always equals something. ;)
    what about NULL / NULL ?

  15. Avatar for cbmeeks
    cbmeeks December 18th, 2006
  16. Avatar for Aparichit
    Aparichit January 16th, 2007

    To bobby,
    The difference between Coalesce and Isnull is that Coalesce can take multiple values (2 or more).... it'll check each value until it finds one that is not null... once it does, it'll return that value...
    IsNull, on the other hand, has 2 arguments and will only check the first value to see if it's null, if it is null, it'll return the 2nd arguement.

    As you mentioned that Coalesce can take multiple values (2 or more).... it'll check each value until it finds one that is not null... once it does, it'll return that value...
    My question is that, if we dont have any not null value in the multiple values in coalesce then what will be value returned?

  17. Avatar for Sagar Chavan
    Sagar Chavan April 24th, 2007

    Isnull(@test,'test')
    replaces all the null values with 'test'
    whereas Coalesce returns first non null record

  18. Avatar for chis
    chis June 12th, 2007

    what will happen if this is the case:
    TABLE-1
    BRANCH CHAR(5) - NOT NULL WITH DEFAULT
    AMOUNT DECIMAL(15,2) - NOT NULL WITH DEFAULT
    SELECT BRANCH, COALESCE (AMOUNT, 0)
    FROM TABLE-1
    What will be the output? when it is not null, it will show the value of the amount, right. But let us assume that the amount is null, will the value be 0 (zero)?

  19. Avatar for Andrew
    Andrew June 27th, 2007

    hey Guys Why the Code is not Working..

  20. Avatar for jasjeet
    jasjeet August 1st, 2007

    A very Good Stuff. Thanks for sharing this wonderful knowledge.

  21. Avatar for sivsam
    sivsam September 3rd, 2007

    Exactly what I was looking for...thanks

  22. Avatar for Divya
    Divya December 13th, 2007

    its a nice article..... got to know a lot .... :)

  23. Avatar for Gelder
    Gelder February 13th, 2008

    It is correct to say that coalesce will take a multiple value test for nullability for example say that you have a three column in a table and there will only ever be one value in any of the three that is when it will become useful for examle
    Table
    Col1 – Col2 – Col3
    Null – Null – 10
    9 – Null – Null
    and you ran the query
    Select coalesce(col1,col2,col3) as price from table
    Then it would just display the column with values entered, where as isnull has a limit of just checking one column

  24. Avatar for paulo
    paulo April 8th, 2008

    nice work thanks

  25. Avatar for ana
    ana June 19th, 2008

    DECLARE @test VARCHAR(2)
    DECLARE @first VARCHAR(4)
    DECLARE @second VARCHAR(4)
    SELECT @first = ISNULL(@test, 'test')
    SELECT @second = COALESCE(@test, 98988)
    PRINT @first
    PRINT @second
    it displays
    te
    *

  26. Avatar for Gangz
    Gangz March 2nd, 2009

    Good article and informative, Gave a great idea about the keywords
    COALESCE and ISNULL.

  27. Avatar for Piyush Bajaj
    Piyush Bajaj July 6th, 2011

    The answer of your question :
    The results will be same.. :)

  28. Avatar for Kevin
    Kevin May 21st, 2014

    Not sure of the specifics, but the variables are of type varchar, whereas your 98988 value is an integer - so you are checking varchars but trying to return in integer in your coalesce statement.
    If you instead enclose 98988 in single quotes ('98988') making it a string value, you results would be:
    test
    9898