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?
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)?
WHERE field IN (0, NULL)isWHERE field = 0 or field = NULLthe last case isunknownfield IN (0, NULL)is equivalent tofield = 0 OR field = NULLand you cannot use the "=" operator to test for NULL, the result is unknown.