SQLite 对 NULL 的处理 [英] SQLite handling of NULL

问题描述

我有一个类似的 SQL 查询

SELECT * FROM tableName WHERE colName != 'contents'

现在与我的预期相反，这似乎与内容为NULL"的 colName 不匹配.查看 SQLite 如何处理 NULL，特别是这种情况 "null OR true" 为真 对 SqlLite 来说是正确的 我本以为我的查​​询足以为 colName 选择带有 NULL 的行.我一定是错误地理解了这一点.任何人都可以为我解释一下吗?我可以用

SELECT * FROM tableName WHERE (colName != 'contents' OR colName IS NULL)

但我不认为我必须这样做.

谢谢

解决方案

您可以使用 IS NOT 而不是 != 来解决这个问题.如果您查看本文档的运算符部分，您会看到:

<块引用>

IS 和 IS NOT 运算符的工作方式与 = 和 != 类似，除非其中一个或两个操作数为 NULL.在这种情况下，如果两个操作数都为 NULL，则 IS 运算符的计算结果为 1(真)，IS NOT 运算符的计算结果为 0(假).如果一个操作数为 NULL 而另一个不是，则 IS 运算符的计算结果为 0(假)，IS NOT 运算符的计算结果为 1(真).IS 或 IS NOT 表达式不可能计算为 NULL. 运算符 IS 和 IS NOT 具有与 = 相同的优先级.

我自己测试过:

sqlite>创建表临时(数据列，空列)；sqlite>插入 temp( dataColumn, nullColumn ) 值 ( 'test', NULL );sqlite>select * from temp where nullColumn != 'test';sqlite>select * from temp where nullColumn IS NOT 'test';测试|sqlite>select * from temp where dataColumn IS NOT 'test';sqlite>

多玩一点，似乎 != 运算符 将 评估为 NULL 当您使用它与某些内容进行比较时NULL:

sqlite>选择('内容'！= NULL)；sqlite>选择 ('内容' == NULL);sqlite>选择(内容"为空)；0sqlite>选择(内容"不为空)；1

大概这就是为什么你没有得到你期望的行为 - 你实际上是在说 WHERE NULL != 'contents' 其计算结果为 NULL，并且不满足 WHERE 子句

I have an SQL query like

SELECT * FROM tableName WHERE colName != 'contents'

now contrary to what I expected this does NOT seem to match a colName where its contents is 'NULL'. Looking at how SQLite handles NULLs and specifially this condition "null OR true" is true being true for SqlLite I wouldve thought my query wouldve been sufficent to select the rows with NULL for colName. I mustve understood this incorrectly. Can anyone shed some light on this for me? I can just use

SELECT * FROM tableName WHERE (colName != 'contents' OR colName IS NULL)

but i didnt think i would have to.

Thanks

解决方案

You can get around this issue by using IS NOT rather than !=. If you look at the operators section of this document you'll see:

The IS and IS NOT operators work like = and != except when one or both of the operands are NULL. In this case, if both operands are NULL, then the IS operator evaluates to 1 (true) and the IS NOT operator evaluates to 0 (false). If one operand is NULL and the other is not, then the IS operator evaluates to 0 (false) and the IS NOT operator is 1 (true). It is not possible for an IS or IS NOT expression to evaluate to NULL. Operators IS and IS NOT have the same precedence as =.

I tested this out myself:

sqlite> create table temp( dataColumn, nullColumn );
sqlite> insert into temp( dataColumn, nullColumn ) values ( 'test', NULL );
sqlite> select * from temp where nullColumn != 'test';
sqlite> select * from temp where nullColumn IS NOT 'test';
test|
sqlite> select * from temp where dataColumn IS NOT 'test';
sqlite>

From a bit more playing about, it seems that the != operator will evaluate to NULL when you use it to compare something with NULL:

sqlite> select ( 'contents' != NULL );

sqlite> select ( 'contents' == NULL );

sqlite> select ( 'contents' IS NULL );
0
sqlite> select ( 'contents' IS NOT NULL );
1

Presumably this is why you're not getting the behaviour you expect - you are effectively saying WHERE NULL != 'contents' which evaluates to NULL, and doesn't satisfy the WHERE clause

这篇关于SQLite 对 NULL 的处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！