BETWEEN子句与< = AND> = [英] BETWEEN clause versus <= AND >=

问题描述

使用BETWEEN子句或使用< = AND> =比较之间是否存在性能差异?

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

即这两个查询:

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

...和

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

在此示例中，年份列是VARCHAR2(4)，上面有一个索引.

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

推荐答案

两个示例查询之间没有性能差异，因为BETWEEN只是表示包含范围范围比较的简写方式.当Oracle解析BETWEEN条件时，它将自动扩展为单独的比较子句:

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:

例如.

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

...将自动变为:

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

这篇关于BETWEEN子句与&lt; = AND&gt; =的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！