CODEWITHSUNDEEP
sql-serverperformancebetween
Jeff Meatball Yang
Jeff Meatball Yang

Reputation: 39027

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?

Upvotes: 18

Views: 16054

Answers (5)

Leo Bravo
Leo Bravo

Reputation: 31

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

Upvotes: 0

Jay
Jay

Reputation: 27474

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.

Upvotes: 2

Adriaan Stander
Adriaan Stander

Reputation: 166396

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.

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425391

No benefit, just a syntax sugar.

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

Upvotes: 8

tloflin
tloflin

Reputation: 4050

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

Upvotes: 2

Related Questions