sql boolean truth test: zero OR null

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 abou开发者_如何学运维t 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?

---

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

---

It does not work because field In(0,Null) is the equivalent of field = 0 Or field = Null as opposed to 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 better option is to stick with your original field = 0 OR field Is Null as it makes your intent clearer.

---

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