A value of NULL indicates the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.
Null values usually indicate data that is unknown, not applicable, or to be added at a later time. For example, a customer's middle initial may not be known at the time the customer places an order.
Here is some information about nulls:
In program code, you can check for null values so that certain calculations are performed only on rows with valid (or not NULL) data. For example, a report can print the social security column only if there is data that is not NULL in the column. Eliminating null values when performing calculations can be important because certain calculations (such as an average) can be inaccurate if NULL columns are included.
If it is possible that null values may be stored in your data, it is a good idea to create queries and data-modification statements that either eliminate NULLs or transform NULLs into some other value (if you do not want null values appearing in your data).
Important To minimize maintenance and possible effects on existing queries or reports, it is recommended that you minimize the use of null values. Plan your queries and data-modification statements so that null values have minimal effect.
When null values are present in data, logical and comparison operators can potentially return a third result of UNKNOWN instead of just TRUE or FALSE. This need for three-valued logic is a source of many application errors. These tables outline the effect of introducing null comparisons.
AND with value | TRUE | UNKNOWN | FALSE |
---|---|---|---|
TRUE | TRUE | UNKNOWN | FALSE |
UNKNOWN | UNKNOWN | UNKNOWN | FALSE |
FALSE | FALSE | FALSE | FALSE |
OR with value | TRUE | UNKNOWN | FALSE |
---|---|---|---|
TRUE | TRUE | TRUE | TRUE |
UNKNOWN | TRUE | UNKNOWN | UNKNOWN |
FALSE | TRUE | UNKNOWN | FALSE |
NOT | Evaluates to |
---|---|
TRUE | FALSE |
UNKNOWN | UNKNOWN |
FALSE | TRUE |
The SQL-92 standard introduces the keywords IS NULL and IS NOT NULL to test for the presence of null values.
IS NULL | Evaluates to | IS NOT NULL | Evaluates to |
---|---|---|---|
TRUE | FALSE | TRUE | TRUE |
NULL | TRUE | NULL | FALSE |
FALSE | FALSE | FALSE | TRUE |
Transact-SQL also offers an extension for null processing. If the option ANSI_NULLS is set off, then comparisons between nulls, such as NULL = NULL, evaluate to TRUE. Comparisons between NULL and any data value evaluate to FALSE.
AND | NOT |
CREATE TABLE | OR |
Allowing Null Values | Modifying Column Properties |
ISNULL | WHERE |