SQL 当我们可以不用 BETWEEN 运算符时，为什么要使用它？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1960801/
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
Why use the BETWEEN operator when we can do without it?
提问by Thunder
As seen below the two queries, we find that they both work well. Then I am confused why should we ever use BETWEEN because I have found that BETWEEN behaves differently in different databases as found in w3school
如下两个查询所示，我们发现它们都运行良好。然后我很困惑为什么我们应该使用 BETWEEN 因为我发现 BETWEEN 在不同的数据库中的行为与在w3school 中发现的不同
SELECT *
FROM employees
WHERE salary BETWEEN 5000 AND 15000;
SELECT *
FROM employees
WHERE salary >= 5000
AND salary <= 15000;
回答by Quassnoi
BETWEENcan help to avoid unnecessary reevaluation of the expression:
BETWEEN可以帮助避免对表达式进行不必要的重新计算：
SELECT AVG(RAND(20091225) BETWEEN 0.2 AND 0.4)
FROM t_source;
---
0.1998
SELECT AVG(RAND(20091225) >= 0.2 AND RAND(20091225) <= 0.4)
FROM t_source;
---
0.3199
t_sourceis just a dummy table with 1,000,000records.
t_source只是一个带有1,000,000记录的虚拟表。
Of course this can be worked around using a subquery, but in MySQLit's less efficient.
当然，这可以使用子查询来解决，但MySQL效率较低。
And of course, BETWEENis more readable. It takes 3times to use it in a query to remember the syntax forever.
当然，BETWEEN更具可读性。3在查询中使用它以永远记住语法需要时间。
In SQL Serverand MySQL, LIKEagainst a constant with non-leading '%'is also a shorthand for a pair of >=and <:
在SQL Serverand 中MySQL，LIKE针对具有非前导的常量'%'也是一对>=and的简写<：
SET SHOWPLAN_TEXT ON
GO
SELECT *
FROM master
WHERE name LIKE 'string%'
GO
SET SHOWPLAN_TEXT OFF
GO
|--Index Seek(OBJECT:([test].[dbo].[master].[ix_name_desc]), SEEK:([test].[dbo].[master].[name] < 'strinH' AND [test].[dbo].[master].[name] >= 'string'), WHERE:([test].[dbo].[master].[name] like 'string%') ORDERED FORWARD)
However, LIKEsyntax is more legible.
但是，LIKE语法更清晰。
回答by Jonathan Leffler
Using BETWEEN has extra merits when the expression that is compared is a complex calculation rather than just a simple column; it saves writing out that complex expression twice.
当被比较的表达式是一个复杂的计算而不仅仅是一个简单的列时，使用 BETWEEN 有额外的好处；它省去了两次写出复杂表达式的麻烦。
回答by bniwredyc
BETWEEN in T-SQL supports NOT operator, so you can use constructions like
T-SQL 中的 BETWEEN 支持 NOT 运算符，因此您可以使用类似的结构
WHERE salary not between 5000 AND 15000;
In my opinion it's more clear for a human then
在我看来，这对人类来说更清楚
WHERE salary < 5000 OR salary > 15000;
And finally if you type column name just one time it gives you less chances to make a mistake
最后，如果你只输入一次列名，那么你犯错的机会就会减少
回答by Rich
The version with "between" is easier to read. If I were to use the second version I'd probably write it as
带有“之间”的版本更容易阅读。如果我要使用第二个版本，我可能会把它写成
5000 <= salary and salary <= 15000
for the same reason.
出于同样的原因。
回答by David says reinstate Monica
Personally, I wouldn't use BETWEEN, simply because there seems no clear definition of whether it should include, or exclude, the values which serve to bound the condition, in your given example:
就个人而言，我不会使用BETWEEN，只是因为在您给定的示例中，似乎没有明确定义是否应该包含或排除用于约束条件的值：
SELECT *
FROM emplyees
WHERE salary between 5000 AND 15000;
The range couldinclude the 5000 and 15000, or it couldexclude them.
该范围可以包括 5000 和 15000，也可以排除它们。
Syntactically I think it should exclude them, since the values themselves are notbetweenthe given numbers. But my opinion is precisely that, whereas using operators such as >=is very specific. And less likely to change between databases, or between incremements/versions of the same.
从语法上讲，我认为它应该排除它们，因为值本身不在给定的数字之间。但我的观点正是如此，而使用诸如此类的运算符>=则非常具体。并且不太可能在数据库之间或相同的增量/版本之间发生变化。
Editedin response to Pavel and Jonathan's comments.
编辑响应帕维尔和乔纳森的意见。
As noted by Pavel, ANSI SQL (http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt) as far back as 1992, mandates the end-points should be considered within the returned date and equivalent to X >= lower_bound AND X <= upper_bound:
正如 Pavel 所指出的，ANSI SQL (http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt) 早在 1992 年，就要求在返回的日期内考虑终点，相当于X >= lower_bound AND X <= upper_bound：
8.3
Function Specify a range comparison. Format <between predicate> ::= <row value constructor> [ NOT ] BETWEEN <row value constructor> AND <row value constructor> Syntax Rules 1) The three <row value constructor>s shall be of the same degree. 2) Let respective values be values with the same ordinal position in the two <row value constructor>s. 3) The data types of the respective values of the three <row value constructor>s shall be comparable. 4) Let X, Y, and Z be the first, second, and third <row value con- structor>s, respectively. 5) "X NOT BETWEEN Y AND Z" is equivalent to "NOT ( X BETWEEN Y AND Z )". 6) "X BETWEEN Y AND Z" is equivalent to "X>=Y AND X<=Z".
8.3
Function Specify a range comparison. Format <between predicate> ::= <row value constructor> [ NOT ] BETWEEN <row value constructor> AND <row value constructor> Syntax Rules 1) The three <row value constructor>s shall be of the same degree. 2) Let respective values be values with the same ordinal position in the two <row value constructor>s. 3) The data types of the respective values of the three <row value constructor>s shall be comparable. 4) Let X, Y, and Z be the first, second, and third <row value con- structor>s, respectively. 5) "X NOT BETWEEN Y AND Z" is equivalent to "NOT ( X BETWEEN Y AND Z )". 6) "X BETWEEN Y AND Z" is equivalent to "X>=Y AND X<=Z".
回答by sangam
I vote @Quassnoi - correctness is a big win.
我投票@Quassnoi - 正确性是一个巨大的胜利。
I usually find literals more useful than the syntax symbols like <, <=, >, >=, != etc. Yes, we need (better, accurate) results. And at least I get rid of probabilities of mis-interpreting and reverting meanings of the symbols visually. If you use <= and sense logically incorrect output coming from your select query, you may wander some time and only arrive to the conclusion that you did write <= in place of >= [visual mis-interpretation?]. Hope I am clear.
我通常发现文字比 <、<=、>、>=、!= 等语法符号更有用。是的，我们需要（更好、更准确）的结果。并且至少我摆脱了视觉上错误解释和恢复符号含义的可能性。如果您使用 <= 并感觉到来自选择查询的逻辑不正确的输出，您可能会徘徊一段时间并得出结论，您确实写了 <= 代替了 >= [视觉错误解释？]。希望我清楚。
And aren't we shortening the code (along with making it more higher-level-looking), which means more concise and easy to maintain?
我们不是缩短了代码（同时使它看起来更高级），这意味着更简洁和易于维护吗？
SELECT *
FROM emplyees
WHERE salary between 5000 AND 15000;
SELECT *
FROM emplyees
WHERE salary >= 5000 AND salary <= 15000;
First query uses only 10 words and second uses 12!
第一个查询仅使用 10 个单词，第二个使用 12 个！
回答by OMG Ponies
If the endpoints are inclusive, then BETWEENis the preferred syntax.
如果端点包含在内，则BETWEEN是首选语法。
Less references to a column means less spots to update when things change. It's the engineering principle, that less things means less stuff can break.
对列的更少引用意味着在事情发生变化时更新的点更少。这是工程原理，更少的东西意味着更少的东西可以破坏。
It also means less possibility of someone putting the wrong bracket for things like including an OR. IE:
这也意味着不太可能有人为包含 OR 之类的东西放置错误的括号。IE：
WHERE salary BETWEEN 5000 AND (15000
OR ...)
...you'll get an error if you put the bracket around the AND part of a BETWEEN statement. Versus:
...如果你将括号放在 BETWEEN 语句的 AND 部分，你会得到一个错误。相对：
WHERE salary >= 5000
AND (salary <= 15000
OR ...)
...you'd only know there's a problem when someone reviews the data returned from the query.
...只有当有人查看从查询返回的数据时，您才会知道存在问题。
回答by Bill Karwin
Semantically, the two expressions have the same result.
在语义上，这两个表达式具有相同的结果。
However, BETWEENis a single predicate, instead of two comparison predicates combined with AND. Depending on the optimizer provided by your RDBMS, a single predicate may be easier to optimize than two predicates.
但是，BETWEEN是单个谓词，而不是两个比较谓词组合在一起AND。根据 RDBMS 提供的优化器，单个谓词可能比两个谓词更容易优化。
Although I expect most modern RDBMS implementations shouldoptimize the two expressions identically.
尽管我希望大多数现代 RDBMS 实现应该以相同的方式优化这两个表达式。
回答by SF.
worse if it's
更糟的是
SELECT id FROM entries
WHERE
(SELECT COUNT(id) FROM anothertable WHERE something LEFT JOIN something ON...)
BETWEEN entries.max AND entries.min;
Rewrite this one with your syntax without using temporary storage.
在不使用临时存储的情况下用你的语法重写这个。
回答by Marcelo Cantos
In SQL, I agree that BETWEENis mostly unnecessary, and can be emulated syntactically with 5000 <= salary AND salary <= 15000. It is also limited; I often want to apply an inclusive lower bound and an exclusive upper bound: @start <= when AND when < @end, which you can't do with BETWEEN.
在 SQL 中，我同意这BETWEEN几乎是不必要的，并且可以在语法上用5000 <= salary AND salary <= 15000. 它也是有限的；我经常想应用一个包含下限和一个排他上限: @start <= when AND when < @end，你不能用BETWEEN.
OTOH, BETWEEN is convenient if the value being tested is the result of a complex expression.
如果被测试的值是复杂表达式的结果，则 OTOH, BETWEEN 很方便。
It would be nice if SQL and other languages would follows Python's lead in using proper mathematical notation: 5000 <= salary <= 15000.
如果 SQL 和其他语言在使用正确的数学符号方面遵循 Python 的领先优势，那就太好了：5000 <= salary <= 15000.
One small tip that wil make your code more readable: use < and <= in preference to > and >=.
一个能让你的代码更具可读性的小技巧：使用 < 和 <= 优先于 > 和 >=。
