SQL: Why are NULL values filtered out within this where clause?

Question

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?

Answer 1

Lots of good answers, but let me give you a really concise version.

To SQL, Null does NOT mean "No value" it means "Unknown Value"

With that in mind, consider the answer to the question you are asking SQL in plain English.

Q: Is this unknown value not equal to 1? 
A: I don't know, there is no way to tell without knowing the value.

Hence Null<>1 = Null

Answer 2

From the Wikipedia entry on NULL:

For example, a WHERE clause or conditional statement might compare a column's value with a constant. It is often incorrectly assumed that a missing value would be "less than" or "not equal to" a constant if that field contains Null, but, in fact, such expressions return Unknown. An example is below:

-- Rows where num is NULL will not be returned,
-- contrary to many users' expectations.
SELECT * FROM sometable WHERE num <> 1;   
             

Basically, any comparison between NULL and something else, whether it's with = or <> will not be true.

As another reference, the MSDN T-SQL page on <> states:

Compares two expressions (a comparison operator). When you compare nonnull expressions, the result is TRUE if the left operand is not equal to the right operand; otherwise, the result is FALSE. If either or both operands are NULL, see SET ANSI_NULLS (Transact-SQL).

The SET ANSI_NULLS page then states:

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

...

When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN. When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL.

Answer 3

It's not a bug.

NULL is not equal to anything, not even NULL (NULL = NULL returns FALSE).

Typically NULL values aren't indexed either. It's generally a bad idea to rely on a particular value or NULL. Depending on what you're storing in the column, you may be better off putting a dummy or sentinel value in rather than using NULL to indicate some meaning.