SQL：BETWEEN与<=和>=的区别

Question

在 SQL Server 2000 和 2005 中：

• 这两个 WHERE 子句有什么区别？
• 在哪些场景下应该使用它们中的哪一个？

查询1：

SELECT EventId, EventName
FROM EventMaster
WHERE EventDate BETWEEN '10/15/2009' AND '10/18/2009'

查询2：

SELECT EventId, EventName
FROM EventMaster
WHERE EventDate >='10/15/2009'
  AND EventDate <='10/18/2009'

（编辑：第二个事件日期最初缺失，因此查询在语法上是错误的）

- Shyju

---

这是一个准重复问题，与https://dev59.com/EUnSa4cB1Zd3GeqPRdEh相似。 - mjv

不完全是，处理日期时间的方式略有不同，而且那是针对SQL Server 2008的，Shyju没有询问之前无法确定答案在早期版本中是否相同。 - Irfy

Answer 1

它们是完全相同的：BETWEEN 是问题中包括两个值的语法的简写（EventDate >= '10/15/2009' and EventDate <= '10/19/2009'）。

当 BETWEEN 不适用于一个或两个值不应被包括在内时，可以使用替代的较长语法。

Select EventId,EventName from EventMaster
where EventDate >= '10/15/2009' and EventDate < '10/19/2009'

（请注意第二个条件中是<而不是<=。）

- Tony Andrews

---

我想强烈建议您永远不要使用BETWEEN，除非您正在处理DATE数据类型或已经确保您的datetime值不会有时间组件。保持这种一致性可以减少因错误地使用BETWEEN而获取一些您本不想得到的查询数据，或者认为您在获取额外的一天数据时实际上并没有... - Aaron Bertrand

在BETWEEN被转换为条件语句时，是否会有第二个编译器步骤？我知道这有点迂腐，但是否会有额外的开销？ - James Scott

奇怪...我觉得我被问题、答案的写法、评论以及我的代码中显然存在的一个 bug 搞糊涂了 =) - xmashallax

为了确保所有阅读此帖子的人不会感到困惑，between 子句包括了两个日期，而使用 > 和 < 语法时，您可以排除范围起始和结束的任何一个日期。 - Michele La Ferla

@RamonMartinez 我从未听说过这个。我可以想象一些DBMS可能会认识到BETWEEN过滤了一小段索引值，但是使用单独的谓词d >= v1 and d <= v2可能只会看到两个单独的谓词，它们都无法单独从索引中受益。但这只是一个猜测！ - Tony Andrews

Answer 2

它们是相同的。

需要注意的一点是，如果您将其用于 DATETIME 类型的日期上时，匹配结束日期的时间会是当天的开始时间：

<= 20/10/2009

不是同一个东西:

<= 20/10/2009 23:59:59

（它 将 与<= 20/10/2009 00:00:00.000相匹配）

- Irfy

---

在这种情况下，您可以简单地使用“2009-10-20”和“2009-10-21”之间来捕获日期。 - David Andrei Ned

@DavidAndreiNed，这也将匹配“2009-10-21 00:00:00.000” - 可能不是您想要的。 - Hans Kesting

你需要将 field BETWEEN '2009-10-20 00:00:00' AND '2009-10-20 23:59:59' 或者 field >= '2009-10-20 00:00:00' AND field <= '2009-10-20 23:59:59' 进行绝对确认。 - geilt

@geilt 您的示例会忽略在当天最后一秒内发生的任何事件...即在23:59:59和第二天的00:00:00之间。 - Seth Flowers

00:00:00 是下一天的开始，这就是为什么我使用 >= 和 <= 而不是 > 或 <。但如果你指的是微秒并且你存储它们，那么你也需要把最后一个微秒加上。 - geilt

@geilt，我认为要绝对确定的话，你可以使用< '2009-10-21'，这样就包括了10/21 00:00:00之前的所有内容，而且不需要担心微秒级别的问题。 - Phil Goldenberg

Answer 3

虽然BETWEEN易于阅读和维护，但我很少建议使用它，因为它是一个闭区间，并且正如先前提到的，这可能是日期问题的一个问题-即使没有时间组件。

例如，在处理每月数据时，通常会比较日期BETWEEN first AND last，但在实践中，通常更容易编写dt >= first AND dt < next-first（也解决了时间部分的问题）-因为确定last通常比确定next-first多一步（减去一天）。

此外，另一个值得注意的是，下限和上限需要以正确的顺序指定（即BETWEEN low AND high）。

- Cade Roux

Answer 4

正如@marc_s和@Cloud等人所说，对于闭区间，它们基本上是相同的。

但是，任何分数时间值可能会导致与半开区间（大于或等于和小于）不同的闭区间（大于或等于和小于或等于）出现问题，因为结束值在最后一个可能的时刻之后。

因此，为了避免这种情况，查询应该被重写为：

SELECT EventId, EventName
  FROM EventMaster
 WHERE (EventDate >= '2009-10-15' AND
        EventDate <  '2009-10-19')    /* <<<== 19th, not 18th */

由于 BETWEEN 不能用于半开区间，因此我总是仔细检查使用它的任何日期/时间查询，因为这很可能是一个错误。

- devstuff

Answer 5

我稍微倾向于使用 BETWEEN，因为它可以立即清晰地向读者表明您正在检查一个字段的范围。如果您的表中存在类似的字段名称，则此情况尤其如此。举例来说，如果我们的表既有 transactiondate 也有 transitiondate，那么当我阅读以下查询时：

transactiondate between ...

我可以立即知道测试的两端都是针对这个字段的。

如果我读取

transactiondate>='2009-04-17' and transactiondate<='2009-04-22'

我需要额外花费一些时间来确保这两个字段是相同的。

此外，随着查询的不断编辑，粗心的程序员可能会将这两个字段分开。我看过很多查询，类似于：

where transactiondate>='2009-04-17'
  and salestype='A'
  and customernumber=customer.idnumber
  and transactiondate<='2009-04-22'

如果他们尝试使用 BETWEEN，当然会出现语法错误并立即修复。

- Jay

Answer 6

通常情况下，这两个查询没有区别-不是所有的RDBMS平台都支持BETWEEN关键字，但如果支持，这两个查询应该是相同的。由于它们是相同的，因此在速度或其他方面没有区别-请使用您觉得更自然的那个。

- marc_s

Answer 7

我认为唯一的区别就是每个查询中语法糖的数量。BETWEEN只是一种更加简洁的表达方式，与第二个查询完全相同。

可能有些特定于关系型数据库管理系统的差异我不知道，但我并不认为会有太大区别。

- pyrocumulus

Answer 8

从逻辑上讲，它们没有任何差别。

在性能方面，通常情况下，在大多数数据库管理系统上，它们也没有任何区别。

- mjv

Answer 9

有无限个逻辑上等价的语句，但我会考虑三种（左右）。

情况1：标准顺序中的两个比较（评估顺序固定）

A >= MinBound AND A <= MaxBound

情况2：语法糖（评估顺序不由作者选择）

A BETWEEN MinBound AND MaxBound

情况3：有教养顺序中的两个比较（评估顺序在编写时选择）

A >= MinBound AND A <= MaxBound

或者

A <= MaxBound AND A >= MinBound

根据我的经验，情况1和情况2在性能上没有任何一致或显着的差异，因为它们是数据集无知的。

然而，情况3可以大大提高执行时间。具体来说，如果您正在处理一个大数据集并且恰好了解到A更可能大于MaxBound还是小于MinBound，那么通过使用情况3并相应地排序比较，您可以明显地改善执行时间。

我有一个使用案例是查询具有非索引日期的大型历史数据集中特定时间段内的记录。在编写查询时，我将对是否存在指定时间段之前或之后的更多数据有很好的了解，并可以相应地排序我的比较。根据数据集的大小、查询的复杂性以及第一个比较筛选的记录数量，执行时间可以减少一半甚至更多。

- LanchPad

---

什么？第3种情况与第1种和第2种情况的逻辑不同。如果您想查看A是否大于两个边界，则只需检查A是否大于MaxBound即可。您的帖子需要进行一些调整。 - mickmackusa

看起来我在等式运算符上打错了字。很好发现。 - LanchPad

Answer 10

在这种情况下，col BETWEEN ... AND ... 和 col <= ... and col >= ... 是等价的。

---

SQL标准还定义了T461 对称BETWEEN谓词：

 <between predicate part 2> ::=
 [ NOT ] BETWEEN [ ASYMMETRIC | SYMMETRIC ]
 <row value predicand> AND <row value predicand>

Transact-SQL does not support this feature.

BETWEEN 要求值已排序。例如：

SELECT 1 WHERE 3 BETWEEN 10 AND 1
-- no rows

<=>

SELECT 1 WHERE 3 >= 10 AND 3 <= 1
-- no rows

另一方面：

SELECT 1 WHERE 3 BETWEEN SYMMETRIC 1 AND 10;
-- 1

SELECT 1 WHERE 3 BETWEEN SYMMETRIC 10 AND 1
-- 1

它的工作方式与普通的BETWEEN完全相同，但是在对比值进行排序后执行。

db<>fiddle演示

- Lukasz Szozda