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!

Solution 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

Solution 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

Solution 3

There's a bypass:

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

Comments

[Mike Christensen]

Because null is unknown, nothing is equal to null.

[King King]

This doesn't work in MS SQL Server, do you know the solution for SQL Server? Thanks!

[King King]

Thank you, you are a little late after I said thanks to Lamak. But give you my +1. Thanks.

[Doo Dah]

Good idea. Thanks.