BETWEEN clause versus <= AND >=

sql performance oracle oracle10g oracle11g

wweicker · Jan 26, 2011 · Viewed 72.4k times · Source

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.

Answer

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.