Microsoft SQL Server 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.