CODEWITHSUNDEEP
sqlsql-servert-sqlwhere-clausebetween
Shyju
Shyju

Reputation: 218722

SQL : BETWEEN vs <= and >=

In SQL Server 2000 and 2005:

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)

Upvotes: 167

Views: 255203

Answers (10)

Tony Andrews
Tony Andrews

Reputation: 132570

They are identical: BETWEEN is a shorthand for the longer syntax in the question that includes both values (EventDate >= '10/15/2009' and EventDate <= '10/19/2009').

Use an alternative longer syntax where BETWEEN doesn't work because one or both of the values should not be included e.g.

Select EventId,EventName from EventMaster
where EventDate >= '10/15/2009' and EventDate < '10/19/2009'

(Note < rather than <= in second condition.)

Dealing with DATETIME

When dealing with DATETIME (vs. DATE) the result might not be intuitive however. If EventDate happens to be a DATETIME the comparison is done with the time (!)

EventDate BETWEEN '10/15/2009' AND '10/18/2009'

will in fact become:

EventDate BETWEEN '2009-10-15 00:00' and '2009-10-18 00:00'

as no time has specified. This will effectively exclude everything on 10/18/2009.

The proper expression in that case would be:

EventDate BETWEEN '2009-10-15 00:00' and '2009-10-18 23:59:59'

Upvotes: 175

LanchPad
LanchPad

Reputation: 287

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.

Upvotes: 3

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

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

Upvotes: 0

Jay
Jay

Reputation: 27464

I have a slight preference for BETWEEN because it makes it instantly clear to the reader that you are checking one field for a range. This is especially true if you have similar field names in your table.

If, say, our table has both a transactiondate and a transitiondate, if I read

transactiondate between ...

I know immediately that both ends of the test are against this one field.

If I read

transactiondate>='2009-04-17' and transactiondate<='2009-04-22'

I have to take an extra moment to make sure the two fields are the same.

Also, as a query gets edited over time, a sloppy programmer might separate the two fields. I've seen plenty of queries that say something like

where transactiondate>='2009-04-17'
  and salestype='A'
  and customernumber=customer.idnumber
  and transactiondate<='2009-04-22'

If they try this with a BETWEEN, of course, it will be a syntax error and promptly fixed.

Upvotes: 5

Cade Roux
Cade Roux

Reputation: 89661

Although BETWEEN is easy to read and maintain, I rarely recommend its use because it is a closed interval and as mentioned previously this can be a problem with dates - even without time components.

For example, when dealing with monthly data it is often common to compare dates BETWEEN first AND last, but in practice this is usually easier to write dt >= first AND dt < next-first (which also solves the time part issue) - since determining last usually is one step longer than determining next-first (by subtracting a day).

In addition, another gotcha is that lower and upper bounds do need to be specified in the correct order (i.e. BETWEEN low AND high).

Upvotes: 18

Irfy
Irfy

Reputation: 2521

They are the same.

One thing to be careful of, is if you are using this against a DATETIME, the match for the end date will be the beginning of the day:

<= 20/10/2009

is not the same as:

<= 20/10/2009 23:59:59

(it would match against <= 20/10/2009 00:00:00.000)

Upvotes: 46

devstuff
devstuff

Reputation: 8387

As mentioned by @marc_s, @Cloud, et al. they're basically the same for a closed range.

But any fractional time values may cause issues with a closed range (greater-or-equal and less-or-equal) as opposed to a half-open range (greater-or-equal and less-than) with an end value after the last possible instant.

So to avoid that the query should be rewritten as:

SELECT EventId, EventName
  FROM EventMaster
 WHERE (EventDate >= '2009-10-15' AND
        EventDate <  '2009-10-19')    /* <<<== 19th, not 18th */

Since BETWEEN doesn't work for half-open intervals I always take a hard look at any date/time query that uses it, since its probably an error.

Upvotes: 4

mjv
mjv

Reputation: 75115

Logically there are no difference at all. Performance-wise there are -typically, on most DBMSes- no difference at all.

Upvotes: 4

pyrocumulus
pyrocumulus

Reputation: 9290

I think the only difference is the amount of syntactical sugar on each query. BETWEEN is just a slick way of saying exactly the same as the second query.

There might be some RDBMS specific difference that I'm not aware of, but I don't really think so.

Upvotes: 3

marc_s
marc_s

Reputation: 754368

Typically, there is no difference - the BETWEEN keyword is not supported on all RDBMS platforms, but if it is, the two queries should be identical.

Since they're identical, there's really no distinction in terms of speed or anything else - use the one that seems more natural to you.

Upvotes: 5

Related Questions