SQL BETWEEN 子句与 <= AND >=
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4809083/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
BETWEEN clause versus <= AND >=
提问by wweicker
Is there a performance difference between using a BETWEEN clause or using <= AND >= comparisons?
使用 BETWEEN 子句或使用 <= AND >= 比较之间是否存在性能差异？
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.
在此示例中，年份列是带有索引的 VARCHAR2(4)。
采纳答案by wweicker
There is no performance difference between the two example queries because BETWEENis simply a shorthand way of expressing an inclusiverange comparison. When Oracle parses the BETWEENcondition it will automatically expand out into separate comparison clauses:
两个示例查询之间没有性能差异，因为BETWEEN这只是表达包含范围比较的一种简写方式。当 Oracle 解析BETWEEN条件时，它会自动扩展为单独的比较子句：
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
回答by Quassnoi
There is no difference.
没有区别。
Note that BETWEENis always inclusive and sensitive to the order of the arguments.
请注意，BETWEEN它始终包含且对参数顺序敏感。
BETWEEN '2010' AND '2005'will never be TRUE.
BETWEEN '2010' AND '2005'永远不会TRUE。
回答by Benoit
Actually it depends on your DBMS engine.
实际上，这取决于您的 DBMS 引擎。
Some database management systems will compute twice your expression (once for each comparison), and only once when you use BETWEEN.
一些数据库管理系统会计算两次您的表达式（每次比较一次），当您使用BETWEEN.
Actually if the expression can have a non-deterministic result BETWEENwill have a different behaviour, compare the following in SQLite:
实际上，如果表达式可以有一个不确定的结果BETWEEN会有不同的行为，请在 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.
如果您的表达式是（例如）子查询，这可能非常耗时。
回答by tbone
When in doubt (for Oracle anyway), run an explain planand 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.
如有疑问（无论如何对于 Oracle），请运行解释计划，您将看到优化器想要做什么。这适用于大多数关于“......之间是否存在性能差异”的问题。当然还有很多其他工具，但解释计划是一个好的开始。
回答by FolksLord
It shouldbe the same.
它应该是相同的。
Good database engine will generate same plan for that expression.
好的数据库引擎将为该表达式生成相同的计划。
回答by Lukas Eder
It may be worth considering the SQL standard for this (although this might notcorrespond to all implementations, even if it should):
可能值得为此考虑 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".
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
话虽如此，行为上没有区别，尽管对于 complex X，解析时间可能会有所不同，正如Benoit 在这里提到的
Found in http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
在http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt 中找到
回答by u5452748
run1 "X>=Y AND X<=Z"
run1 "X>=Y AND X<=Z"
run2 "X BETWEEN Y AND Z"
run2 "X BETWEEN Y AND Z"
I get one Plan hash valuewhen I run explain plan twice.
But Tom's runStats_pkg get diffent result:
Plan hash value当我运行解释计划两次时，我得到一个。但是汤姆的 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%
回答by luis.espinal
You better check your execution plans because there can be some weird edge cases where BETWEENcan have a different execution plan from the standard >= and <= combination.
您最好检查您的执行计划，因为可能存在一些奇怪的边缘情况，其中BETWEEN可能有与标准 >= 和 <= 组合不同的执行计划。
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.
显然要警告空客。但是由于执行计划会随着时间的推移而改变，而且我真的没有兴趣测试这些东西，我宁愿根本不使用 BETWEEN。
Sometimes less choice is better.
有时选择越少越好。
