You wouldn’t think that “nothing” would cause so many problems. But seriously, understanding how to correctly use NULLs is a very important part of learning T-SQL.

You must remember these two rules: anything, even NULL, compared to NULL is False and if you add anything to NULL you will get NULL.

Here is an example of the first rule:

CREATE TABLE test_nulls (colA INTEGER NULL)

GO

INSERT INTO test_nulls select 1

INSERT INTO test_nulls select 2

INSERT INTO test_nulls select 3

INSERT INTO test_nulls select NULL

-- returns no rows at all

SELECT colA

FROM test_nulls

WHERE colA = NULL

-- returns only 2 rows

SELECT colA

FROM test_nulls

WHERE colA <> 1

The exception to this is if you have used the SET ANSI_NULLS OFF  statement before running the queries. Don’t do this, because it’s not the standard and it's only going to cause more problems for you later.

To get the results you expect, use these queries:

--returns 1 row

SELECT colA

FROM test_nulls

WHERE colA IS NULL

-- returns 3 rows

SELECT colA

FROM test_nulls

WHERE colA <> 1 OR colA IS NULL

Here is an example of the second rule:

DECLARE @test INT

SET @test = @test + 5

--nothing will print

PRINT @test

Because @test has not been initialized, it is NULL. Adding 5 to NULL gives you NULL. Visual Basic programmers especially need to keep this in mind since they may not be used to initializing variables like C++ and C# programmers do.

Be sure to initialize the variable.

DECLARE @test INT

SET @test = 0

SET @test = @test + 5

-- 5 will print

PRINT @test