between clause versus and

between clause versus and  using -'sql,performance,oracle,oracle10g,oracle11g'

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

i.e. these two queries:

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


  FROM table  
 WHERE year >= '2005' AND year <= '2010';

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

asked Oct 13, 2015 by yashwantpinge
0 votes

5 Answers

0 votes

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:


  FROM table
 WHERE column BETWEEN :lower_bound AND :upper_bound  

...will automatically become:

  FROM table
 WHERE :lower_bound <= column
   AND :upper_bound >= column
answered Oct 13, 2015 by r3tt
0 votes

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.

answered Oct 13, 2015 by vimaldas2005
0 votes

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.

answered Oct 13, 2015 by yogeshplv
0 votes

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



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

answered Oct 13, 2015 by abhi
0 votes

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.

answered Oct 13, 2015 by abhi