既然可以不用 BETWEEN 运算符,为什么还要使用它呢?

发布于 2024-08-16 00:36:37 字数 330 浏览 4 评论 0原文

如下这两个查询所示,我们发现它们都运行良好。然后我很困惑为什么我们应该使用 BETWEEN 因为我发现 BETWEEN 在不同的数据库中表现不同,如 w3学校

SELECT *
FROM employees
WHERE salary BETWEEN 5000 AND 15000;

SELECT *
FROM employees
WHERE salary >= 5000
AND salary <= 15000;

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

SELECT *
FROM employees
WHERE salary BETWEEN 5000 AND 15000;

SELECT *
FROM employees
WHERE salary >= 5000
AND salary <= 15000;

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(11

瞄了个咪的 2024-08-23 00:36:37

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_source 只是一个包含 1,000,000 记录的虚拟表。

当然,这可以使用子查询来解决,但在 MySQL 中效率较低。

当然,BETWEEN 更具可读性。在查询中使用它需要 3 次才能永远记住语法。

SQL ServerMySQL 中,针对带有非前导 '%' 的常量的 LIKE 也是以下形式的简写一对 >=<

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)

但是,LIKE 语法更清晰。

BETWEEN can help to avoid unnecessary reevaluation of the expression:

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_source is just a dummy table with 1,000,000 records.

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

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

In SQL Server and MySQL, LIKE against a constant with non-leading '%' is also a shorthand for a pair of >= 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, LIKE syntax is more legible.

醉殇 2024-08-23 00:36:37

当比较的表达式是复杂的计算而不仅仅是简单的列时,使用 BETWEEN 具有额外的优点;它可以节省两次写出复杂表达式的时间。

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.

心病无药医 2024-08-23 00:36:37

T-SQL 中的 BETWEEN 支持 NOT 运算符,因此您可以使用类似的结构

WHERE salary not between 5000 AND 15000; 

在我看来,这对人类来说更清楚 然后

WHERE salary < 5000 OR salary > 15000;

最后,如果您只输入一次列名,那么您犯错误的机会就会减少

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

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

半寸时光 2024-08-23 00:36:37

带“之间”的版本更容易阅读。如果我要使用第二个版本,我可能会

5000 <= salary and salary <= 15000

出于同样的原因编写它。

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.

你另情深 2024-08-23 00:36:37

我投票@Quassnoi - 正确性是一个巨大的胜利。

我通常发现文字比诸如 <、<=、>、>=、!= 等语法符号更有用。是的,我们需要(更好、准确的)结果。至少我消除了在视觉上误解和恢复符号含义的可能性。如果您使用 <= 并感觉到来自 select 查询的逻辑上不正确的输出,您可能会徘徊一段时间,最后得出的结论是您确实写了 <= 代替了 >= [视觉误解?]。希望我说清楚了。

我们不是缩短了代码(同时使其看起来更高级),这意味着更简洁且易于维护吗?

SELECT * 
FROM emplyees 
WHERE salary between 5000 AND 15000; 



SELECT * 
FROM emplyees 
WHERE salary >= 5000 AND salary <= 15000; 

第一个查询仅使用 10 个单词,第二个查询使用 12 个!

I vote @Quassnoi - correctness is a big win.

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!

动听の歌 2024-08-23 00:36:37

就我个人而言,我不会使用 BETWEEN,只是因为在您给定的示例中,似乎没有明确的定义是否应该包含或排除用于限制条件的值:

SELECT *
FROM emplyees
WHERE salary between 5000 AND 15000;

范围 可以包括 5000 和 15000,或者可以排除它们。

从语法上讲,我认为应该排除它们,因为值本身不在 给定数字之间。但我的观点恰恰是,而使用诸如 >= 之类的运算符是非常具体的。并且数据库之间或同一数据库的增量/版本之间发生变化的可能性较小。


编辑以回应 Pavel 和 Jonathan 的评论。

正如 Pavel 所指出的,ANSI SQL (http://www.contrib.andrew.cmu.edu/~shadow/sql /sql1992.txt)早在 1992 年,就要求应在返回的日期内考虑终点,相当于 X >= lower_bound AND X <= upper_bound

8.3

<前><代码>功能

指定范围比较。

格式

<谓词之间> ::=
<行值构造函数> [ 不 ] 之间
<行值构造函数> AND <行值构造函数>

语法规则

1) 三个<行值构造函数>应具有相同的度数。

2) 令各个值为具有相同序数位置的值
在两个<行值构造函数>中。

3)三个<行值各自值的数据类型
构造函数应具有可比性。

4) 设 X、Y 和 Z 为第一、第二和第三 <行值 con-
分别是structor>s。

5) “X NOT BETWEEN Y AND Z”相当于“NOT ( X BETWEEN Y AND
Z)”。

6)“X在Y和Z之间”等价于“X>=Y且X<=Z”。

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:

SELECT *
FROM emplyees
WHERE salary between 5000 AND 15000;

The range could include the 5000 and 15000, or it could exclude them.

Syntactically I think it should exclude them, since the values themselves are not between the 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.


Edited in 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:

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".
剩一世无双 2024-08-23 00:36:37

如果端点包含在内,则 BETWEEN 是首选语法。

对专栏的引用越少,意味着当事情发生变化时需要更新的地方就越少。这是工程原理,更少的东西意味着更少的东西会损坏。

这也意味着某人在包含 OR 等内容时放错括号的可能性较小。 IE:

WHERE salary BETWEEN 5000 AND (15000
  OR ...)

...如果将括号放在 BETWEEN 语句的 AND 部分周围,则会出现错误。对比:

WHERE salary >= 5000
 AND (salary <= 15000
  OR ...)

...只有当有人查看查询返回的数据时,您才会知道存在问题。

If the endpoints are inclusive, then BETWEEN 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:

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:

WHERE salary >= 5000
 AND (salary <= 15000
  OR ...)

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

↘紸啶 2024-08-23 00:36:37

从语义上来说,这两个表达式具有相同的结果。

但是,BETWEEN 是单个谓词,而不是与 AND 组合的两个比较谓词。根据 RDBMS 提供的优化器,单个谓词可能比两个谓词更容易优化。

尽管我预计大多数现代 RDBMS 实现应该对这两个表达式进行相同的优化。

Semantically, the two expressions have the same result.

However, BETWEEN is 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.

Although I expect most modern RDBMS implementations should optimize the two expressions identically.

身边 2024-08-23 00:36:37

如果

  SELECT id FROM entries 
  WHERE 
     (SELECT COUNT(id) FROM anothertable WHERE something LEFT JOIN something ON...) 
     BETWEEN entries.max AND entries.min;

用你的语法重写这个而不使用临时存储,情况会更糟。

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.

心碎的声音 2024-08-23 00:36:37

我最好使用第二个,因为你总是知道它是 <= 还是 <

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

爱殇璃 2024-08-23 00:36:37

在 SQL 中,我同意 BETWEEN 基本上是不必要的,并且可以在语法上用 5000 <=薪资 AND 薪资 <= 15000 进行模拟。也是有限度的;我经常想应用包含下限和排除上限:@start <= 当 AND 当 @end,而 BETWEEN 则无法做到这一点。

如果测试的值是复杂表达式的结果,OTOH, BETWEEN 会很方便。

如果 SQL 和其他语言能够效仿 Python 使用正确的数学符号:5000 <=工资<= 15000,那就太好了。

一个可以让你的代码更具可读性的小技巧:使用 <<和 <= 优先于 >且>=。

In SQL, I agree that BETWEEN is 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.

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: 5000 <= salary <= 15000.

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

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文