Click on the banner to learn about and purchase my database training on Azure

SQL Server – ISNULL x COALESCE: Knowing the Differences Between the Two Functions

Reading Time: 6 minutes

A very common mistake among SQL developers is about the differences between ISNULL and COALESCE functions, where the vast majority of people believe it's just the fact that the ISNULL function allows only 1 input parameter to do the null value analysis, while the function COALESCE allows you to pass N input parameters.

However, there is another very important difference that many people end up not paying attention to and that can make you spend a lot of time trying to debug a problem and that's what I'll explain in this article.

I had an ETL process that gave an error because of this difference in data types between ISNULL and COALESCE and I ended up creating this article because I understand that other people may end up wasting time trying to understand what happened.

Difference #1 - Number of function parameters

The first big difference and the best known between the ISNULL and COALESCE functions is the amount of parameters that the functions accept.

Test base creation

Example:

Return the first non-null value of each row or 0 if all columns are NULL

Result:

As we can see in the code above, the ISNULL function accepts only one input parameter, which can be a column or a value, and a parameter for you to set the replacement value in case the first parameter is null. If you want to make comparisons between multiple columns/values, you will have to use the function multiple times, nested.

The COALESCE function accepts several input parameters (at least 2 parameters and no maximum defined), making the use of this function simpler than ISNULL.

Difference #2 – Return data type

One difference that many people end up not realizing is with regard to the return data type: While the ISNULL function considers the data type of the first column as the function's return data type, the COALESCE function's return data type will be the same data type as the parameter that will be returned (the first non-null).

In practice, this scenario causes the two functions to behave very differently.

Test base creation

Let's return the largest value from the table grouped by code:

Result:

Hmm.. Code 4 returned NULL. I want to replace this value with 0 (zero).

Result:

It worked as expected. But I prefer to use the COALESCE function, so I'll change the SQL code to use it:

Result:

Error message:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '255.55' to data type int.

What happened? With the ISNULL function it worked, but with the COALESCE function it gave an error!

This happens because, as I had explained above, the return of the ISNULL function considers the data type of the first parameter, which is the Value (VARCHAR) column. So the function's return is the value '0' (zero converted to string).

In the COALESCE function, the return data type is the same as the first non-null value, which would be the integer value 0 (zero). And with that, SQL will generate an error message when trying to convert the value '255.55' (as string) to integer (int) data type:

Practical example:

If you still want to use the COALESCE function, you will have to convert the input parameters to a common type so that all parameters can be converted by SQL Server.

Result:

Difference #3 - Function return null

Another difference that people don't pay attention to is in relation to the nullity of the return of the function. The nullability returned by the ISNULL function is always of type non-null (NOT NULL) (assuming the returned value is not null). On the other hand, the COALESCE function always returns data with the null type (NULL), even if the function returns a non-null value.

To make it easier to demonstrate, I will prepare two examples for you to visualize this better:

1 Example
I will create a table and use calculated columns with ISNULL and COALESCE functions.

Let's look at the data type and nullability options of these columns:

Result:

As we can see above, the data type of the ISNULL function followed the same type of the first parameter (Value Column = VARCHAR(100)), while the COALESCE function used as its return type, the type of the first parameter that is guaranteed not to be null, which was the value 0 (zero), an integer.

Furthermore, the calculated column created using the ISNULL function was created with the nullability determined as not null (NOT NULL), while the column created using the COALESCE function was created with the null nullity (NULL), even though it always returns a non-null value because the last parameter is a fixed value 0.

That is, even though the COALESCE function always returns a non-null value in this example, the null of the column was defined as accepting null values.

2 Example
This example can give you a headache if you try to create a primary key using calculated columns and the COALESCE function.

The command below works normally, creating a primary key in the column “ValorNaoNulo1”, being a column calculated using the ISNULL function:

Result:

However, trying to create a primary key in theNullValue2 column, which is a column calculated using the COALESCE function, we will see an error message:

Result:

Error message:

Msg 1711, Level 16, State 1, Line 3
Cannot define PRIMARY KEY constraint on column 'ValorNaoNulo2' in table '#Test3'. The computed column has to be persisted and not nullable.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint or index. See previous errors.

As the error message makes clear to us, this happened because the calculated column was created allowing null values ​​(because of using the COALESCE function) and to create a primary key the column must have nullability set to NOT allowing null values ​​( NOT NULL).

So that's it, guys!
I hope you enjoyed this tip and a big hug!

References:

- https://docs.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql
- https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql