MySQL BETWEEN without endpoints

Question

MySQL's BETWEEN includes all results between two endpoints as well as the endpoints.

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. REFERENCE- http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between

Is there an function like REALLYBETWEEN which doesn't include the endpoints, or am I stuck using min < expr AND expr < max?

Answer 1

I think you can just write it yourself as:

where val > start and val < end

Between is:

where val >= start and val <= end

As for indexes, the first two should use an index correctly and identically to between, assuming that start and end are constants. I'm not sure if the last version will.

If val is a complicated expression, then consider the following:

select *
from (your query here) t
where t.val > start and t.val < end

This should evaluate val only once. I think the other forms would evaluate it twice.

Or, you could do:

where val between start and end and val not in (start, end)