Differences between ISNULL and COALESCE


I came across a question in the SQL Server MVP newsgroup recently about ISNULL and COALESCE usage. COALESCE basically translates to CASE expression and ISNULL is a built-in implemented in the database engine. Both ISNULL and COALESCE can be used to get the same results but there are some differences.


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


2. The NULLability of result expression is different for ISNULL and COALESCE. ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE is not. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different NULLability values. This makes a difference if you are using these expressions in computed columns and creating key constraints or making return value of a scalar UDF deterministic so that it can be indexed.


Please note that I am referring to expressions that will alwahys return a non-NULLable value here. Otherwise, you can have ISNULL or COALESCE return NULL value just fine.


3. Validations for ISNULL and COALESCE is also different. For example, NULL value for ISNULL is converted to int whereas for COAELSCE you have to provide a type. Ex:


 ISNULL(NULL, NULL) — is int


COALESCE(NULL, NULL) — Will throw an error


COALESCE(CAST(NULL as int), NULL) — it valid and returns int


4. ISNULL takes only 2 parameters whereas COALESCE takes variable number of parameters


5. COALESCE is based on the ANSI SQL standard whereas ISNULL is a proprietary TSQL function 


6.  You could get different plans for queries using ISNULL & COALESCE if the expressions involve scalar sub-queries. This will make a performance difference and queries with COALESCE often fare worse here. See below repro script:


use tempdb


go


create table t1 ( i int );


create table t2 ( i int );


create table t3 ( i int );


go


set showplan_text on;


go


select isnull((select i from t1 where t1.i = t2.i), (select max(i) from t3))


from t2;


select coalesce((select i from t1 where t1.i = t2.i), (select max(i) from


t3))


from t2;


go


set showplan_text off;


go


drop table t1, t2;


go

Comments (8)

  1. hugo@perfact.info says:

    It’s good to point out these differences, but there are unfortunately two errors, and two missing differences.

    Ad 2. An ISNULL is not always considered NOT NULLable. The nullability of ISNULL depends on the NULLLability of the second parameter. I.e., if it’s a constant or a column (or expression) that can’t be NULL, it’s NOT NULLable. But if the second expression is NULLable (or even a NULL constant!), the ISNULL is considered to be NULLable. That’s why SELECT ISNULL(NULL,NULL) returns NULL and not an error message.

    Ad 3. This is just incorrect. It is perfectly legal for all expressions in a COALESCE to be NULL, in which case the COALESCE will evaluate to NULL (which is, incidentally, exactly why the result of COALESCE is always considered to be NULLable). If you execute DECLARE @a int, @b int; SELECT COALESCE(@a, @b), you’ll get NULL, not an error.

    Note: SELECT COALESCE(NULL, NULL) will thrown an error – not because all arguments are NULL, but because SQL Server is unable to infer a datatype for the result from the input. All inputs may be NULL, but at least one needs to have a type associated. SELECT COALESCE(CAST(NULL AS int), NULL) runs fine!

    5. ISNULL is limited to two arguments. If you need the first non-NULL value from a list of three, you have to nest two ISNULL expressions. Make it a list of 20, and you are knee-deep in the nested parentheses. COALESCE, however, will take any number or arguments and return the first non-NULL value.

    6. COALESCE is ANSI-standard, ISNULL is not. Even if there are no plans to port now, tomorrow or next year, it never hurts to get used to typing those two extra characters to save yourself some work if these plans happen to change in a year or two.

    Best, Hugo

  2. sqletips says:

    Hi Hugo,

    Thanks for your comments. I did say that NULLability for ISNULL & COALESCE is different if the return value is always non-nullable one. So I was implying that you have an expression that will never return a NULL value.

    And regarding the COALESCE always considered to be NULLable. It is not because that all expressions in COALESCE can be NULL. Consider expressions below:

    ISNULL(@a, 0)

    COALESCE(@a, 0)

    Both these expressions will produce the same results and can never be NULL. But we do not consider the COALESCE expression to be NOT NULLable. Internally in the engine every built-in has an attribute for NULLability, determinism and so on. It just so happens that we treat COALESCE differently if you have an expression that can never return NULL. It has nothing to do with which inputs are legal for COALESCE & ISNULL. The query optimizer could do a better job of evaluating expressions with COALESCE so that you can write deterministic expressions either way.

    >> Note: SELECT COALESCE(NULL, NULL) will thrown an error – not because all arguments are NULL,
    >> but because SQL Server is unable to infer a datatype for the result from the input. All inputs may be NULL,
    >> but at least one needs to have a type associated. SELECT COALESCE(CAST(NULL AS int), NULL) runs fine!

    This is not true. We assume type of NULL as int for ISNULL(NULL, NULL). We are inconsistent with NULL value type. For COALESCE, I believe we follow the ANSI standard and that is why we enforce that you have provide a value for which the type is known. This again goes back to the rules of COALESCE type is determined. Similarly, when you do CAST(NULL sql_variant) there is no implit type assigned to the NULL value.

    I will add the other obvious differences between ISNULL & COALESCE to make the article complete.

    Thanks
    UC

  3. jacob.seb says:

    Hi UC,

    Why does it return and INT data type?

    DECLARE @var1 INT, @var2 FLOAT

    SELECT @var2 = 1.0, @var1 = 1

    SELECT COALESCE(@var2, @var1) AS Value

    /*

    Value

    ———————-

    1

    */

    should n’t it return FLOAT because @var2 is FLOAT?

    And why does the following two queries always return 1 (INT)?

    DECLARE @var1 INT, @var2 FLOAT

    SELECT @var2 = 1.0, @var1 = 1

    SELECT ISNULL(@var2, @var1) AS value

    /*

    value

    ———–

    1

    */

    SELECT ISNULL(@var1, @var2) AS value

    /*

    value

    ———–

    1

    */

    shouldn’t the first example return FLOAT data type (1.0) ?

    Thanks

    Jacob

  4. jacob.seb says:

    spelling mistake:

    pls read "Why does it return and INT data type?" as "Why does it return an INT data type?"

  5. Found this neat post from the SQL Tips and Tricks teams on the fundamental differences of ISNULL and

  6. bwperrin says:

    I have what seems like a pretty alarming bug where these two behave differently.  Nothing to do with types as far as I can tell – isnull is just plain wrong.

    Try this:

    select t.id, val, bug = isnull(val, 0)

    from (select id=1 union select 2 union select 3) t

       left join (select id = 2, val = 1) u on u.id = t.id

    Replace "isnull" with "coalesce" and all is well.  What the heck?

  7. bwperrin says:

    I should’ve mentioned I was using SQL 2000 SP3a or 4 – "bug" returns all 1’s!  This appears to be fixed in SQL 2005.

  8. Much needed information 🙂