CODEWITHSUNDEEP
sqldatabasesqlitenull
King King
King King

Reputation: 63317

CASE WHEN NULL makes wrong result in SQLite?

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!

Upvotes: 20

Views: 22128

Answers (3)

Alexei
Alexei

Reputation: 41

There's a bypass:

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

Upvotes: 4

PM 77-1
PM 77-1

Reputation: 13334

Use a different form of CASE instead:

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

Two useful links:

Upvotes: 9

Lamak
Lamak

Reputation: 70638

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

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

Upvotes: 56

Related Questions