SQLite handling with NULL

Question

I have a SQL query like

SELECT * FROM tableName WHERE colName != 'contents' 

now contrary to what I expected, it is NOT like colName, where its contents are "NULL". Looking at how SQLite handles NULL, and in particular, this condition "null OR true" is true is true for SqlLite. I thought my query would be sufficient to select strings with NULL for colName . I must have misunderstood it. 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 didn’t think I would have to.

thanks

Answer 1

You can work around this problem by using IS NOT rather than != . If you look at the operators section of this document , you will see:

The IS and IS NOT operators work like = and! = Unless one or both 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. The IS and operators do not have the same priority as =.

I myself experienced this:

 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> 

It looks like the != Operator will evaluate to NULL when you use it to compare with NULL :

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

Presumably that is why you are not getting the expected behavior - you are effectively saying WHERE NULL != 'contents' , which evaluates to NULL and does not satisfy the WHERE clause