NULL is a concept in SQL databases (exact implementation can vary) that is used to represent unknown, missing or inapplicable data. Normally it is considered to be a state of the data as opposed to the value of the data.
Ordinary equivalence comparisons do not work with
NULL for the above reason. A
field IS NULL (correct) as opposed to
field = NULL (not correct).
NULL handling can be contentious as some believe
NULL has no place in a database, and others use it for business logic. An important distinction is that
NULL row values are not considered for most aggregate functions such as
SUM(col_name), and correct results are obtained if
NULL values are used; incorrect results are obtained if "magic" values are used to indicate no data. On some database products, the optimizer can perform better if
NULL values are used correctly.
NULLs can also be dangerous and lead to unexpected results when used with the
NOT IN operator.
A good analogy for the meaning of
You are at a party with some people you know and some others you don't.
There are 3 men that you know and 4 men that are strangers. To you at that time, those 4 men have a name of
NULL - you know they have names, but you do not know what they are.
If you were asked "Is that person Bill?" the answer would be
I don't know - which is what most RDBMS will return as well when asked about a value being equal to
However, you would not be able to say "His name is not Bill or Jim", which is why
NULL will not return
TRUE (which in this case means "no match") when used with a
NOT IN comparison.