In a previous article Oracle conditions and how they handle NULL I give some examples on how NULL is evaluated in Oracle. After talking with Scott, a student who is doing some database work with me this semester I believe I may have a better way of explaining the initially cryptic evaluation of NULL.
NULL in Oracle is essentially considered a non-answer. For example let’s consider this yes/no question:
Are you currently living in the USA?
There are two obvious answers to this question, Yes and No, but we need to be prepared for one more circumstance, a non-answer.
So if you ask me this question and I don’t answer it can you say “Jon is currently living in the USA”? No. Can you say “Jon is not currently living in the USA”? No! We can’t compare against something we don’t know so any comparisons against NULL are treated as false.
To handle these circumstances in Oracle we must use
IS NULL and
IS NOT NULL to detect these non-answer values. For some examples of this code and more detail on this check out my original article on the topic.