Solution: Use NULL as a Unique Value

Most problems with NULL values are based on a common misunderstanding of the behavior of SQL’s three-valued logic. This can be a challenge for programmers accustomed to the conventional true/false logic implemented in most other languages. But we can easily handle NULL values in SQL queries after a little study of how they work.

NULL in scalar expressions

Let’s suppose Stan is thirty years old while Oliver’s age is unknown. If you were asked whether Stan is older than Oliver, your only possible answer would be “I don’t know.” If you were asked whether Stan is the same age as Oliver, your answer would still be “I don’t know.” And if you were asked to tell the sum of Stan and Oliver’s ages, your answer would be just the same: “I don’t know.”

Are NULLs Rational?AreNULLsRational

Now, let’s suppose that Charlie’s age is also unknown. If you were asked whether Oliver’s age is equal to Charlie’s age, your answer would still be “I don’t know.” This shows why the result of a comparison like NULL = NULL is also NULL.

The following table describes some cases where programmers expect one result but get something different.