In general, the result of comparing null values is UNKNOWN, since it is not possible
to determine whether NULL is equal (or not equal) to a given value or to another
NULL.
However, the following cases return TRUE when
expression is any column,
variable or literal, or combination of these, which evaluates as NULL:
-
expression
is null
-
expression = null
-
expression = @x, where @x is
a variable or parameter containing NULL. This exception facilitates writing stored
procedures with null default parameters.
-
expression != n, where n is
a literal that does not contain NULL, and expression evaluates to
NULL.
The negative versions of these expressions return TRUE when the expression does not
evaluate to NULL:
-
expression
is not null
-
expression != null
-
expression != @x
Note:
The far right side of these exceptions is a literal null, or a variable or parameter
containing NULL. If the far right side of the comparison is an expression (such as
@nullvar + 1), the entire expression evaluates to NULL.
Following these rules, null column values do not join with other null column values.
Comparing null column values to other null column values in a
where
clause always returns UNKNOWN for null values, regardless of the comparison operator, and
the rows are not included in the results. For example, this query returns no result rows
where column1 contains NULL in both tables (although it may return other
rows):
select column1
from table1, table2
where table1.column1 = table2.column1