16

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?

stakx - no longer contributing
  • 83,039
  • 20
  • 168
  • 268
Jeff Meatball Yang
  • 37,839
  • 27
  • 91
  • 125
  • 1
    I wasn't even aware that there was a between operator. Looking at it for the first time it wasn't 100% clear to me if between would be inclusive or exclusive. I did guess right but since, the answers say there is no performance difference, I would stick with the second version to make it clearer. However, I am the type of person who will add parentheses where they aren't need just to be sure there isn't any misunderstanding when someone else reads my code. – drs9222 Apr 22 '10 at 16:30
  • Does this answer your question? [SQL : BETWEEN vs <= and >=](https://stackoverflow.com/questions/1630239/sql-between-vs-and) – J Eti Jan 23 '22 at 06:02

5 Answers5

7

No benefit, just a syntax sugar.

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

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
2

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.

Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
2

There's no performance benefit, it's just easier to read/write the first one.

tloflin
  • 4,050
  • 1
  • 25
  • 33
2

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.

Jay
  • 26,876
  • 10
  • 61
  • 112
0

Oh, but you all referring to the case when search value is on the left side of the where clause.

Did anybody look at differences when is in the other side of the clause.

SELECT * FROM table WHERE @date BETWEEN someCol1 AND someCol2

SELECT * FROM table WHERE someCol1 >= @date AND someCol2 <= @date

Leo Bravo
  • 11
  • 3