Null marks are a fact of life in relational databases. Sometimes it is good to revisit the basics of how they are treated in basic system functions and T-SQL because they can be tricky. Look at the following scenarios of how null marks are treated different than the unsuspecting eye might see.

Treatment of Null in IsNull vs Coalesce

The IsNull function in SQL Server takes 2 arguments – one for the expression to evaluate for null and the other that it will return if the first argument is null.

The Coalesce function takes n arguments and returns the first non-null value passed to it. While coalesce is somewhat more robust don’t forget that at the heart it is simply using a case statement switch to check if the value is null then return control flow. This means the arguments passed here will be evaluated multiple times. IsNull is a function and will only be evaluated once.

NOTE: IsNull is proprietary of T-SQL while Coalesce is ANSI standard. Therefore whenever possible choose coalesce for portability concerns.

USE AdventureWorks2014;
GO

select ProductID, Name, Color, isnull(Color, 'Unknown') as 'Color-IsNull', coalesce(Color, 'Unknown') as 'Color-Coalesce'
from Production.Product;

USE AdventureWorks2014;

select ProductID, Name, Color, isnull(Color, 'Unknown') as 'Color-IsNull', coalesce(Color, 'Unknown') as 'Color-Coalesce'

from Production.Product;

Another variation has to do with the data types of the arguments passed. From BOL:

Data type determination of the resulting expression is different. 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

Consider loading a table using these functions. You might expect the column data type and nullability to be the same but they are different!

--IsNull
select ProductID, Name, isnull(Color, 'Unknown') as 'Color'
into Production.Product_isnull
from Production.Product;

--Coalesce
select ProductID, Name, coalesce(Color, 'Unknown') as 'Color'
into Production.Product_coalesce
from Production.Product;

exec sp_help 'Production.Product_isnull'; --Color is not nullable
exec sp_help 'Production.Product_coalesce'; --Color is nullable

--IsNull

select ProductID, Name, isnull(Color, 'Unknown') as 'Color'

into Production.Product_isnull

from Production.Product;

--Coalesce

select ProductID, Name, coalesce(Color, 'Unknown') as 'Color'

into Production.Product_coalesce

from Production.Product;

exec sp_help 'Production.Product_isnull'; --Color is not nullable

exec sp_help 'Production.Product_coalesce'; --Color is nullable

This example shows the data type changing depending on how the functions are evaluated.

select isnull(12,'unknown') as 'Col1' into IsNullTest1;
select isnull('twelve','unknown') as 'Col1' into IsNullTest2;

exec sp_help 'IsNullTest1'; --Col1 is int
exec sp_help 'IsNullTest2'; --Col1 is varchar

select isnull(12,'unknown') as 'Col1' into IsNullTest1;

select isnull('twelve','unknown') as 'Col1' into IsNullTest2;

exec sp_help 'IsNullTest1'; --Col1 is int

exec sp_help 'IsNullTest2'; --Col1 is varchar

We expect this much from IsNull. However, coalesce is a little different. It will take the data type from the first non-null value passed and use that for the table definition. This might not always be what you want because if you pass bits you might get integers. If you pass an array of integers and floats you will get numeric. Be aware if this isn’t what you wanted.

select coalesce(12,'pizza',44) as 'Col1' into CoalesceTest1;
select coalesce(45,12.2,6) as 'Col1' into CoalesceTest2;

select * from CoalesceTest1;
select * from CoalesceTest2;

exec sp_help 'CoalesceTest1'; --Col1 is int
exec sp_help 'CoalesceTest2'; --Col1 is numeric

select coalesce(12,'pizza',44) as 'Col1' into CoalesceTest1;

select coalesce(45,12.2,6) as 'Col1' into CoalesceTest2;

select * from CoalesceTest1;

select * from CoalesceTest2;

exec sp_help 'CoalesceTest1'; --Col1 is int

exec sp_help 'CoalesceTest2'; --Col1 is numeric

Behavior of Null in Concat vs + (String) Concatenation

Consider 2 different ways of concatenating – the Concat function and the + string concatenation operator. The concat function takes n arguments and concatenates them in the sequence they were passed.

NOTE: the Concat function exists in SQL Server 2012 and newer.
For example:

declare @a varchar(10) = 'pizza';
declare @b varchar(10) = 'party';
declare @c varchar(10) = null;
declare @d varchar(10) = '!!';

select @a + @b + @d; --pizzaparty!!, only if we skip the null var
select @a + @b + @c + @d; --the whole string turns null

select CONCAT(@a, @b, @c, @d); --pizzaparty!!

declare @a varchar(10) = 'pizza';

declare @b varchar(10) = 'party';

declare @c varchar(10) = null;

declare @d varchar(10) = '!!';

select @a + @b + @d; --pizzaparty!!, only if we skip the null var

select @a + @b + @c + @d; --the whole string turns null

select CONCAT(@a, @b, @c, @d); --pizzaparty!!

The + operator is used for string concatenation when the operand to the left of the operator is a string data type (char, varchar, ntext, etc). As SQL Server evaluates the statement of expressions left to right and sees a string followed by the string concatenation operator it knows to concatenate to the operand to the right. When it encounters a null value the entire concatenated strings turns null. Makes sense because a null marker represents an unknown value. Adding “unknown” to the equation makes the result “unknown”.

NOTE: if the operand to the left of + is not a string data type and a number data type then SQL Server will assume you want to perform arithmetic instead and try to add them.

select 2 + 99; --101
select 'I am number: ' + 99; --Conversion failed when converting the varchar value 'I am number: ' to data type int.
select 'I am number: ' + cast(99 as varchar); --I am number: 99
select 99 + 'different ways'; --Conversion failed when converting the varchar value 'different ways' to data type int.
select cast(99 as char(2)) + ' different ways'; --99 different ways

select 2 + 99; --101

select 'I am number: ' + 99; --Conversion failed when converting the varchar value 'I am number: ' to data type int.

select 'I am number: ' + cast(99 as varchar); --I am number: 99

select 99 + 'different ways'; --Conversion failed when converting the varchar value 'different ways' to data type int.

select cast(99 as char(2)) + ' different ways'; --99 different ways

Conclusion

Most importantly be mindful of the way null markers are handled when checking for null in string manipulation and overall usage. As a result this can prevent unexpected bugs in your code that can be hard to debug.

Does reading about nulls make you hungry for more? Please subscribe!

Yes I want to Subscribe!

One thought on “Nuances of Null – Using IsNull, Coalesce, Concat, and String Concatenation”

Dealing With NULL – Curated SQL says:

April 24, 2017 at 5:05 pm

[…] Jeff Mlakar has a pair of comparisons for NULL handling, with ISNULL vs COALESCE and CONCAT vs + fo…: […]