SQLite handling of NULL

Question

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

Answer 1

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