In the SQL query languages of relational databases, NULL is a special value for "unknown". Use that tag for questions concerning the SQL NULL value. Please also use the "ansi-sql" tag if your question is about the SQL standard or a tag that indicates which database you are using.
In ansi-sql, “NULL” stands for the “unknown” value.
In a boolean-expression, it has a special three-valued-logic, so that the following holds:
NULL AND Xis the same asNULLNULL OR Xis the same asXNOT NULLis the same asNULL
Also, most sql-functions and operators, when operating on a NULL (unknown) argument, will produce a NULL result.
That holds for the equality-operator in particular, so the expression X = NULL will not produce TRUE or FALSE, but NULL.
Use x IS NULL to test for NULL-ness and X IS NOT DISTINCT FROM Y to test if X and Y are either both NULL or both not NULL and equal.
oracle database deviates from the SQL standard by treating empty strings as NULL, so don't rely on that if you want to write portable SQL.
Two guidelines for proper use of NULL:
Define your database columns as
NOT NULL(which is not the default) wherever possible.This improves the quality of your data and makes your queries simpler and consequently faster, since they don't have to deal with the oddities of
NULL.It is trivial to change a column from non-nullable to nullable, but not vice versa!
Use
NULLfor unknown values, not for values that are known to be absent, infinite values and the like. That will make your SQL intuitively do the right thing.For example, a missing comment had better be an empty string than a
NULL, so that string operations work as expected.Infinite values are better represented by
infinity(if your SQL dialect supports that) or values beyond the normal range, so that comparisons have the intended result.
