Remember != does not return nulls

Author: Steven Neiland
Published:

I'm just trying to get back into the swing of blogging right now but I saw this mistake the other day in some sql I was migrating so I figured it was worth a note for any new developers out there.

Imagine you have a table with the following values:

Idvalue
11
220
3NULL
45

You can create this same data yourself using this script:

DECLARE @data AS TABLE 
(Id INT NOT NULL, value INT NULL)

INSERT INTO @data ( Id, value ) VALUES
(1,1)
,(2,20)
,(3,NULL)
,(4,5)

Now if I asked you to return all records where the value column is not 1 you would assume you could run this sql.

select Id, value 
from @data
where value != 1

You would get this back, note how we are not getting back the third record:

Idvalue
220
45

The reason for this is that NULL is considered by databases as an unknown value as opposed to not existing when queried. To get around this you need to explicitly test for the NULL condition as well. So in this case the query we need is:

select Id, value 
from @data
where value != 1
or value is null

Which returns:

Idvalue
220
3NULL
45

Its a simple mistake to make and one of many reasons why I try to avoid defining nullable columns in my databases where possible, but sometimes you don't have that option.

What Do You Think?

Reader Comments

Comment Etiquette:

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.
*
*



Archives Blog Listing