BETWEEN子句与<=和>=的区别

Question

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

即这两个查询：

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

...和

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

在这个例子中，年份列是VARCHAR2（4），并且有一个索引。

- wweicker

---

不过，我不会使用像 '2005' 这样的字符串来过滤年份或日期。 - Petrus Theron

Answer 1

没有任何区别。

请注意，BETWEEN 操作符总是包含其两个参数，并且对参数的顺序敏感。

BETWEEN '2010' AND '2005' 永远不会是 TRUE。

- Quassnoi

---

取决于底层RDBMS系统。SQL-92规定_X BETWEEN Y AND Z_等价于_"X>=Y AND X<=Z"_, 但并不是所有供应商都遵循这一准则。例如，SQL Server就没有这样做。 - Nicholas Carey

@Nicholas：请看一下问题标签。至于你的“SQL Server”评论，能否提供一个参考资料？ - Quassnoi

除非他们已经对之间的事情进行了严格限制，否则顺序是无关紧要的。虽然我最近没有测试过这一点，但以前曾经遇到过类似问题。如果顺序对于测试很重要，我会使用>=和<=来明确测试。 - Nicholas Carey

@Nicholas：你能给这个版本起个名字吗？ - Quassnoi

@Nicholas：不能是 SQL Server 2000。也不可能是共享 Sybase 代码库的 SQL Server 4.21。因此，它必须是 6.0、6.5 或 7.0，在这些版本中，他们引入了标准违规行为，只为在 2000 年修复它（而没有提供向后兼容性，甚至在文档中提到此行为）。虽然我并不怀疑你的话，但这对于一个微软产品来说确实很奇怪。 - Quassnoi

Answer 2

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

例如：

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

...将自动变为：

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

- wweicker

---

@rsenna: 不，我发布这个问题是为了将信息存储在这里，因为我们办公室的某人问我这个问题，当我搜索时发现以前没有人在这里提出过这个问题。这是我今天对世界的知识贡献，也符合常见问题和使命陈述的有效使用。我本意是在10秒内提问、回答并接受我的答案，但系统不会让我那么快就接受自己的答案，所以其他人也开始回答了... - wweicker

没问题。事实上，Stack Overflow 批准这种行为... - rsenna

@rsenna: 没错！:) 感谢你找到那个链接，并进一步解释了我忘记的情况，即你必须等待两天才能回答自己的问题...（http://blog.stackoverflow.com/2009/01/accept-your-own-answers/） - wweicker

个人认为这个徽章存在是有争议的，因为我所看到的情况都是人们试图通过作弊来获得它（在发布问题后几分钟内发布已知答案以获取重复标记）。更糟糕的是，有些人会优先选择自己的答案而不是正确回答的人。我不会通过投票支持这样的答案，也不会鼓励这种行为。 - OMG Ponies

Answer 3

实际上这取决于您的数据库管理系统引擎。

一些数据库管理系统将计算两次您的表达式（每个比较一次），而仅在使用 BETWEEN 时计算一次。

实际上，如果表达式可能具有不确定结果，则 BETWEEN 将具有不同的行为，请参考 SQLite 中的以下内容：

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

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

这可能非常耗时，特别是如果你的表达式是一个子查询。

- Benoit

---

有趣的想法...你能提供一个例子吗？你在哪个数据库上体验过这个？ - Lukas Eder

嗯，想得不错。这还存在一些关于SQL标准的未解之谜，以及是否真的可以声称这两个表达式是等价的。 - Lukas Eder

Answer 4

如果对于 Oracle 的问题不确定，可以运行一个 解释计划，这样你就能看到优化器想要做什么。这适用于大多数关于“...之间是否存在性能差异”的问题。当然还有很多其他工具，但解释计划是一个好的开始。

- tbone

Answer 5

应该是一样的。

好的数据库引擎将为该表达式生成相同的计划。

- FolksLord

---

是的，我的评论比这个具体情况更通用。授人以鱼不如授人以渔... - tbone

Answer 6

对于这个问题，考虑采用SQL标准可能值得一试（尽管即使它“应该”做到了，也可能不符合所有实现）：

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".

话虽如此，行为没有区别，但对于复杂的X，解析时间可能会有所不同，正如Benoit在这里提到的。

发现于http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

- Lukas Eder

Answer 7

运行1 "X>=Y AND X<=Z"

运行2 "X BETWEEN Y AND Z"

当我运行两次解释计划时，我得到一个计划哈希值。 但是Tom的runStats_pkg得到了不同的结果：

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%

- 要你命三千

Answer 8

你最好检查一下你的执行计划，因为有些奇怪的边缘情况下，BETWEEN 可能会与标准的 >= 和 <= 组合有不同的执行计划。

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

显然，买家自负。但由于执行计划可能随时间而变化，而且我真的没有兴趣测试这些东西，所以我宁愿根本不使用BETWEEN。

有时候，选择少一点更好。

- luis.espinal