BETWEEN operator vs. >= AND <=: Is there a performance difference?

Question

These two statements are logically equivalent:

SELECT * FROM table WHERE someColumn BETWEEN 1 AND 100

SELECT * FROM table WHERE someColumn >= 1 AND someColumn <= 100

Is there a potential performance benefit to one versus the other?

Answer 1

Hmm, here was a surprising result. I don't have SQL Server here, so I tried this in Postgres. Obviously disclaimers apply: this won't necessarily give the same results, your mileage may vary, consult a physician before using. But still ...

I just wrote a simple query in two different ways:

select *
from foo
where (select code from bar where bar.barid=foo.barid) between 'A' and 'B'

and

select *
from foo
where (select code from bar where bar.barid=foo.barid)>='A'
and (select code from bar where bar.barid=foo.barid)<='B'

Surprisingly to me, both had almost identical run times. When I did an EXPLAIN PLAN, they gave identical results. Specifically, the first query did the lookup against bar twice, once for the >= test and again for the <= test, just like the second query.

Conclusion: In Postgres, at least, BETWEEN is indeed just syntactic sugar.

Personally, I use it regularly because it is clearer to the reader, especially if the value being tested is an expression. Figuring out that two complex expressions are identical can be a non-trivial exercise. Figuring out that two complex expressions SHOULD BE identical even though they're not is even more difficult.

Answer 2

No benefit, just a syntax sugar.

By using the BETWEEN version, you can avoid function reevaluation in some cases.