NULLs

I just saw a question on SQLServerCentral about NULLs behavior and thought I could write something about the common gotchas when using NULL in T-SQL, and why they behave the way they do.

To go into this, we need to understand the logic behind what a NULL represents. A NULL is used, or at least should be used, to denote the absence of information. It is not a 0 or a blank string or any other value. For instance, if you are looking at pizza orders and someone’s order comes to £0.00 for whatever reason (saved a lot of coupons, sleeping with the manager, etc.) then the OrderValue column should be set to 0.00. If, however, the order value is not known (maybe the system failed that day and orders are being input by hand) the OrderValue column should be set to NULL.
When you come to query this table, you would treat the 0 and NULL differently, if you want a total of sales values for the month you would include the 0 in your total, but the NULL makes that total unknowable. SQL Server recognizes this, which is why a SUM of the total sales values will return a NULL if at least one of the sales values is NULL.

That’s important behavior, but it becomes even more important when looking at WHERE conditions (and HAVING conditions, and CASE statements, and anything else that evaluates an expression). Usually an expression can be evaluates to TRUE or FALSE, so 1 = 1 evaluates to TRUE and 1 = 2 evaluates to FALSE. If one side of the expression is NULL then the expression evaluates to UNKNOWN, because in the case of 1 = NULL, you are asking does an unknown value equal 1 and obviously you don’t know the answer. This holds even if both sides of the expression are NULL, because in that case you are asking if two unknown value equal each other.

This gets really important when you consider that WHERE clauses are only looking for rows where the expressions evaluate to TRUE.

Going back to the pizza orders, if you are filtering for orders where OrderValue = 9.99, any orders with OrderValue of NULL will be excluded (expression evaluates to UNKNOWN). Where people often fall down is that if you are filtering for orders where OrderValue <> 9.99, and orders with OrderValue of NULL will still be excluded. To include these in the filter you would need to filter for orders where OrderValue <> 9.99 OR OrderValue IS NULL.

This may seem like a long-winded explanation for something that’s pretty simple, but what I’ve tried to do in this post is lay out the fundamentals of why NULLs work as they do. Hopefully you can then work out for yourself how NULLs will work in a particular situation.