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;
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
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
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
Behavior of Null in Concat vs + (String) Concatenation
NOTE: the Concat function exists in SQL Server 2012 and newer.
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
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!