The obvious difference

The IsNull() Function is pretty straight forward. You pass in one or two values. If the first one is not Null, it will return that one. If the first one is Null, it will return the second one.

It works like this:

declare @Date1 datetime = null
declare @Date2 datetime = getdate()

select isnull(@Date1, @Date2)
GO

If both Dates are Null, it simply returns Null. But what if you would like to set a Default? Then you would need a third Value.

This is where Coalesce() comes in. With this one you can add an unlimitted number of Values and it will return the first in line that is not null.

So if you wanted a Default Value as a fall-back scenario, you could do something like this:

declare @Date1 datetime = null
declare @Date2 datetime = null

select coalesce(@Date1, @Date2, getdate())
GO

So, whereas the IsNull() Function can only handle 2 Values, Coalesce() can handle multiple Values. But besides this, there is another difference that is often overlooked. Resulting in unexpected errors.

Handling the Data Type

There is a difference in the way the Data Type is handled. We already saw this in my Post on the IsNull() behavior.

The IsNull() Function uses the same Data Type from the first Parameter for the Return Value.

Coalesce() however, uses the Data Type from the first Parameter that is not Null for the Return Value. See for yourself:

declare @Date1 datetime = null
declare @Date2 datetime2 = getdate()

select @Date2
select isnull(@Date1, @Date2)
select coalesce(@Date1, @Date2)
GO

Even while @Date2 is a DateTime2 Data Type, it is returned as a DateTime Data Type with the IsNull() Function. So it is loosing its precision of 7 behind the decimal point and it only has a precision of 3 left after the IsNull().

If we do a bit of swapping with this Query, you will see that the DateTime with the Value turns into a DateTime2:

declare @Date1 datetime = getdate()
declare @Date2 datetime2 = null

select @Date1
select isnull(@Date2, @Date1)
select coalesce(@Date2, @Date1)
GO

This might lead to confusion and errors, especially if you have something like this one:

declare @Text1 nvarchar(5) = null
declare @Text2 nvarchar(10) = '0123456789'
declare @Text3 nvarchar(10) = @Text2

select IIF(
  isnull(@Text1, @Text2) = @Text3,
  concat('Equal: ', isnull(@Text1, @Text2), ' ', @Text3),
  concat('NotEqual: ', isnull(@Text1, @Text2), ' ', @Text3)
)

select IIF(
  coalesce(@Text1, @Text2) = @Text3,
  concat('Equal: ', coalesce(@Text1, @Text2), ' ', @Text3),
  concat('NotEqual: ', coalesce(@Text1, @Text2), ' ', @Text3)
)

select isnull(@Text1, @Text2)
select coalesce(@Text1, @Text2)
GO

Here I've set @Text3 to have the exact same Value of @Text2. But when we use the IsNull() in the first compare, they don't seem to be equal. With the Coalesce, they are.

The reason for that is the Data Type of @Text1 which in an nvarchar(5). Because IsNull() returns the same Data Type of the first Value, the output is also an nvarchar(5). And due to this behavior, a part of the text got truncated, resulting in @Text2 and @Text3 no longer being equal.

With the 2 Select statements you can clearly see the difference.

That's it for today!

As you can see IsNull() and Coalesce() can be used for the same purpose. Coalesce() has the advantage that you can use more than 2 Values so you can optionally set a Default Value in case all Values are Null.

But whereas IsNull() uses the Data Type of the first Value, Coalesce() returns the Data Type of the first Value that is not Null.

And this can lead to confusion and even errors, as you have seen with the last example.

So, now you know about this behavior and can avoid falling for it. When using IsNull() or Coalesce(), choose wisely!

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!