A database concept used to represent missing, unknown or inapplicable data.
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.
NULL
s 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.