IS NULL 与 <> 1 SQL 位

IS NULL versus <> 1 SQL bit

提问by ché

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

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

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

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

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

此查询没有带回应有的内容：（返回 0 行）

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 Nullvalues.

有人可以帮助解释上面的区别是什么吗？我专门将相关位列更新为 NULL，这不会改变结果。（认为​​“空”和Null值之间可能存在差异。

Thanks in advance for any explanations.

预先感谢您的任何解释。

回答by Bennor McCarthy

The reason <>doesn't work is that SQL treats NULLas unknown - it doesn't know what NULLis supposed to mean, so it evaluates both =and <>on a NULLvalue 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.

之所以<>不起作用是SQL对待NULL未知-它不知道什么NULL是应该的意思，所以它会评估=，并<>在NULL值UNKNOWN（在where子句或连接条件视为假）。有关更多信息，请阅读：为什么 NULL = NULL 在 SQL server 中评估为 false。

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:

如果上面有索引，则使用 ISNULL 函数将意味着无法使用该索引，因此为了确保查询可以使用该索引，只需使用OR：

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

回答by SQLGuru

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.

这应该返回所有 NULL 和 FALSE 记录。

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

没有看到您的表结构和数据，我无法真正评论为什么您的 2 个查询得到不同的结果。

回答by Jonathan Leffler

MSDN says that the BITtype 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.)

MSDN 说BIT类型可以存储值 0、1 或 NULL。（BIT 值为 NULL 的事实必须与位值本身分开存储，因为可以压缩位值，以便将 8 个 BIT 值存储在一个字节中。）

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 = 0evaluates to NULL or UNKNOWN, and so does column <> 0.

请记住，当条件为 TRUE 时，WHERE 子句中的条件会选择一行。对于大多数二元谓词（条件），如果将 NULL 与某个值进行比较，则结果为 NULL 或 UNKNOWN（非 TRUE）。因此，例如，如果列中的值为 NULL，则column = 0计算结果为 NULL 或 UNKNOWN， 也是column <> 0。

Looking at your queries:

查看您的查询：

SELECT * FROM table WHERE bit_column_value <> 1

Where the value in the bit_column_valuecolumn 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.

如果bit_column_value列中的值为 1，则条件为 FALSE，因此不返回该行；如果值为 0，则条件为 TRUE，因此返回该行；如果值为 NULL，则条件也是 NULL 或 UNKNOWN，因此不会返回该行。

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:

根据 SQL 标准，IS [NOT] NULL 谓词和相关的 IS [NOT] {TRUE|FALSE|UNKNOWN} 谓词略有不同。如果测试值为 NULL，则 IS NULL 测试返回 TRUE；否则，它们返回 FALSE（并且永远不会返回 UNKNOWN）。IS [NOT] {TRUE|FALSE|UNKNOWN} 测试是类似的；如果值是指定类型，则返回 TRUE，否则返回 FALSE（不是 UNKNOWN）。例如：

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_valuevalue is NULL (which is separate from both 0 and 1) will be selected?—?not the TRUE, nor the FALSE.

因此，在您的第二个查询中，只会选择bit_column_value值为 NULL（与 0 和 1 分开）的行？-？不是 TRUE，也不是 FALSE。

---

---

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

我正在尝试检索此位列为 NULL 或 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

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.

鉴于上面的真值表，查询 4 ​​将产生您想要的结果 — 主要警告我不确定 MS SQL Server 是否支持 IS [NOT] {TRUE|FALSE|UNKNOWN}。从 MSDN 上的Predicates判断，不支持 IS [NOT] {TRUE|FALSE|UNKNOWN} 谓词（但我可能错过了手册的正确部分）。如果这是正确的，您需要使用查询 2 或 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.)

（当值不是简单的列而是行值时，这些谓词会有一些额外的复杂性。但是，这与您的问题或问题无关；双重不是因为 MS SQL Server 似乎不支持它们。）

回答by Iswanto San

Please check your table data, if it's contain value = 0 ?

请检查您的表数据，如果它包含 value = 0 ？

SQL Bit data typecan only have value either 0, 1 or NULL, if you insert other value, it's considered to 1 (Exception : If you insert 'False' it will became 0, 'True' will became 1).

SQL Bit 数据类型的值只能为 0、1 或NULL，如果插入其他值，则将其视为 1（例外：如果插入 ' False' 它将变为 0，' True' 将变为 1）。

For example :

例如 ：

insert into t1 values (1),(2),(1),(3),(-1),(0),(NULL),('false'),('true')

The result :

结果 ：

1, 1, 1, 1, 1, 0, NULL, 0, 1

1, 1, 1, 1, 1, 0, NULL, 0, 1

回答by Mahmoud Gamal

I think this is because that all the data have NULLvalues in this column. So:

我认为这是因为所有数据NULL在此列中都有值。所以：

Select * 
from table 
where bit_column_value <> 1;

Won't give you the result. Since NULLis unknown. And this:

不会给你结果。既然NULL不详。和这个：

Select * 
from table 
where bit_column_value IS NULL;

Will give you the result you are looking for.

会给你你正在寻找的结果。

But you have a misconception of representing trueand falseusing the bitdata type.

但是，你有代表的一个误解真和假使用的bit数据类型。

You are representing falseas NULL, 0is empty and trueis any other value. The bitdata types works as @IswantoSan explained in his answer; It should be 0 or 1 or NULL:

您将false表示为NULL,0为空，true是任何其他值。该bit数据类型可以作为@IswantoSan在他的回答解释; 它应该是 0 或 1 或NULL：

• 0 is false,
• 1 is true,
• NULLis empty.

• 0 是假的，
• 1 是真的，
• NULL是空的。

Therefore to get:

因此得到：

• truevalues use the where bit_column_value = 1.
• falsevalues use the where bit_column_value = 0.
• NULLor empty where bit_column_value IS NULL.
• NULL or not true:where bit_column_value IS NULL or bit_column_value = 0`.

• true值使用where bit_column_value = 1.
• false值使用where bit_column_value = 0.
• NULL或空where bit_column_value IS NULL。
• NULL or not true:其中 bit_column_value 为 NULL 或 bit_column_value = 0`。

The other thing to note is that NULLand empty are two different things, they are not the same. In case of the BITdata type empty is NULLnot 0, because 0 is supposed to be false. But consider a string data type like VARCHARfor example then the empty string ''is totally different from the NULLvalue.

另外要注意的是，NULL和空是两个不同的东西，它们是不一样的。在BIT数据类型的情况下，空NULL不是 0，因为 0 应该是假的。但是考虑一个字符串数据类型VARCHAR，例如那么空字符串''与NULL值完全不同。