SQL Server: NULL NULL NULL

Version: SQL Server 2019 Developer Edition

Sample Database: AdventureWorks2017

Sometimes NULL values trigger issues not only in SQL Servers, but also in most database engines. When I train my colleagues to sharpen their SQL skills, I often present examples of NULL behaviors. By understanding NULL behaviors we can reduce a lot of bugs. Even after demonstrating factors and understanding what may cause NULL behaviors, I see many developers, including myself, continue to encounter them.😓 I am sharing some cases that I came across:

#1. NULL + 10 / N'STR' + NULL / NULL + NULL

In SQL, "NULL" means data doesn't exist in the field. It does not mean zero(0) or empty('') field.

In most SQL dialects when NULL is used in arithmetic operations, or string concatenations, the output returns NULL. SQL Server has the same behaviors. [ie. NULL + [string] query will return NULL] Although these rules are very simple, it is important to understand these behaviors to reduce bugs and create optimal data sets.

***If you are using concatenating stings with SQL server 2012 or later. I recommend researching and learning more about CONCAT() function instead of using "+".

• MS Doc Link: CONCAT()
• SQL Server: CONCAT, CONCAT_WS - Concatenating fields to hash row

#2. JOIN with NULL values

This is related with case #1 above. In SQL Server, NULL = NULL and NULL <> NULL is considered as UNKNOWN. When we use a nullable column on INNER JOIN clause, the number of row counts can be different than what we expect. 

Because of this, we need to validate output more carefully when a query uses INNER JOIN clause with multiple columns which include columns that are nullable.

When I need to join nullable columns to generate reports, I like to create sample cases by converting NULL values into unique values. I do this by using COALESCE, or ISNULL functions. After I confirm that the output is what my clients want, I run query on the actual data.

...
  FROM [Table1] AS T01
  JOIN [Table2] AS T02
    ON ISNULL(T01.[Nullable Column], '9090909') = ISNULL(T02.[Nullable Column], '9090909')

...

#3. COUNT from Nullable field

Although heading #3 only mentions COUNT from Nullable field, this topic is also related with all aggregated functions.

Many users are using COUNT(*) or COUNT(1) function to get the number of rows in a table. 

However when a nullable field name is entered into the function, it is possible to get an unexpected or different number of rows  due to NULL values. 

If you look at the screenshot with the two queries above, we can see that both queries are the same except the bottom query has a nullable field name "MiddleName." As you can see, the bottom query returns less number of rows.

In SQL Server, aggregate functions ignore NULL values except COUNT(*) or COUNT(1). I usually check for this type of NULL behavior when I review my coworker's queries. I covered the negative characteristics of this NULL behavior on aggregate functions, however, this NULL behavior can also be used as a powerful feature on calculated fields. (I will discuss this on a different page.) 

#4. ORDER BY with NULL

If we use ORDER BY clause with the DESC clause, NULL values will be placed last. If we use ORDER BY clause with ASC (default) clause, NULL values will be placed first. Some SQL dialects has a syntax: (NULLS LAST/FIRST), to control the order of the NULL values. Unfortunately, SQL Server does not have this type of syntax. If we need to control the order of the NULL values using the ORDER BY clause, the query below can be helpful.

SELECT TOP 10 MiddleName
  FROM AdventureWorks2017.Person.Person
 ORDER BY IIF(MiddleName IS NULL, 1, 0), MiddleName

#5. NOT IN with NULL in subquery

Let's take a look at the query and output below. On the [Sales].[SalesPerson] table, the "TerritoryID" field has NULL values. In this case, if you run the query below, the query will not return anything. 

SELECT BusinessEntityID

  FROM Sales.SalesPerson
 WHERE TerritoryID NOT IN (SELECT TerritoryID 
			     FROM Sales.SalesTerritory)

However, if we change this query using NOT EXISTS clause instead of NOT IN clause, then you will see a different output as show below.

SELECT BusinessEntityID
  FROM Sales.SalesPerson AS P
 WHERE NOT EXISTS (SELECT 1 FROM Sales.SalesTerritory AS T
		            WHERE T.TerritoryID = P.TerritoryID)

If you are experiencing different NULL behavior compared to those mentioned above, then I recommend checking ANSI_NULLS and CONCAT_NULL_YIELDS_NULL property.

SELECT SESSIONPROPERTY('ANSI_NULLS')
SELECT SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL'

# Reference

• SET ANSI_NULLS
• CONCAT_NULL_YIELDS_NULL