Is that bug in SQL server or I don't know something?
I have table "Items" of structure:
Id - (PRIMARY) int Auto Increment
FinalStatus - tinyint (NULLABLE)
With data it looks like:
| Id | Status |
|---|---|
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | 1 |
| 5 | 2 |
I'm trying to list all records that their Status is different than 2:
SELECT * FROM [Items] WHERE [Status] != '2'
SELECT * FROM [Items] WHERE [Status] != 2
SELECT * FROM [Items] WHERE [Status] NOT LIKE '2'
SELECT * FROM [Items] WHERE [Status] NOT IN (2)
All these queries returns only one row:
| Id | Status |
|---|---|
| 4 | 1 |
However if I use query:
SELECT * FROM [Items] WHERE [Status] IS NULL
In that case I get all NULL rows displayed - but only them.
Since NULL is not the same as 2 or any other number, I feel confused with the result of the queries. However there is difference how I designed this table and all previous. This time I've forgot to allow nulls on creation, and I've used SQL studio to do it afterwards. IMHO this is the reason of the trouble, but I have no idea what exactly it can be. I could delete the table and recreate it, but would like to learn something. Thanks for all kind help.
Is that bug in SQL server or I don't know something
Well, it's not a bug.
Think of NULL as a placeholder for "Unknown" and it will be clearer.
If I ask you to find me all the rows where the value is not 2 then you cannot return any NULL (unknown) value since you do not know that it is NOT 2.
If you want to include NULLs then the criteria should be
where value != 2 or value is null;