IS NULL 与 <>1 个 SQL 位 [英] IS NULL versus <> 1 SQL bit

问题描述

我在 SQL Server 2012 数据库的表上有一个位列.

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

我正在尝试检索此位列为 NULL 或 NOT TRUE 的所有行.

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

此查询没有返回它应该返回的内容:(返回 0 行)

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

Select * 
from table 
where bit_column_value <> 1

此查询返回正确的行:

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!

有人可以帮忙解释一下上面的区别是什么吗?我专门将相关位列更新为 NULL，这不会改变结果.(认为​​Empty"和 Null 值之间可能存在差异.

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.

推荐答案

<> 不起作用的原因是 SQL 将 NULL 视为未知 - 它不知道 NULL 应该是什么意思，所以它对 NULL 和 <> 进行评估code> 值作为 UNKNOWN (在 where 子句或连接条件中被视为 false).有关更多信息，请阅读:为什么 NULL = NULL在 SQL 服务器中评估为 false.

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.

如果上面有索引，使用ISNULL函数就意味着索引不能用了，所以要确保查询可以使用索引只需使用 OR:

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

这篇关于IS NULL 与 <>1 个 SQL 位的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！