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