Understanding Null in SQL

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 has the answer YES. If we ask the question “Is 1 is greater than 4?” the answer is always NO. Procedural languages are based on this principle, the outcome is always deterministic there.

But we live in an open world. There is always the possibility of “unknown”. If there’s a question “Who will win the election, Democratic or Republican?” we can’t surely tell the 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 the value of X.

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

Here, we are making the 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 a NULL outcome.

Work with NULL in WHERE condition

We can’t use the “=” or “<>” operator 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 counts of Addresses with the field “Address2” empty and not empty. If we wrote a query to compare NULL with “=” and “<>” it won’t fetch us any result. Take a look at the code here.

Now if we use “IS NULL” and “IS NOT NULL” operators, we’ll get results.

Here’s the code.

A Patch

If we’re desperate to use our 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 that require “SET ANSI_NULLS ON” at creation time.
2. Mixing and matching these options can confuse other developers.
3. Microsoft has deprecated the “SET ANSI_NULLS OFF” setting. It will be removed in future versions of SQL Server. So it’s wiser to not use this option.

   

About the Author

ByteScout Team of Writers ByteScout has a team of professional writers proficient in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.