Microsoft SQL Server NULLs NULL comparison
Example
NULL is a special case when it comes to comparisons.
Assume the following data.
id someVal
----
0 NULL
1 1
2 2
With a query:
SELECT id
FROM table
WHERE someVal = 1
would return id 1
SELECT id
FROM table
WHERE someVal <> 1
would return id 2
SELECT id
FROM table
WHERE someVal IS NULL
would return id 0
SELECT id
FROM table
WHERE someVal IS NOT NULL
would return both ids 1 and 2.
If you wanted NULLs to be "counted" as values in a =, <> comparison, it must first be converted to a countable data type:
SELECT id
FROM table
WHERE ISNULL(someVal, -1) <> 1
OR
SELECT id
FROM table
WHERE someVal IS NULL OR someVal <> 1
returns 0 and 2
Or you can change your ANSI Null setting.
