SQL Query Performance with BETWEEN AND Vs GTE/LTE

Question

In SQL we have these 2 function to retrieve data in range, I am using this on datetime field and trying to retrieve data for last 4 years.

Just wanted to confirm which is better in terms of query performance out of BETWEEN...AND or >= .

My final result row count is 200000 and it is has grouping, overall execution time is 3 minutes.

Possible duplicate of [SQL : BETWEEN vs <= and >=](http://stackoverflow.com/questions/1630239/sql-between-vs-and) — Chetan Sanghani, Jul 07 '16 at 10:26
2 UDFs returning data for 4 years, grouping of 200K records... And this led you to the question about `>=` and `between`? — Ivan Starostin, Jul 07 '16 at 10:33

score 0 · Answer 1 · answered Jul 07 '16 at 11:58

Actually, there is a slight difference BETWEEN and regular operators.

BETWEEN is inclusive, meaning the expression WHERE COL BETWEEN 200 AND 500 is equivalent to writing WHERE COL >=200 AND COL < 501 (or <=)

On the other hand, the operators < and >and = are exclusive.

Its a distinction that should be kept in mind.

1 Answers1