sql boolean truth test: zero OR null

Question

Is there way to test for both 0 and NULL with one equality operator?

I realize I could do this:

WHERE field = 0 OR field IS NULL

But my life would be a hundred times easier if this would work:

WHERE field IN (0, NULL)

(btw, why doesn't that work?)

I've also read about converting NULL to 0 in the SELECT statement (with COALESCE). The framework I'm using would also make this unpleasant.

Realize this is oddly specific, but is there any way to test for 0 and NULL with one WHERE predicate?

Answer 1

I would write that comparison using the handy IFNULL function:

IFNULL(field, 0) = 0

And in response to your question about the IN function:

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