IS NULL versus <> 1 SQL bit

Question

I have a bit column on a table in a SQL Server 2012 database.

I am trying to retrieve all the rows where this bit column is either NULL or NOT TRUE.

This query does not bring back what it should: (returns 0 rows)

Select * 
from table 
where bit_column_value <> 1

This query brings back the correct rows:

Select * 
from table 
where bit_column_value IS NULL

Now, I'd be happy to use the second query, but my issue is that, in a similar query for another table, the reverse of the above is true, where the first way works, but the second way does not!

Could someone assist in explaining what the difference is in the above? I have specifically updated the relevant bit columns to be NULL and this does not change the results. (Thought maybe there was a difference between "Empty" and Null values.

Thanks in advance for any explanations.

Answer 1

The reason <> doesn't work is that SQL treats NULL as unknown - it doesn't know what NULL is supposed to mean, so it evaluates both = and <> on a NULL value as UNKNOWN (which is treated as false in a where clause or join condition). For more info, read this: Why does NULL = NULL evaluate to false in SQL server.

If there's an index on it, using the ISNULL function will mean the index can't be used, so to ensure the query can use the index just use OR:

SELECT * 
FROM TableName
WHERE
   bit_column_value IS NULL OR bit_column_value = 0

Answer 2

your best bet would be to write the query as such:

SELECT
     * 
FROM 
     table 
WHERE 
     ISNULL(bit_column_value, 0) = 0

This should return all the NULL and FALSE records.

Without seeing your table structure and data, I cannot really comment on why you are getting different results from your 2 queries.

Answer 3

MSDN says that the BIT type can store values 0, 1 or NULL. (The fact that a BIT value is NULL must be stored separately from the bit value itself, since the bit values can be compressed so that 8 BIT values are stored in a byte.)

Remember that a condition in a WHERE clause selects a row when the condition is TRUE. For most binary predicates (conditions), if you compare NULL with some value, the result is NULL or UNKNOWN (not TRUE). So, for example, if the value in a column is NULL, then column = 0 evaluates to NULL or UNKNOWN, and so does column <> 0.

Looking at your queries:

SELECT * FROM table WHERE bit_column_value <> 1

Where the value in the bit_column_value column is 1, the condition is FALSE so the row is not returned; where the value is 0, the condition is TRUE so the row is returned; and where the value is NULL, the condition is also NULL or UNKNOWN so the row is not returned.

SELECT * FROM table WHERE bit_column_value IS NULL

According to the SQL standard, the IS [NOT] NULL predicate, and the related IS [NOT] {TRUE|FALSE|UNKNOWN} predicates, are slightly different. The IS NULL test returns TRUE if the tested value is NULL; otherwise, they return FALSE (and never return UNKNOWN). The IS [NOT] {TRUE|FALSE|UNKNOWN} tests are similar; they return TRUE if the value is of the specified type and FALSE otherwise (not UNKNOWN). For example:

Column   IS TRUE   IS FALSE   IS UNKNOWN   IS NOT TRUE   IS NOT FALSE   IS NOT UNKNOWN
FALSE    FALSE     TRUE       FALSE        TRUE          FALSE          TRUE
TRUE     TRUE      FALSE      FALSE        FALSE         TRUE           TRUE
NULL     FALSE     FALSE      TRUE         TRUE          TRUE           FALSE

So, in your second query, only the rows where the bit_column_value value is NULL (which is separate from both 0 and 1) will be selected — not the TRUE, nor the FALSE.

---

I am trying to retrieve all the rows where this bit column is either NULL or NOT TRUE.

Try writing the query directly from your specification:

1. SELECT * FROM table WHERE bit_column_value IS NULL OR bit_column_value IS NOT TRUE
2. SELECT * FROM table WHERE bit_column_value IS NULL OR bit_column_value = FALSE
3. SELECT * FROM table WHERE bit_column_value IS NULL OR bit_column_value <> TRUE
4. SELECT * FROM table WHERE bit_column_value IS NOT TRUE

Given the truth table above, query 4 would yield the result you want — with the major caveat that I'm not certain that MS SQL Server supports the IS [NOT] {TRUE|FALSE|UNKNOWN}. Judging from MSDN on Predicates, the IS [NOT] {TRUE|FALSE|UNKNOWN} predicates are not supported (but I might have missed the correct part of the manual). If that's correct, you need to use one of query 2 or 3.

---

(There are some extra complications with these predicates when the value is not a simple column but is a row value. However, that's not relevant to your question or problem; doubly not since MS SQL Server does not seem to support them.)