Understanding Null in SQL

Written on Apr 25, 2016

In a closed world logic assumption, the outcome is always deterministic. It's either true or false. Let's say for example, a question "Is 1 is less than 4?" always have answer YES. If we ask question "Is 1 is greater than 4?" answer is always NO. Procedural languages are based on this principle, the outcome is always deterministic there.

But we live in open world. There is always possibility of "unknown". If there's a question "Who will win election, Democratic or Republican?" we can't surely tell answer, because it's unknown. Similarly if we ask "Is X is less than 4?" we can't say YES or NO, because we don't know value of X.

In open world logic, possible outcome of any problem is "TRUE", "FALSE" and "Unknown". This concept is called three-valued logic. Logical outcome in SQL Server is based on 3-Valued logic. SQL Server is indicating unknown values using "NULL". As value of NULL is unknown, any operation with NULL will always result in NULL in SQL Server. Let's analyze below query and its outcome.


Here, we are making combination of all TRUE, FALSE and Unknown and making AND, OR and NOT operations. As we have discussed earlier, any operation with NULL is resulting in NULL outcome.


Work with NULL in where condition

We can't use "=" or "<>" operator in to compare values with null. In order to work with NULL, we have to use special operators "IS NULL" or "IS NOT NULL". Let's think of an example, where we want to get all count of Addresses with field "Address2" empty and not empty. If we wrote query to compare NULL with "=" and "<>" it won't fetch us any result.


Now' if we use "IS NULL" and "IS NOT NULL" operators, we'll get result.


A Patch

If we're desperate to use out regular operators "=" and "<>" with NULL, then there is a patch. We can set "ANSI_NULLS" to "OFF", and our condition will work. Take a look.


It worked! But it's not suggested to use this way. Here're the reasons.

  1. There are many SQL Server features like computed columns, indexed views and XML indexes require "SET ANSI_NULLS ON" at creation time.
  2. Mixing and matching these options can confuse other developers.
  3. Microsoft has deprecated "SET ANSI_NULLS OFF" setting. It will be removed in future versions of SQL Server. So it's wiser to not use this option.