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
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
