CASE WHEN NULL does the wrong result in SQLite?

Question

I have a table with an image type column, there are several rows in the table, but all rows do not yet have an image, all of them are zero. To test CASE WHEN NULL, I tried this and it gave a weird result:

SELECT CASE myImageColumn WHEN NULL THEN 0 ELSE 1 END FROM myTable 

All returned rows were in column 1 (I thought 0). What is wrong here?

Your help will be greatly appreciated!

Thanks!

Answer 1

You cannot compare with NULL like this, you should try:

 SELECT CASE WHEN myImageColumn IS NULL THEN 0 ELSE 1 END FROM myTable 

Answer 2

Instead, use CASE different form:

 SELECT CASE WHEN myImageColumn IS NULL THEN 0 ELSE 1 END FROM myTable 

Two useful links:

• http://www.sqlite.org/nulls.html
• http://www.sqlite.org/lang_expr.html

Answer 3

There is a bypass here:

 CASE ifnull(myValue, 'someUniqueStringOrValue') WHEN 'someUniqueStringOrValue' THEN 0 -- this means null WHEN 'someNormalValue' THEN 1 END