Sql boolean test: zero OR null

Question

Is there a way to check both 0 and NULL with a single equality operator?

I understand that I can do this:

WHERE field = 0 OR field IS NULL

But my life would be a hundred times easier if it worked:

WHERE field IN (0, NULL)

(By the way, why doesn't this work?)

I also read about converting NULL to 0 into a SELECT statement (with COALESCE). The framework that I use will also make this unpleasant.

Understand this is strangely specific, but is there a way to check for 0 and NULL the same WHERE predicate?

Answer 1

This does not work because field In(0,Null) is equivalent to field = 0 Or field = Null , not field = 0 Or field Is Null . One option is to use a Case expression:

 Case When field = 0 Then 1 When field Is Null Then 1 End = 1 

The best option is to stick with the original field = 0 Or field Is Null , as it makes your intention more clear.

Answer 2

I would write this comparison using the convenient IFNULL function:

 IFNULL(field, 0) = 0 

And in answer to your question about the IN function:

"To comply with the SQL standard, IN returns NULL not only if the expression on the left side is NULL, but also if a match is not found in the list and one of the expressions in the list is NULL." - documents

Answer 3

Your WHERE statement is better than IMO, but can you try WHERE !IFNULL(field, 0) ?