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.
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.
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.