SQLite 对 NULL 的处理

Question

我有一个像 SQL 查询

SELECT * FROM tableName WHERE colName != 'contents'

现在与我的预期相反，这似乎与内容为“NULL”的 colName 不匹配。看着 how SQLite handles NULLs特别是这个条件 "null OR true" is true对于 SqlLite，我认为我的查询足以选择带有 NULL 的行为 colName .我一定是错误地理解了这一点。任何人都可以为我解释一下吗？我只能用

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

但我不认为我会不得不。

谢谢

Answer 1

您可以使用 IS NOT 解决此问题。而不是 != .如果你看 the operators section of this document你会看到的:

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

我自己测试了这个:

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>

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

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

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

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

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