Nuances of Null – Using IsNull, Coalesce, Concat, and String Concatenation

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.


IsNull and Coalesce handing nulls

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!


Output showing nullable Color column

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


Data type of column is different depending on the arguments to IsNull and Coalesce

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.


Coalesce table creation

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:

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.

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.

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

Leave a Comment