Working with NULL values

NULL is an undefined value and as such, it has a special status as values and datatypes go in SQL Server. There are quite a few potential pitfalls to watch out for when you’re dealing with data that can contain NULL values.

What is NULL?

NULL is undefined. NULL is not the same as a blank string, and it’s not the same as zero. NULL actually means “undefined value”.

Comparing NULL values

Because NULL values are not really values, but rather undefined, they can’t really be compared to anything else, not even other NULL values. Here’s an example of comparing two variables, @a and @b, both of which are NULL.

DECLARE @a int, @b int;

SELECT
    (CASE WHEN @a=@b THEN 'True' ELSE 'False' END) AS is_equal,
    (CASE WHEN @a!=@b THEN 'True' ELSE 'False' END) AS not_equal;

Both of the above comparisons will return “false”. But it gets trickier. The following expression will evaluate as true:

(CASE WHEN 'a' IN ('a', 'b', 'c', NULL)
      THEN 'True' ELSE 'False' END)

… whereas the following is false:

(CASE WHEN 'x' NOT IN ('a', 'b', 'c', NULL)
      THEN 'True' ELSE 'False' END)

Can you spot it? The correct answer is that in the first query, you can see that “a”=”a”, so it evaluates to true. In the second query, however, we don’t know what the NULL represents. NULL is an unknown, remember? So you cannot definitively say that “x” is not part of the value list.

Using IS NULL and IS NOT NULL

The proper way to compare NULL values is by using the IS NULL and IS NOT NULL keywords.

SELECT
   (CASE WHEN @a IS NULL THEN 'True' ELSE 'False' END) AS is_null,
   (CASE WHEN @a IS NOT NULL THEN 'True' ELSE 'False' END) AS not_null

Connection flags that affect NULL

Two connection flags are important in the context of working with NULL values, although they are both on the future deprecation list. This means that both these flags should always be set to ON, and in future versions and compatibility levels of SQL Server, you will not be able to change them at all.

SET ANSI_NULLS OFF modifies the way NULLs are handled. ANSI standard dictates that NULL values cannot be compared. Setting ANSI_NULLS to OFF allows NULL values to be compared. Example:

SET ANSI_NULLS OFF;
DECLARE @a int, @b int;

SELECT (CASE WHEN @a=@b THEN 'True' ELSE 'False' END) AS is_equal;
SELECT (CASE WHEN @a!=@b THEN 'True' ELSE 'False' END) AS not_equal;

SET ANSI_NULLS ON;

SET CONCAT_NULL_YIELDS_NULL OFF modifies the standard behaviour when concatenating NULL values with strings. The standard behaviour is that concatenating a NULL value with a string will result in a NULL value. If you turn this flag off, concatenated NULLs will be treated as blank strings.

This flag, however, affects only string values, and not integers, numeric values, etc.

SET CONCAT_NULL_YIELDS_NULL OFF;
DECLARE @a int, @b int;

SELECT 1234+NULL;
SELECT 'xyz'+NULL;

SET CONCAT_NULL_YIELDS_NULL ON;

Functions for working with NULL

Two T-SQL functions come to mind for working with NULL values. First off, there’s the ISNULL() function, which is similar to the COALESCE() function. ISNULL() accepts two arguments – we can refer to them as a and b. If a is not NULL, a is returned, but if a is NULL, b is returned. COALESCE() works in a very similar manner, except it accepts any number of arguments, so you could use COALESCE(a, b, c, …, n).

Notably, COALESCE() is ANSI SQL, whereas ISNULL() is T-SQL. On the other hand, ISNULL() will sometimes yield better performance. COALESCE() and ISNULL() will also handle implicit datatype conversions differently (where the datatypes of the different arguments are not equal).

The opposite of ISNULL() is NULLIF(). NULLIF() accepts two arguments, a and b, and returns NULL if a=b. Otherwise, it returns a. This function is particularly useful when performing divisions where the divisor could be 0 and you don’t want to cause a div/0 error. Here’s how:

--- This query will halt with a division by zero error if the divisor
--- is 0 on any row.
SELECT dividend/divisor FROM dbo.recordset;

--- This query will return NULL instead of a division by zero error:
SELECT dividend/NULLIF(divisor, 0.0) FROM dbo.recordset;

For more in-depth reading, check out this excellent article on COALESCE vs ISNULL on sqlmag.com.

That’s all for this week. Have a nice week and check back next sunday!