NULLs If a column in a row has no value, then the column is said to be NULL, or to contain a NULL. Use a NULL when the actual value is not known or when a value would not be meaningful.
Do not use NULL to represent a value of zero, because they are not equivalent. Any arithmetic expression containing a NULL always evaluates to NULL. For example, NULL added to 10 is NULL. In fact, all operators (except concatenation) return NULL when given a NULL operand.
Most aggregate functions ignore NULLs. For example, a query that averages the five values 1000, NULL, NULL, NULL, and 2000, ignores the NULLs and calculates the average to be (1000+2000)/2 = 1500.
NULLs with Comparison OperatorsTo test for NULLs, use only the comparison operators IS NULL and IS NOT NULL. If you use any other operator with NULLs, the result is UNKNOWN. Because NULL represents a lack of data, a NULL cannot be equal or unequal to any value or to another NULL.
| If a is: | Condition | Evaluates to: |
|---|---|---|
| 10 | a IS NULL | FALSE |
| 10 | a IS NOT NULL | TRUE |
| NULL | a IS NULL | TRUE |
| NULL | a IS NOT NULL | FALSE |
| 10 | a = NULL | UNKNOWN |
| 10 | a != NULL | UNKNOWN |
| NULL | a = NULL | UNKNOWN |
| NULL | a != NULL | UNKNOWN |
| NULL | a = 10 | UNKNOWN |
| NULL | a != 10 | UNKNOWN |
NULLs in Conditions A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows. However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus, NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN.
The table shows examples of various evaluations involving NULLs in conditions. If the conditions evaluating to UNKNOWN were used in a WHERE clause of a SELECT statement, then no rows would be returned for that query.