andre andre - 4 months ago 33
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.


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


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

Read more about null comparaison in Stackoverflow Documentation