CASE WHEN NULL makes wrong result in SQLite?

Question

I have a table with a column of image type, the table has some rows but all the rows haven't had any image yet, they are all null. To test the CASE WHEN NULL, I've tried this and it gave a strange result:

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

All the returned rows were in a column of 1's (I thought 0's). What is wrong here?

Your help would be highly appreciated!

Thank you!

Answer 1

You can't compare with NULL like that, you should try:

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

Answer 2

Use a different form of CASE instead:

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's a bypass:

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