Why BETWEEN is error prone
We do not use BETWEEN for now, and chances are we’ll never expose it as a direct operator in our query builder. Sure, there are pros and cons on using x BETWEEN a AND b rather then x >= a AND x <= b, and that’s one reason why we have this article. However, assuming our tool tries to avoids as much as possible error prone SQL constructs and the “element of surprise”, it seems like the bad won over the good.
Table of Contents
The Pros
- BETWEEN can make an expression more readable. x NOT BETWEEN a AND b could be more readable than x < a OR x > b.
- BETWEEN always refers to an interval or range, while separate comparatives may not highlight this fact.
- Expression may require just one single evaluation, instead of two.
The Cons
It may not be clear if the end points are included in the interval. They always are, for any of our supported databases, but the confusion still persists, especially if you come up with a NOT BETWEEN clause. For the record, 10 BETWEEN 10 and 100 is true, while 10 NOT BETWEEN 10 and 100 is false.
What if first end point is greater than the last? Is 20 BETWEEN 100 AND 10 a true and valid statement? Most humans maybe, including Microsoft Access, think it is. All other supported databases return false. And this is bad, bad, bad! Because there should be a difference between an invalid statement (and we should have an error thrown) and a valid filter that returns nothing. The problem with these databases is all of them translate literally x BETWEEN a AND b by x >= a AND x <= b. Our 20 BETWEEN 100 AND 10 becomes 20 >= 100 AND 20 <= 10, which is indeed false.
Another con for our application is, at least for now, we did not find a proper way to expose on the GUI this singular conditional operator with two parameters. And things that look more complex than they should, may introduce complexity to the end user. Or we try exactly the opposite.
Conclusion
We’ll leave BETWEEN for now, we’ll see in the future.
