BETWEEN operator vs. >= AND <=: Is there a performance difference?
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?
4 Solutions collect form web for “BETWEEN operator vs. >= AND <=: Is there a performance difference?”
No benefit, just a syntax sugar.
By using the
BETWEEN version, you can avoid function reevaluation in some cases.
There’s no performance benefit, it’s just easier to read/write the first one.
No, no performance benifit. Its just a little candy.
If you were to check a query comparison, something like
DECLARE @Table TABLE( ID INT ) SELECT * FROM @Table WHERE ID >= 1 AND ID <= 100 SELECT * FROM @Table WHERE ID BETWEEN 1 AND 100
and check the execution plan, you should notice that it is exactly the same.
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'
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.