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?

Harriettharrietta

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... – Commonwealth

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

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

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

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.

Gwen

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))) – Dam

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

Tribromoethanol

@AaronAnodide MySQL uses ifnull, sql server isnull. – Embankment

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

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

Butyraldehyde

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

Lm

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

Milquetoast

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. – Banta

Answer 6

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.

Packard

Answer 7

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/

Supat

Answer 8

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

Ileanaileane

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

@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. – Ileanaileane

Answer 9

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

Carvelbuilt

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. – Fitzsimmons

@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 – Carvelbuilt