Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Advice from Aunt Kathi

Kathi Kellenberger is a Sr. Consultant with Pragmatic Works. She is an author, speaker and trainer.

Something About Nothing: NULL

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

 

 

 

 

 

 

 

 

 

 

 

 

 

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.