In my table, I have a nullable bit column (legacy system...) and another developer recently made a change to a stored procedure to only show values where the bit column was not true (1). Because this is a nullable column, we noticed that if the column was NULL, the record was not being picked up. WHY is this?
Both the other developer and I agree that NULL <> 1... Is this a bug in SQL or was this designed this way? Seems like a design flaw.
Current Code:
(VoidedIndicator <> 1)
Proposed Fix:
(VoidedIndicator <> 1 OR VoidedIndicator IS NULL)
Clarification (By Jon Erickson)
VoidedIndicator is a nullable bit field so it can have the following values: NULL, 0, or 1
When a SQL statement is created with a where clause such as (VoidedIndicator <> 1) we only get records returned that have VoidedIndicator == 0, but we were expecting both VoidedIndicator == 0 and VoidedIndicator IS NULL. Why is this?