BETWEEN clause versus <= AND >=

Is there a performance difference between using a BETWEEN clause or using <= AND >= comparisons?

i.e. these two queries:

SELECT *  
  FROM table  
 WHERE year BETWEEN '2005' AND '2010';  

...and

SELECT *  
  FROM table  
 WHERE year 开发者_如何转开发>= '2005' AND year <= '2010';

In this example, the year column is VARCHAR2(4) with an index on it.

There is no difference.

Note that BETWEEN is always inclusive and sensitive to the order of the arguments.

BETWEEN '2010' AND '2005' will never be TRUE.

There is no performance difference between the two example queries because BETWEEN is simply a shorthand way of expressing an inclusive range comparison. When Oracle parses the BETWEEN condition it will automatically expand out into separate comparison clauses:

ex.

SELECT *  
  FROM table
 WHERE column BETWEEN :lower_bound AND :upper_bound  

...will automatically become:

SELECT *  
  FROM table
 WHERE :lower_bound <= column
   AND :upper_bound >= column

Actually it depends on your DBMS engine.

Some database management systems will compute twice your expression (once for each comparison), and only once when you use BETWEEN.

Actually if the expression can have a non-deterministic result BETWEEN will have a different behaviour, compare the following in SQLite:

WHERE RANDOM() BETWEEN x AND y -- one random value generated

WHERE RANDOM() >= x AND RANDOM() <= y -- two distinct random values generated

This can be very time consuming if your expression is (for example) a subquery.

When in doubt (for Oracle anyway), run an explain plan and you'll see what the optimizer wants to do. This would apply to most questions about "is there a performance difference between ...". Of course there are a lot of other tools also, but explain plan is a good start.

It should be the same.

Good database engine will generate same plan for that expression.

It may be worth considering the SQL standard for this (although this might not correspond to all implementations, even if it should):

Format

<between predicate> ::=
  <row value constructor> [ NOT ] BETWEEN
    <row value constructor> AND <row value constructor>

Syntax Rules

[...]

6) "X BETWEEN Y AND Z" is equivalent to "X>=Y AND X<=Z".

Having said so, there is no difference in behaviour, although for complex X, there may be a difference in parsing time, as mentioned by Benoit here

Found in http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

run1 "X>=Y AND X<=Z"

run2 "X BETWEEN Y AND Z"

I get one Plan hash value when I run explain plan twice. But Tom's runStats_pkg get diffent result:

Run1 ran in 1 cpu hsecs
Run2 ran in 1 cpu hsecs
run 1 ran in 100% of the time

Name                      Run1    Run2        Diff
STAT...recursive calls          12      13       1
STAT...CPU used by this sessio       2       3       1
STAT...physical read total IO        0       1       1
STAT...consistent gets          18      19       1
...
...
LATCH.row cache objects         44,375   1,121     -43,254
LATCH.cache buffers chains      68,814   1,397     -67,417
STAT...logical read bytes from     655,360     573,440     -81,920
STAT...session uga memory max      123,512       0    -123,512
STAT...session pga memory      262,144  65,536    -196,608
STAT...session pga memory max      262,144  65,536    -196,608
STAT...session uga memory     -327,440  65,488     392,928

Run1 latches total versus runs -- difference and pct
Run1        Run2    Diff       Pct
203,927      28,673    -175,254    711.22%

You better check your execution plans because there can be some weird edge cases where BETWEEN can have a different execution plan from the standard >= and <= combination.

https://blog.pythian.com/oracle-can-between-and-greater-than-or-equal-to-and-less-than-or-equal-to-differ/

Caveat emptor obviously. But since execution plans can change over time and I really do not have an appetite to test such things, I rather not use BETWEEN at all.

Sometimes less choice is better.