Variables and null values

Local variables are assigned the value NULL when they are declared, and may be assigned the null value by a select statement. The special meaning of NULL requires that the comparison between null-value variables and other null values follow special rules.

Table 15-2 shows the results of comparisons between null-value columns and null-value expressions using different comparison operators. An expression can be a variable, a literal, or a combination of variables, literals, and arithmetic operators.

**Table 15-2: Comparing null values**
Type of comparison	Using the = operator	Using the <, >, <=, !=, !<, !>, or <> operator
Comparing column_value to column_value	FALSE	FALSE
Comparing column_value to expression	TRUE	FALSE
Comparing expression to column_value	TRUE	FALSE
Comparing expression to expression	TRUE	FALSE

For example, this test:

declare @v int, @i int
if @v = @i select "null = null, true"
if @v > @i select "null > null, true"

shows that only the first comparison returns true:

----------------- 
null = null, true 
 
(1 row affected)

This example returns all the rows from the titles table where the advance has the value NULL:

declare @m money
select title_id, advance
from titles
where advance = @m

title_id advance        
-------- ----------------
MC3026              NULL 
PC9999              NULL

(2 rows affected)