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.

This table 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.

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)

Using a not in clause when you compare a null value with a null value produces different results, depending on whether the variable or column contains the null values. This table shows the results compares between not null-value columns and null-value expressions using different comparison operators

Type of Comparison	Using the = Operator	Using the <, >, <=, !=, !<, !>, or <> Operator
Comparing `column_value` to `column_value`
Comparing `column_value` to `expression`
Comparing `expression` to `column_value`
Comparing `expression` to `expression`

For example, if you create this table:

create table #test(i int not null, a char(5) null)
insert into #test(i) values (1)

This example returns true because the @a variable contains a null value

declare @a char(5)
select @a = a from #test where i = 1
if @a not in ('NTTRD', 'NTOFF')
print 'true'
else
print 'false'

true

However, this example returns false because the column a contains a null value:

if (select a from #test where i = 1) not in ('NTTRD', 'NTOFF') 
print 'true'
else
print 'false'

false