Post date: Apr 04, 2011 1:41:10 PM
Many folks think of NULL as being the same as zero or the empty string (''). Not only is this not true, NULL is not even the same as NULL.
NULL really means, "The value is unknown". Consider the Middle Name field for two contacts:
Contact A: John NULL Doe
Contact B: James NULL Doe
The answers to these questions are obvious:
Q: Do Contact A and Contact B have the same middle name?
A: we don't know
Q: Do Contact A and Contact B have different middle names?
A: we don't know
Translate this into SQL, the results may seem odd, unless you consider the real-world questions above:
Q: IF NULL = NULL SELECT 'True' ELSE SELECT 'False'
A: False
Q: IF NULL != NULL SELECT 'True' ELSE SELECT 'False'
A: False
How can using equals and not equals BOTH be false? Well, comparing unknown to anything is, well, unknown. If the answer is unknown, but you require SQL to give you a true or false answer, SQL will return false.
If you want to consider whether something is actually unknown, use IS NULL:
Q: IF NULL IS NULL SELECT 'True' ELSE SELECT 'False'
A: True
It is possible to override this behavior in SQL, but it is generally a very bad idea.