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:
| Id | value |
|---|---|
| 1 | 1 |
| 2 | 20 |
| 3 | NULL |
| 4 | 5 |
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:
| Id | value |
|---|---|
| 2 | 20 |
| 4 | 5 |
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:
| Id | value |
|---|---|
| 2 | 20 |
| 3 | NULL |
| 4 | 5 |
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.