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?

Answer 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.

Answer 2

The NULL and COALESCE are not always interchangeable. It deserves to know their differences in order to know when its better to use the one over the other:

The table above is comparison between ISNULL and COALESCE from Exam Ref 70-761 Querying Data with Transact-SQL book written by Itzik Ben-Gan.

---

1. Number of supported parameters - 2 for ISNULL vs >2 when using COALESCE
2. ISNULL is proprietary T-SQL feature and COALESCE is ISO/ANSI SQL standard

3. The data type of the result is important. After reading notes in the table above, check the following cases:

   DECLARE @x VARCHAR(3)  = NULL
          ,@y VARCHAR(10) = '1234567890';
   
   SELECT ISNULL(@x, @y) AS [ISNULL], COALESCE(@x, @y) AS [COALESCE];
   

   

   The ISNULL is getting the data type of the first argument as it is the not NULL literal. It is VARCHAR(3) and is a result, the second argument data is cut to match it. With COALESCE the data type if highest precedence is used.

   DECLARE @x VARCHAR(8)  = '123x5'
          ,@y INT = 123;
   
   SELECT ISNULL(@x, @y) AS [ISNULL];
   SELECT COALESCE(@x, @y) AS [COALESCE];
   

   

   

   The ISNULL is returning the data type of first argument, while in COALESCE we are getting error, as the INT has highest precedence and the conversion of the first argument value to INT fails.

4. The nullability of the result can be important, too. For, example:

   DECLARE @x VARCHAR(3) = NULL
          ,@y VARCHAR(3) = NULL;
   
   DROP TABLE IF EXISTS [dbo].[DataSource01];
   
   SELECT ISNULL(10, 20) AS [C1]
         ,ISNULL(@x, 'text') AS [C2]
         ,ISNULL(@x, @y) AS [C3]
   INTO [dbo].[DataSource01];
   
   DROP TABLE IF EXISTS [dbo].[DataSource02];
   
   SELECT COALESCE(10, 20) AS [C1]
         ,COALESCE(@x, 'text') AS [C2]
         ,COALESCE(@x, @y) AS [C3]
   INTO [dbo].[DataSource02];
   

   Let's check the Nullable property of each column:

   

   

   Using COALESCE we have a NOT NULL property of column set to Yes, only when all of the inputs are non null-able.

5. According to the SQL standard, the COALESCE expression is translated to:

   CASE WHEN (<subquery>) IS NOT NULL THEN (<subquery>) ELSE 0 END
   

   If the result of the execution of the subquery in the WHEN clause isn’t NULL, SQL Server executes it a second time in the THEN clause. In other words, in such a case it executes it twice. Only if the result of the execution in the WHEN clause is NULL, SQL Server doesn’t execute the subquery again, rather returns the ELSE expression. So when using subqueries, the ISNULL function has a performance advantage.

Answer 3

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'

Answer 4

This explanation gives clear about coalesce vs isnull

The COALESCE function in SQL returns the first non-NULL expression among its arguments. The syntax for COALESCE is as follows:

 COALESCE ("expression 1", "expressions 2", ...)

It is the same as the following CASE statement:

SELECT CASE ("column_name")
  WHEN "expression 1 is not NULL" THEN "expression 1"
  WHEN "expression 2 is not NULL" THEN "expression 2"
  ...
  [ELSE "NULL"]
  END
FROM "table_name";

In SQL Server, the ISNULL( ) function is used to replace NULL value with another value.

select CountryName = ISNULL("columnname", 'INDIA') from Countries

Coalesce return first non null expression where as isnull() is used to replace null value with our desired value.

COALESCE is a part of ANSI standards and are available in almost all databases.

when deciding between ISNULL v COALESCE there parameters has to be taken care off:

1. COALESCE determines the type of the output based on data type precedence where as With ISNULL, the data type is not influenced by data type precedence.

2. Consider following sql statements

   DECLARE @c5 VARCHAR(5);
   SELECT 'COALESCE', COALESCE(@c5, 'longer name')
   UNION ALL
   SELECT 'ISNULL',   ISNULL(@c5,   'longer name');
   

Results:

COALESCE longer name
ISNULL   longe

This happens because ISNULL takes the data type of the first argument, while COALESCE inspects all of the elements and chooses the best fit (in this case, VARCHAR(11))

For more detailed explanation on deciding between COALESCE vs ISNULL check this: https://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/

Answer 5

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.

Answer 6

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.

Answer 7

In COALESCE one can use multiple expressions, It will return value which is not a null and occurs first... for example

DECLARE @Value1 INT, @Value2 INT, @Value3 INT, @Value4 INT
SELECT @Value2 = 2, @Value4 = 4
SELECT COALESCE(@Value1, @Value2, @Value3, @Value4)
SELECT COALESCE(@Value1, @Value4, @Value3, @Value2)

And in ISNULL if expression null it will return second parameter provided, and of course you can check only for one expression...

So if want check multiple expression and select first not null among them, then use coalesce otherwise go for ISNULL

Answer 8

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 )

Answer 9

Where there is only one null condition, ISNULL will have less overhead. The difference is probably negligible, though.