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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
IF (OBJECT_ID('tempdb..#Teste1') IS NOT NULL) DROP TABLE #Teste1 CREATE TABLE #Teste1 ( Coluna1 NUMERIC(10, 2), Coluna2 NUMERIC(10, 2), Coluna3 NUMERIC(10, 2) ) INSERT INTO #Teste1 VALUES ('255.55', '4755.12', '145.55'), (NULL, NULL, NULL), (NULL, '4755.12', NULL), (NULL, NULL, '145.55') |
Example:
Return the first non-null value of each row or 0 if all columns are NULL
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- COM ISNULL SELECT ISNULL(Coluna1, ISNULL(Coluna2, ISNULL(Coluna3, 0))) FROM #Teste1 -- COM COALESCE SELECT COALESCE(Coluna1, Coluna2, Coluna3, 0) FROM #Teste1 |
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
|
1 2 3 4 5 6 7 8 |
IF (OBJECT_ID('tempdb..#Teste2') IS NOT NULL) DROP TABLE #Teste2 CREATE TABLE #Teste2 ( Codigo INT, Valor VARCHAR(100) ) INSERT INTO #Teste2 VALUES(1, '255.55'), (1, '0'), (2, '1'), (3, '1.99'), (4, NULL) |
Let's return the largest value from the table grouped by code:
|
1 2 3 |
SELECT Codigo, MAX(Valor) FROM #Teste2 GROUP BY Codigo |
Result:
Hmm.. Code 4 returned NULL. I want to replace this value with 0 (zero).
|
1 2 3 |
SELECT Codigo, MAX(ISNULL(Valor, 0)) FROM #Teste2 GROUP BY Codigo |
Result:
It worked as expected. But I prefer to use the COALESCE function, so I'll change the SQL code to use it:
|
1 2 3 |
SELECT Codigo, MAX(COALESCE(Valor, 0)) FROM #Teste2 GROUP BY Codigo |
Result:
Error message:
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT Codigo, MAX( COALESCE( CAST(Valor AS NUMERIC(18, 2) ), 0) ) FROM #Teste2 GROUP BY Codigo |
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.
|
1 2 3 4 5 6 |
IF (OBJECT_ID('tempdb..#Teste2') IS NOT NULL) DROP TABLE #Teste2 CREATE TABLE #Teste2 ( Valor VARCHAR(100) NOT NULL, ValorNaoNulo1 AS (ISNULL(Valor, 0)), ValorNaoNulo2 AS (COALESCE(Valor, 0)), ) |
Let's look at the data type and nullability options of these columns:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT A.[name], A.column_id, C.[name], A.max_length, A.[precision], A.scale, A.is_nullable FROM tempdb.sys.columns A JOIN tempdb.sys.tables B ON A.[object_id] = B.[object_id] JOIN tempdb.sys.types C ON A.system_type_id = C.user_type_id WHERE B.[name] LIKE '#Teste2%'; |
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:
|
1 2 3 4 5 6 |
IF (OBJECT_ID('tempdb..#Teste3') IS NOT NULL) DROP TABLE #Teste3 CREATE TABLE #Teste3 ( Valor VARCHAR(100) NOT NULL, ValorNaoNulo1 AS (ISNULL(Valor, 0)) PRIMARY KEY, ValorNaoNulo2 AS (COALESCE(Valor, 0)), ) |
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:
|
1 2 3 4 5 6 |
IF (OBJECT_ID('tempdb..#Teste3') IS NOT NULL) DROP TABLE #Teste3 CREATE TABLE #Teste3 ( Valor VARCHAR(100) NOT NULL, ValorNaoNulo1 AS (ISNULL(Valor, 0)), ValorNaoNulo2 AS (COALESCE(Valor, 0)) PRIMARY KEY, ) |
Result:
Error message:
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
Your explanation is very good. Congratulations ?
I enjoyed and learned.