SQL : BETWEEN vs <= 和 >=

SQL : BETWEEN vs <= and >=

提问by Shyju

In SQL Server 2000 and 2005:

在 SQL Server 2000 和 2005 中：

• what is the difference between these two WHEREclauses?
• which one I should use on which scenarios?

• 这两个WHERE条款有什么区别？
• 我应该在哪些场景中使用哪一个？

Query 1:

查询 1：

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

Query 2:

查询 2：

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

(Edit: the second Eventdate was originally missing, so the query was syntactically wrong)

（编辑：最初缺少第二个 Eventdate，因此查询在语法上是错误的）

回答by Tony Andrews

They are identical: BETWEENis a shorthand for the longer syntax in the question.

它们是相同的：BETWEEN是问题中较长语法的简写。

Use an alternative longer syntax where BETWEENdoesn't work e.g.

在BETWEEN不起作用的地方使用另一种更长的语法，例如

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

(Note <rather than <=in second condition.)

（注意<而不是<=在第二种情况下。）

回答by Irfy

They are the same.

他们是一样的。

One thing to be careful of, is if you are using this against a DATETIME, the match for the end date will be the beginning of the day:

需要注意的一件事是，如果您针对 DATETIME 使用它，则结束日期的匹配将是一天的开始：

<= 20/10/2009

is not the same as:

不等同于：

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

(it wouldmatch against <= 20/10/2009 00:00:00.000)

（这将对阵<= 20/10/2009 00:00:00.000）

回答by Cade Roux

Although BETWEENis easy to read and maintain, I rarely recommend its use because it is a closed interval and as mentioned previously this can be a problem with dates - even without time components.

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

For example, when dealing with monthly data it is often common to compare dates BETWEEN first AND last, but in practice this is usually easier to write dt >= first AND dt < next-first(which also solves the time part issue) - since determining lastusually is one step longer than determining next-first(by subtracting a day).

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

In addition, another gotcha is that lower and upper bounds do need to be specified in the correct order(i.e. BETWEEN low AND high).

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

回答by marc_s

Typically, there is no difference - the BETWEENkeyword is not supported on all RDBMS platforms, but if it is, the two queries should be identical.

通常，没有区别 -BETWEEN并非所有 RDBMS 平台都支持该关键字，但如果支持，则两个查询应该相同。

Since they're identical, there's really no distinction in terms of speed or anything else - use the one that seems more natural to you.

由于它们是相同的，因此在速度或其他方面确实没有区别 - 使用对您来说看起来更自然的那个。

回答by Jay

I have a slight preference for BETWEENbecause it makes it instantly clear to the reader that you are checking one field for a range. This is especially true if you have similar field names in your table.

我有点偏爱 ，BETWEEN因为它使读者立即清楚您正在检查一个字段的范围。如果您的表中有相似的字段名称，则尤其如此。

If, say, our table has both a transactiondateand a transitiondate, if I read

如果，说，我们的表有 atransactiondate和 a transitiondate，如果我读

transactiondate between ...

I know immediately that both ends of the test are against this one field.

我立即知道测试的两端都针对这一领域。

If I read

如果我读

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

I have to take an extra moment to make sure the two fields are the same.

我必须花额外的时间来确保这两个字段相同。

Also, as a query gets edited over time, a sloppy programmer might separate the two fields. I've seen plenty of queries that say something like

此外，随着时间的推移对查询进行编辑，粗心的程序员可能会将这两个字段分开。我已经看到很多查询都说类似

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

If they try this with a BETWEEN, of course, it will be a syntax error and promptly fixed.

如果他们尝试使用BETWEEN，当然，这将是一个语法错误并立即修复。

回答by devstuff

As mentioned by @marc_s, @Cloud, et al. they're basically the same for a closed range.

正如@marc_s、@Cloud 等人所提到的。它们在封闭范围内基本相同。

But any fractional time values may cause issues with a closed range (greater-or-equal and less-or-equal) as opposed to a half-open range (greater-or-equal and less-than) with an end value afterthe last possible instant.

但是，任何分数时间值可能会导致具有封闭范围的问题（大于或-等于和小于或-等于），而不是一个半开区间（大于或-等于和小于号）具有端值后的最后可能的瞬间。

So to avoid that the query should be rewritten as:

因此，为避免查询应重写为：

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

Since BETWEENdoesn't work for half-open intervals I always take a hard look at any date/time query that uses it, since its probably an error.

由于BETWEEN在半开间隔内不起作用，我总是仔细查看使用它的任何日期/时间查询，因为它可能是一个错误。

回答by pyrocumulus

I think the only difference is the amount of syntactical sugar on each query. BETWEEN is just a slick way of saying exactly the same as the second query.

我认为唯一的区别是每个查询的语法糖量。BETWEEN 只是一种巧妙的表达方式，与第二个查询完全相同。

There might be some RDBMS specific difference that I'm not aware of, but I don't really think so.

可能有一些我不知道的 RDBMS 特定差异，但我真的不这么认为。

回答by mjv

Logically there are no difference at all. Performance-wise there are -typically, on most DBMSes- no difference at all.

从逻辑上讲，根本没有区别。在性能方面——通常，在大多数 DBMS 上——根本没有区别。

回答by Andy Jones

See this excellent blog postfrom Aaron Bertrandabout why you should change your string format and how the boundary values are handled in date range queries.

请参阅Aaron Bertrand 的这篇优秀博客文章，了解为什么应该更改字符串格式以及如何在日期范围查询中处理边界值。

回答by LanchPad

Disclaimer: Everything below is only anecdotal and drawn directly from my personal experience. Anyone that feels up to conducting a more empirically rigorous analysis is welcome to carry it out and down vote if I'm. I am also aware that SQL is a declarative language and you're not supposed to have to consider HOW your code is processed when you write it, but, because I value my time, I do.

免责声明：以下所有内容都只是轶事，直接来自我的个人经验。如果我愿意，任何愿意进行更严格的实证分析的人都可以执行它并投票。我也知道 SQL 是一种声明性语言，您在编写代码时不应该考虑如何处理代码，但是，因为我珍惜我的时间，所以我会这样做。

There are infinite logically equivalent statements, but I'll consider three(ish).

有无数逻辑上等效的语句，但我会考虑三个（ish）。

Case 1: Two Comparisons in a standard order (Evaluation order fixed)

案例 1：按标准顺序进行两次比较（评估顺序固定）

A >= MinBound AND A <= MaxBound

A >= MinBound AND A <= MaxBound

Case 2: Syntactic sugar (Evaluation order is not chosen by author)

案例2：语法糖（评估顺序不是作者选择的）

A BETWEEN MinBound AND MaxBound

介于 MinBound 和 MaxBound 之间

Case 3: Two Comparisons in an educated order (Evaluation order chosen at write time)

案例 3：按照受过教育的顺序进行两次比较（写入时选择的评估顺序）

A >= MinBound AND A <= MaxBound

A >= MinBound AND A <= MaxBound

Or

或者

A <= MaxBound AND A >= MinBound

A <= MaxBound AND A >= MinBound

In my experience, Case 1 and Case 2 do not have any consistent or notable differences in performance as they are dataset ignorant.

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

However, Case 3 can greatly improve execution times. Specifically, if you're working with a large data set and happen to have some heuristic knowledge about whether Ais more likely to be greater than the MaxBoundor lesser than the MinBoundyou can improve execution times noticeably by using Case 3 and ordering the comparisons accordingly.

但是，案例 3 可以大大缩短执行时间。具体来说，如果您正在处理大型数据集并且碰巧对A是否更有可能大于MaxBound或小于MinBound有一些启发式知识，则可以通过使用案例 3 并为比较排序来显着缩短执行时间因此。

One use case I have is querying a large historical dataset with non-indexed dates for records within a specific interval. When writing the query, I will have a good idea of whether or not more data exists BEFORE the specified interval or AFTER the specified interval and can order my comparisons accordingly. I've had execution times cut by as much as half depending on the size of the dataset, the complexity of the query, and the amount of records filtered by the first comparison.

我的一个用例是查询一个包含非索引日期的大型历史数据集，以获取特定时间间隔内的记录。在编写查询时，我会很好地了解在指定间隔之前或指定间隔之后是否存在更多数据，并可以相应地对我的比较进行排序。根据数据集的大小、查询的复杂性以及第一次比较过滤的记录数量，我将执行时间减少了一半。