andre andre - 1 year ago 76
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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download