SQL : BETWEEN vs <= and >=

user_40521 0
Viewed 203173

In SQL Server 2000 and 2005:

  • what is the difference between these two WHERE clauses?
  • which one I should use on which scenarios?

Query 1:

SELECT EventId, EventName
FROM EventMaster
WHERE EventDate BETWEEN '10/15/2009' AND '10/18/2009'

Query 2:

SELECT EventId, EventName
FROM EventMaster
WHERE EventDate >='10/15/2009'
  AND EventDate <='10/18/2009'

(Edit: the second Eventdate was originally missing, so the query was syntactically wrong)

between where tsql sql-server sql
10 Answers

Disclaimer: Everything below is only anecdotal and drawn directly from my personal experience. Anyone that feels up to conducting a more empirically rigorous analysis is welcome to carry it out and down vote if I'm. I am also aware that SQL is a declarative language and you're not supposed to have to consider HOW your code is processed when you write it, but, because I value my time, I do.

There are infinite logically equivalent statements, but I'll consider three(ish).

Case 1: Two Comparisons in a standard order (Evaluation order fixed)

A >= MinBound AND A <= MaxBound

Case 2: Syntactic sugar (Evaluation order is not chosen by author)

A BETWEEN MinBound AND MaxBound

Case 3: Two Comparisons in an educated order (Evaluation order chosen at write time)

A >= MinBound AND A <= MaxBound

Or

A <= MaxBound AND A >= MinBound

In my experience, Case 1 and Case 2 do not have any consistent or notable differences in performance as they are dataset ignorant.

However, Case 3 can greatly improve execution times. Specifically, if you're working with a large data set and happen to have some heuristic knowledge about whether A is more likely to be greater than the MaxBound or lesser than the MinBound you can improve execution times noticeably by using Case 3 and ordering the comparisons accordingly.

One use case I have is querying a large historical dataset with non-indexed dates for records within a specific interval. When writing the query, I will have a good idea of whether or not more data exists BEFORE the specified interval or AFTER the specified interval and can order my comparisons accordingly. I've had execution times cut by as much as half depending on the size of the dataset, the complexity of the query, and the amount of records filtered by the first comparison.

user_35458300

In this scenario col BETWEEN ... AND ... and col <= ... and col >= ... are equivalent.

SQL Standard defines also T461 Symmetric BETWEEN predicate:

 <between predicate part 2> ::=
 [ NOT ] BETWEEN [ ASYMMETRIC | SYMMETRIC ]
 <row value predicand> AND <row value predicand>

Transact-SQL does not support this feature.

BETWEEN requires that values are sorted. For instance:

SELECT 1 WHERE 3 BETWEEN 10 AND 1
-- no rows

<=>

SELECT 1 WHERE 3 >= 10 AND 3 <= 1
-- no rows

On the other hand:

SELECT 1 WHERE 3 BETWEEN SYMMETRIC 1 AND 10;
-- 1

SELECT 1 WHERE 3 BETWEEN SYMMETRIC 10 AND 1
-- 1

It works exactly as the normal BETWEEN but after sorting the comparison values.

db<>fiddle demo

user_50708790
Related