# 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
from titles
```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 ```