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?

  • Parsing xml in sql server
  • Sql server 2005 knowing new record is inserted
  • SQL Server : select every day of a range if there are no days in a table with condition
  • How to make additional columns on pivot of sql table in SQL Server 2005?
  • Check date between two dates t-sql
  • SQL Server database backup restore on lower version
  • 4 Solutions collect form web for “BETWEEN operator vs. >= AND <=: Is there a performance difference?”

    No benefit, just a syntax sugar.

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

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

    No, no performance benifit. Its just a little candy.

    If you were to check a query comparison, something like

            ID INT
    SELECT  *
    FROM    @Table
    WHERE   ID >= 1 AND ID <= 100
    SELECT  *
    FROM    @Table 

    and check the execution plan, you should notice that it is exactly the same.

    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'


    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.