次の方法で共有

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

  • Anonymous
    June 27, 2008
    The comment has been removed

  • Anonymous
    June 28, 2008
    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

  • Anonymous
    September 26, 2008
    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

  • Anonymous
    September 26, 2008
    spelling mistake: pls read "Why does it return and INT data type?" as "Why does it return an INT data type?"

  • Anonymous
    January 04, 2009
    The comment has been removed

  • Anonymous
    May 30, 2009
    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?

  • Anonymous
    May 30, 2009
    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.

  • Anonymous
    June 07, 2013
    Much needed information :)