andre andre - 10 days ago 5
SQL Question

Querying a null boolean field

When querying a boolean field that is null why does

Select * From MyTableName where [boolfieldX] <> 1


not return any rows with null in [boolfieldX]? 1 <> null I would have expected rows with null to be returned.

Answer

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

You have to use the IS NULL and IS NOT NULL operators instead, or you have to use functions like ISNULL() and COLEASCE()

Select * From MyTableName where [boolfieldX] <> 1 OR [boolfieldX] IS NULL

OR

Select * From MyTableName where ISNULL([boolfieldX],0) <> 1

Read more about null comparaison in Stackoverflow Documentation

Comments