Null Values
A value of NULL indicates that 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 generally indicate data that is unknown, not applicable, or that the data will be added later. For example, a customer's middle initial may not be known at the time the customer places an order.
Following is information about nulls:
To test for null values in a query, use IS NULL or IS NOT NULL in the WHERE clause.
When query results are viewed in SQL Server Management Studio Code editor, null values are shown as NULL in the result set.
Null values can be inserted into a column by explicitly stating NULL in an INSERT or UPDATE statement, by leaving a column out of an INSERT statement, or when adding a new column to an existing table by using the ALTER TABLE statement.
Null values cannot be used for information that is required to distinguish one row in a table from another row in a table, such as primary keys.
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. Removing null values when you are performing calculations can be important, because certain calculations, such as an average, can be inaccurate if NULL columns are included.
If it is likely that null values are stored in your data and you do not want null values appearing in your data, you should create queries and data-modification statements that either remove NULLs or transform them into some other value.
Important
To minimize maintenance and possible effects on existing queries or reports, you should 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.
The following table shows the results of applying an AND operator to two Boolean operands.
AND |
TRUE |
UNKNOWN |
FALSE |
|---|---|---|---|
TRUE |
TRUE |
UNKNOWN |
FALSE |
UNKNOWN |
UNKNOWN |
UNKNOWN |
FALSE |
FALSE |
FALSE |
FALSE |
FALSE |
The following table shows the results of applying an OR operator to two Boolean operands.
OR |
TRUE |
UNKNOWN |
FALSE |
|---|---|---|---|
TRUE |
TRUE |
TRUE |
TRUE |
UNKNOWN |
TRUE |
UNKNOWN |
UNKNOWN |
FALSE |
TRUE |
UNKNOWN |
FALSE |
The following table shows how the NOT operator negates, or reverses, the result of a Boolean operator.
Boolean expression to which the NOT operator is applied |
Evaluates to |
|---|---|
TRUE |
FALSE |
UNKNOWN |
UNKNOWN |
FALSE |
TRUE |
The ISO standard uses the keywords IS NULL and IS NOT NULL to test for the presence of null values.
Boolean expression to which the IS NULL operator is applied |
Evaluates to |
Boolean expression to which the IS NOT NULL operator is applied |
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 to OFF, comparisons between nulls, such as NULL = NULL, evaluate to TRUE. Comparisons between NULL and any data value evaluate to FALSE.