SELECT * FROM tableName WHERE colName != 'contents'
现在与我的预期相反,这似乎与其内容为“NULL”的colName不匹配.看看how SQLite handles NULLs并且特别是这个条件“null OR true”对于sqlLite是正确的我认为我的查询足以为colName选择NULL为NULL的行.我必须错误地理解这一点.任何人都可以为我阐明这一点吗？我可以用
SELECT * FROM tableName WHERE (colName != 'contents' OR colName IS NULL)
但我不认为我必须这样做.
谢谢
解决方法
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子句
