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 as opposed to
field = NULL.
NULL handling can be contentious as some believe
NULL has no place in a database, and others use it for business logic.
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.