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 NULL is:

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 NULL.

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.

history | show excerpt | excerpt history