Using ISNULL vs using COALESCE for checking a specific condition?

Question

I know that multiple parameters can be passed to COALESCE, but when you want to to check just one expression to see if it doesn't exist, do you use a default or is it a better practice to use ISNULL instead?

Is there any performance gain between the two?

Solution 1

This problem reported on Microsoft Connect reveals some differences between COALESCE and ISNULL:

an early part of our processing rewrites COALESCE( expression1, expression2 ) as CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END. In [this example]:

COALESCE ( ( SELECT Nullable
             FROM Demo
             WHERE SomeCol = 1 ), 1 )

we generate:

SELECT CASE
          WHEN (SELECT Nullable FROM Demo WHERE SomeCol = 1) IS NOT NULL
          THEN (SELECT Nullable FROM Demo WHERE SomeCol = 1)
          ELSE 1
       END

Later stages of query processing don't understand that the two subqueries were originally the same expression, so they execute the subquery twice...

One workaround, though I hate to suggest it, is to change COALESCE to ISNULL, since the latter doesn't duplicate the subquery.

Solution 2

I think not, but COALESCE is in the SQL '92 standard and supported by more different databases. If you go for portability, don't use ISNULL.

Solution 3

In COALESCE you can have multiple expressions, where as in ISNULL you can check only one expression

COALESCE ( expression [ ,...n ] ) 

ISNULL ( check_expression , replacement_value )

Solution 4

Worth mentioning is that the type handling between the two can also make a difference (see this related answer item (2)).

Say a query tries to use a shortcut for writing null comparison:

select * from SomeTable
 where IsNull(SomeNullableBitField, -1) != IsNull(SomeOtherNullableBitField, -1);

which is different than

select * from SomeTable
 where coalesce(SomeNullableBitField, -1) != coalesce(SomeOtherNullableBitField, -1);

Because in the first case, the IsNull() forces the type to be a bit (so -1 is converted to true) whereas the second case will promote both to an int.

with input as 
(
  select convert(bit, 1) as BitOn,      
         convert(bit, 0) as BitOff,
         convert(bit, null) as BitNull
)
select BitOn, 
       BitOff,
       BitNull,
       IsNull(BitOn, -1) IsNullBitOn,         -- true
       IsNull(BitOff, -1) IsNullBitOff,       -- false
       IsNull(BitNull, -1) IsNullBitNull,     -- true, converts the -1 to bit
       coalesce(BitOn, -1) CoalesceBitOn,     -- 1
       coalesce(BitOff, -1) CoalesceBitOff,   -- 0       
       coalesce(BitNull, -1) CoalesceBitNull  -- -1
  from input;

There is a similar comment/link (@Martin Smith) on the question itself.

Solution 5

One major thing that I don't see explicitly indicated is that ISNULL's output type is similar to the first expression but with COALESCE it returns the datatype of value of highest precedence.

DECLARE @X VARCHAR(3) = NULL
DECLARE @Y VARCHAR(10) = '123456789'
/* The datatype returned is similar to X, or the first expression*/
SELECT ISNULL(@X, @Y) ---> Output is '123'
/* The datatype returned is similar to Y, or to the value of highest precedence*/
SELECT COALESCE(@X, @Y) ---> Output is '123456789'

View more solutions

Comments

[Admin]

The COALESCE documentation has this note: ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL...

[Martin Smith]

ISNULL will also coerce the result to the datatype of the first expression as illustrated here

[Data Masseur]

This article spells out the differences quite well... sqlmag.com/t-sql/coalesce-vs-isnull

[goodeye]

This is a good article as well... mssqltips.com/sqlservertip/2689/…

[Scratz]

Do you have any support for the claim that there is less overhead with ISNULL?

[Scratz]

OP stated that they were aware of the ability of COALESCE to handle multiple parameters, the question is about the specific case when there are only two.

[Ranadeera Kantirava]

@JoshuaDrake please read complete answer... I read question and I request you read my answer completely... Its very easy to over look some point and down vote it

[James Johnson]

@JoshuaDrake: There are two areas where COALESCE would introduce more overhead when used interchangeably. First, ISNULL deals with a fixed number of inputs, where COALESCE is designated to work with any number of inputs. Secondly, COALESCE is configured to return the data type of the expression with the highest data type precedence, whereas ISNULL returns the same type as the check_expression. As I said above, in later versions of SQL Server the difference is probably negligible, but strictly speaking there is still overhead.

[ganders]

quick question, if you have 3 values, like coalesce(expression1, expression2, expression3, 1), where those 'expressions' are actually select statements, would it then make sense to actual do nested isnull statements? ie isnull(expression1, isnull(expression2, isnull(expression3, 1)))

[underscore_d]

It's not a matter of first vs second/Nth expression. See here: 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.

[nawfal]

@AaronAnodide MySQL uses ifnull, sql server isnull.

[Suncat2000]

Oracle's alternative to COALESCE is NVL. So, the point of COALESCE being standard is valid, even if its implementation details differ among databases.