blocks|key|1159700|text|BETWEEN可以帮助避免对表达式进行不必要的重新计算：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1159701|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|code-block|syntax|javascript|1159702|t_source只是一个包含1,000,000记录的虚拟表。|1159703|当然，这可以使用子查询来解决，但在MySQL中效率较低。|1159704|当然，BETWEEN更具可读性。为了永远记住语法，在查询中使用它需要花费3时间。|1159705|在SQL+Server和MySQL中，对具有非前导'%25'的常量的LIKE也是>=和<对的简写|1159706|SET+SHOWPLAN_TEXT+ON
GO
SELECT++*
FROM++++master
WHERE+++name+LIKE+'string%25'
GO
SET+SHOWPLAN_TEXT+OFF
GO


%7C--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%25')+ORDERED+FORWARD)|1159707|但是，LIKE语法更清晰。|1159708|entityMap^0|0|7|0|0|0|8|E|9|0|H|5|0|3|7|10|1|0|1|A|C|5|P|3|W|4|12|2|15|1|0|0|3|4|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|11|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|12|8|@$9|13|A|14|B|C]|$9|15|A|16|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|17|8|@$9|18|A|19|B|C]]|D|@]|E|$]]|$1|O|3|P|5|6|7|1A|8|@$9|1B|A|1C|B|C]|$9|1D|A|1E|B|C]]|D|@]|E|$]]|$1|Q|3|R|5|6|7|1F|8|@$9|1G|A|1H|B|C]|$9|1I|A|1J|B|C]|$9|1K|A|1L|B|C]|$9|1M|A|1N|B|C]|$9|1O|A|1P|B|C]|$9|1Q|A|1R|B|C]]|D|@]|E|$]]|$1|S|3|T|5|H|7|1S|8|@]|D|@]|E|$I|J]]|$1|U|3|V|5|6|7|1T|8|@$9|1U|A|1V|B|C]]|D|@]|E|$]]|$1|W|3|-4|5|6|7|1W|8|@]|D|@]|E|$]]]|X|$]]

<code>BETWEEN</code> can help to avoid unnecessary reevaluation of the expression:

<pre><code>SELECT AVG(RAND(20091225) BETWEEN 0.2 AND 0.4)
FROM t_source;

---
0.1998

SELECT AVG(RAND(20091225) &gt;= 0.2 AND RAND(20091225) &lt;= 0.4)
FROM t_source;

---
0.3199
</code></pre>

<code>t_source</code> is just a dummy table with <code>1,000,000</code> records.

Of course this can be worked around using a subquery, but in <code>MySQL</code> it's less efficient.

And of course, <code>BETWEEN</code> is more readable. It takes <code>3</code> times to use it in a query to remember the syntax forever.

In <code>SQL Server</code> and <code>MySQL</code>, <code>LIKE</code> against a constant with non-leading <code>'%'</code> is also a shorthand for a pair of <code>&gt;=</code> and <code>&lt;</code>:

<pre><code>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] &lt; 'strinH' AND [test].[dbo].[master].[name] &gt;= 'string'), WHERE:([test].[dbo].[master].[name] like 'string%') ORDERED FORWARD)
</code></pre>

However, <code>LIKE</code> syntax is more legible.

blocks|key|1159656|text|当所比较的表达式是一个复杂的计算而不仅仅是一个简单的列时，使用BETWEEN有额外的优点；它省去了两次写出复杂的表达式。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1159657|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|1159470|text|带有"between“的版本更容易阅读。如果我使用第二个版本，我可能会把它写成|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1159471|5000+<=+salary+and+salary+<=+15000|code-block|syntax|javascript|1159472|出于同样的原因。|1159473|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

The version with "between" is easier to read. If I were to use the second version I'd probably write it as

<pre><code>5000 &lt;= salary and salary &lt;= 15000
</code></pre>

for the same reason.

blocks|key|1374871|text|BETWEEN+in+T-SQL支持NOT运算符，因此可以使用如下结构|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1374872|WHERE+salary+not+between+5000+AND+15000;+|code-block|syntax|javascript|1374873|在我看来，对于一个人类来说|1374874|WHERE+salary+<+5000+OR+salary+>+15000;|1374875|最后，如果只键入一次列名，出错的机会就会减少|1374876|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

BETWEEN in T-SQL supports NOT operator, so you can use constructions like

<pre><code>WHERE salary not between 5000 AND 15000; 
</code></pre>

In my opinion it's more clear for a human then

<pre><code>WHERE salary &lt; 5000 OR salary &gt; 15000;
</code></pre>

And finally if you type column name just one time it gives you less chances to make a mistake

blocks|key|1159772|text|我投票给@Quassnoi+--正确性是一个巨大的胜利。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1159773|我通常发现文字比语法符号(如<，<=，>，>=，!=等)更有用。是的，我们需要(更好，更准确)结果。至少我在视觉上摆脱了错误解释和还原符号含义的可能性。如果您使用<=并检测到来自select查询的逻辑上不正确的输出，那么您可能会在一段时间后得出这样的结论:您确实编写了<=而不是>=视觉上的错误解释？希望我说清楚了。|1159774|我们不是在缩短代码(同时让它看起来更高级)，这意味着更简洁、更容易维护吗？|1159775|SELECT+*+
FROM+emplyees+
WHERE+salary+between+5000+AND+15000;+



SELECT+*+
FROM+emplyees+
WHERE+salary+>=+5000+AND+salary+<=+15000;+|code-block|syntax|javascript|1159776|第一个查询只使用10个单词，第二个查询使用12个单词！|1159777|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|R|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I vote @Quassnoi - correctness is a big win. 

I usually find literals more useful than the syntax symbols like &lt;, &lt;=, >, >=, != 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 &lt;= 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 &lt;= 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?

<pre><code>SELECT * 
FROM emplyees 
WHERE salary between 5000 AND 15000; 



SELECT * 
FROM emplyees 
WHERE salary &gt;= 5000 AND salary &lt;= 15000; 
</code></pre>

First query uses only 10 words and second uses 12!

blocks|key|1374801|text|如果端点是包含性的，那么BETWEEN是首选语法。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1374802|对列的引用越少，当事情发生变化时，需要更新的点就越少。这是工程原理，越少的东西就意味着更少的东西可以被打破。|1374803|这也意味着有人把错误的括号放在像包含OR这样的东西上的可能性更小。即：|1374804|WHERE+salary+BETWEEN+5000+AND+(15000
++OR+...)|code-block|syntax|javascript|1374805|...如果你把括号放在BETWEEN语句的AND部分，你会得到一个错误。对比：|1374806|WHERE+salary+>=+5000
+AND+(salary+<=+15000
++OR+...)|1374807|...只有当有人检查从查询返回的数据时，您才会知道有问题。|1374808|entityMap^0|C|7|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Z|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|10|8|@]|D|@]|E|$]]|$1|J|3|K|5|L|7|11|8|@]|D|@]|E|$M|N]]|$1|O|3|P|5|6|7|12|8|@]|D|@]|E|$]]|$1|Q|3|R|5|L|7|13|8|@]|D|@]|E|$M|N]]|$1|S|3|T|5|6|7|14|8|@]|D|@]|E|$]]|$1|U|3|-4|5|6|7|15|8|@]|D|@]|E|$]]]|V|$]]

If the endpoints are inclusive, then <code>BETWEEN</code> is the preferred syntax.

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:

<pre><code>WHERE salary BETWEEN 5000 AND (15000
 OR ...)
</code></pre>

...you'll get an error if you put the bracket around the AND part of a BETWEEN statement. Versus:

<pre><code>WHERE salary &gt;= 5000
 AND (salary &lt;= 15000
 OR ...)
</code></pre>

...you'd only know there's a problem when someone reviews the data returned from the query.

blocks|key|1375066|text|更糟的是|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1375067|++SELECT+id+FROM+entries+
++WHERE+
+++++(SELECT+COUNT(id)+FROM+anothertable+WHERE+something+LEFT+JOIN+something+ON...)+
+++++BETWEEN+entries.max+AND+entries.min;|code-block|syntax|javascript|1375068|在不使用临时存储的情况下，使用您的语法重写此代码。|1375069|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

worse if it's 

<pre><code> SELECT id FROM entries 
 WHERE 
 (SELECT COUNT(id) FROM anothertable WHERE something LEFT JOIN something ON...) 
 BETWEEN entries.max AND entries.min;
</code></pre>

Rewrite this one with your syntax without using temporary storage.

blocks|key|1159343|text|我最好使用第二个，因为你总是知道它是<=还是<|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1159344|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I'd better use the 2nd one, as you always know if it's &lt;= or &lt;

blocks|key|1374685|text|在SQL中，我同意BETWEEN在大多数情况下是不必要的，并且可以用5000+<=+salary+AND+salary+<=+15000在语法上模拟它。它也是有限制的；我经常想要应用一个包含性的下限和一个排他性的上限：@start+<=+when+AND+when+<+@end，这是用BETWEEN做不到的。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1374686|如果要测试的值是复杂表达式的结果，则OTOH，BETWEEN非常方便。|1374687|如果SQL和其他语言能效仿Python使用适当的数学符号：5000+<=+salary+<=+15000，那就太好了。|1374688|一个小技巧将使您的代码更具可读性:优先使用<和<=，而不是>和>=。|1374689|entityMap^0|9|7|Y|Y|31|U|3Z|7|0|0|T|N|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]|$9|Q|A|R|B|C]|$9|S|A|T|B|C]|$9|U|A|V|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|W|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|X|8|@$9|Y|A|Z|B|C]]|D|@]|E|$]]|$1|J|3|K|5|6|7|10|8|@]|D|@]|E|$]]|$1|L|3|-4|5|6|7|11|8|@]|D|@]|E|$]]]|M|$]]

In SQL, I agree that <code>BETWEEN</code> is mostly unnecessary, and can be emulated syntactically with <code>5000 &lt;= salary AND salary &lt;= 15000</code>. It is also limited; I often want to apply an inclusive lower bound and an exclusive upper bound: <code>@start &lt;= when AND when &lt; @end</code>, which you can't do with <code>BETWEEN</code>.

OTOH, BETWEEN is convenient if the value being tested is the result of a complex expression.

It would be nice if SQL and other languages would follows Python's lead in using proper mathematical notation: <code>5000 &lt;= salary &lt;= 15000</code>.

One small tip that wil make your code more readable: use &lt; and &lt;= in preference to > and >=.

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 <a href="http://www.w3schools.com/sql/sql_between.asp" rel="nofollow noreferrer">w3school</a>

<pre><code>SELECT *
FROM employees
WHERE salary BETWEEN 5000 AND 15000;

SELECT *
FROM employees
WHERE salary &gt;= 5000
AND salary &lt;= 15000;
</code></pre>

Why use the BETWEEN operator when we can do without it?

如下两个查询所示，我们发现它们都工作得很好。然后我感到困惑，为什么我们要使用BETWEEN，因为我发现在不同的数据库中，BETWEEN的行为是不同的，正如在中发现的那样SELECT *FROM employeesWHERE salary BETWEEN 5000 AND 15000;SELECT *FROM employeesWHERE salary >= 5000AND salary <= 15

问当我们可以不使用BETWEEN运算符时，为什么还要使用它呢？
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问当我们可以不使用BETWEEN运算符时，为什么还要使用它呢？EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问当我们可以不使用BETWEEN运算符时，为什么还要使用它呢？
EN