Using OR, or IN with NULL

MySQL & MariaDB Other MySQL® Questions
1

Is there any benefit to using one of these over the other?

  1. AND (r.resolve_status = 0 OR ISNULL(r.resolve_status))

  2. AND r.resolve_status IN(0,NULL)

I ran a query through an online “tuner” and the only recommendation was to use IN instead of OR. I can not find any documentation on this, but then again searching for variations of ‘using or or in’ may not have been the best approach.

2

Hi RPereira;

Generally speaking if you only have a couple values you are comparing, the two options will be similar performance wise. However if you have a larger list of values, then the IN tends to perform better. There is no easy way to say what that magic cutoff number is, but in most cases if you have 2-3 values, either way is fine. Any more than that and I would go with IN typically (though like most answers it will depend on the situation still).

-Scott