Search code examples
sqlitecastingboolean

SQLite Boolean WHERE to check for 0 and NULL value

I'm looking for a SQLite WHERE clause which will give me the records if they are:

0 (false)
NULL (false)
1 (true)

So besides 0 and 1 i also have records with NULL.

I now do a

SELECT * FROM my_table WHERE (MyBoolean IS NULL OR MyBoolean = 0) AND SomeOtherValue NOT NULL;

When using

SELECT * FROM my_table WHERE NOT MyBoolean AND SomeOtherValue NOT NULL;

The value=NULL records are not found, only the value=0 records :o(

Do i have to cast my column as a boolean?

Solution

  • Casting won't help here (CAST (null AS sometype) is still NULL). Anything that you can use will be more or less equivalent of the expression you use now: I find WHERE NOT COALESCE(MyBoolean,0) a bit more readable, but that's a personal preference.