58

Semi-open (or Half-Open, Half-Closed, Half-Bounded) intervals ([a,b), where xbelongs to the interval iff a <= x < b) are pretty common on programming, as they have many convenient properties.

Can anyone offer a rationale that explains why SQL's BETWEEN uses a closed interval ([a,b])? This is esp. inconvenient for dates. Why would you have BETWEEN behave like this?

Improve this question
8
  • I'm curious, what convenient properties do they have?
    – phant0m
    Aug 9, 2012 at 18:51
  • 2
    if it was not inclusive how could you easily query for all last names in the range A to D? or names W to Z? For numbers between 1 and 10 you can search 0 < n < 11, but for characters you would have to use ASCII numbers? or unicode numbers? Plus, the indexes can easily get you to the start of your data.
    – jqa
    Aug 9, 2012 at 18:58
  • 3
    I understand your frustration, (StartDate >= '2010-01-01' and StartDate < '2011-01-01'), works beautifully, to use Between the equivelent would be (StartDate between '2010-01-01' and '2010-12-31 23:59:59'), both bulky and one needs to know how many days are in Dec.
    – Kind Contributor
    Jan 17, 2013 at 0:31
  • 1
    @phant0m [a,b) U [c,d) == [a,d). [a:int,b:int) contains exactly b-a elements. Todd's comment shows how they work especially well for dates (which is were I miss them most). Basically, when coding, semiopen intervals tend to be simpler, easier to use and robust.
    – alex
    Jan 17, 2013 at 20:53
  • The best answer should have referenced objective decision documentation from the people who first specified BETWEEN for SQL, thereby answering Why, rather than the subjective answer selected.
    – Kind Contributor
    Nov 10, 2014 at 5:44

4 Answers 4

Reset to default
52

I think inclusive BETWEEN is more intuitive (and apparently, so did the SQL designers) than a semi-open interval. For example, if I say "Pick a number between 1 and 10", most people will include the numbers 1 and 10. The open-ended interval is actually particularly confusing for non-developers because it's asymmetric. SQL is occasionally used by non-programmers to make simple queries, and semi-open semantics would have been much more confusing for them.

Improve this answer
6
  • 15
    Your example focuses on integers, for decimal numbers and other delimited quantities (such as dates), the term between is ambiguous. If I say have you done X between 2012 and 2013, I don't include 2013 (or specifically the day 2013-01-01)
    – Kind Contributor
    Jan 17, 2013 at 0:35
  • 4
    @Todd Any usage of these terms are ambiguous. That is why mathematicians, scientists, and savvy programmers document their intention as "half-open" or such. I think the point of Oleski’s answer is that SQL was originally intended for end-users rather than programmers (really!). Apparently the SQL designers took a stab at a definition they thought best for that audience. But as the authors of the Question suggests, half-open is almost always better for working with ranges such as spans of time.
    – Basil Bourque
    Nov 7, 2014 at 19:42
  • "I think inclusive BETWEEN is more intuitive" is subjective. "SQL is occasionally used by non-programmers to make simple queries" - Non-programmers would equally need to check the spec.
    – Kind Contributor
    Nov 10, 2014 at 5:39
  • stackoverflow.com/questions/5080824/…
    – Lucky
    Feb 29, 2016 at 11:43
  • 2
    The question is also often asked "Pick a number from 1 to 10" (simply to avoid the obvious ambiguity). As a side note. You say "pick a number between 1 and 10"; most people probably wouldn't choose 1 or 10. Granted that's more of a psychology issue. :) People would still accept 1 and 10 as valid choices (in spite of being semantically incorrect); but that's a result of contextual interpretation assuming 1 and 10 are valid. If you were to say: "between 13 and 24" and you're more likely to be asked whether 13 and 24 are included.
    – Disillusioned
    Feb 27, 2017 at 9:39
38

QUESTION: Why is SQL's BETWEEN inclusive?

ANSWER: Because the SQL language designers made a poor design decision, in that they failed to deliver syntax that would allow developers to specify which of the 4 variants of BETWEEN (closed, semi-open-left, semi-open-right, or open) they'd prefer.

RECOMMENDATION: Unless/until the SQL standard is amended, don't use BETWEEN for dates/times. Instead get into the habit of coding DATE range comparisons as independent conditions on the start and end boundaries of your BETWEEN range. This is a bit verbose, but will leave you writing conditions that are intuitive (thus less likely to be buggy) and clear to the database optimizers, allowing for optimal execution plans to be determined and indexes to be used.

For example, if your query is accepting an input day specification and should return all records which fell on that date, you'd code as:

  • WHERE DATE_FIELD >= :dt AND DATE_FIELD < :dt+1

Trying to write the logic using BETWEEN risks performance issues and/or buggy code. Three common missteps:

1) WHERE DATE_FIELD BETWEEN :dt AND :dt+1

This is almost certainly a bug - user expects to see only records for a particular date, yet one day will wind up with a report containing records from 12:00 a.m. of the next day.

2) WHERE TRUNC(DATE_FIELD) = :dt

Gives right answer, but applying the function to DATE_FIELD will render most indexing / statistics useless (though sometimes DBAs will try to help by adding function-based indexes to the date fields - still burning up man-hours and disk space and adding overhead to IUD operations on the table)

3) WHERE EVENT_DATE BETWEEN :dt AND :dt + 1-1/24/60/60

Tom Kyte, Oracle guru extraordinaire, recommends this less-than-elegant (IMO) solution. Works great until you're spending all day to find that "1-1/24/06/60" in a query that gives incomplete results... or until you accidentally use it on a TIMESTAMP field. Plus, it's a bit proprietary; compatible with Oracle's DATE data type (which tracks to the second), but needs to be adjusted to the DATE/TIME precision of different database products.

SOLUTION: Petition the ANSI SQL committee to enhance the SQL language specs by modifying the BETWEEN syntax to support specification of alternatives to the CLOSED/INCLUSIVE default. Something like this would do the trick:

expr1 BETWEEN expr2 [ INCL[USIVE] | EXCL[USIVE] ] AND expr3 [ INCL[USIVE] | EXCL[USIVE] ]

Consider how easy it becomes to express WHERE DATE_FIELD BETWEEN :dt INCLUSIVE AND :dt+1 EXCLUSIVE (or just WHERE DATE_FIELD BETWEEN :dt AND :dt+1 EXCL)

Maybe ANSI SQL:2015?

Improve this answer
7
  • This answer is sage advice.
    – Basil Bourque
    Nov 7, 2014 at 19:54
  • @KevinKirkPatrick - Great answer! I suggest you also try to find the decision documentation as objective evidence of the original Why.
    – Kind Contributor
    Nov 10, 2014 at 5:44
  • 3
    I personally like exp1 BETWEEN exp2 AND exp3 AND exp1 != exp3 that way you get to keep the between operator so you know it's a ranged predicate, and the inequality predicate ensures that it's semi open.
    – Sentinel
    Sep 8, 2015 at 15:00
  • @Sentinel, Nice! I'm not going to declare myself a convert prematurely, but I'll definitely keep this variant in mind for when I next code date-range conditions. At first blush, it does have a greater linguistic appeal than the exp1 >= exp2 AND exp1 < exp3; and obviously solves issues with BETWEEN equally well. I'd be interested if any optimizers show greater "understanding" of one variation over the other; certainly, it seems plausible that yours may yield better results in that regard as well (though frankly, I'd be pretty disappointed in the optimizer that treated them differently)
    – KevinKirkpatrick
    Sep 9, 2015 at 15:48
  • @KevinKirkpatrick I've never profiled them to ascertain if there are any differences, and I too would be disappointed if there were.
    – Sentinel
    Sep 9, 2015 at 20:28
9

Both inclusive (a <= x <= b) and exclusive (a < x < b) are about equally common, so when making the standards they simply had to pick one. "Between" in common English is typically inclusive, and a SQL statement is meant to read similar to an English sentence, so inclusive was a sensible choice.

Improve this answer
5
  • 5
    Actually usage in English is even more mixed as you left out Half-Open. When we say "lunch is between noon and 1 PM" we mean half-open in that you are expected back in class / work at the moment of 13:00:00.000, with the break going up to but not including the first moment of the one-o-clock hour. a <= x < b is Half-Open.
    – Basil Bourque
    Nov 7, 2014 at 19:47
  • 1
    @BasilBourque: This may be due to infinite precision - e.g. lunch is between noon and 12:59:99.9999999999999....
    – Brendan
    Nov 8, 2014 at 2:04
  • @Brendan Yes, you are making my point. The infinite (or ambiguous) precision is one of the problems that is handled by using the half-open approach to defining a span of time. The point here is that in English conversation we intuitively handle open and closed (as mentioned in this answer) as well as half-open ranges without much thought. Each approach serves a purpose. That is why the SQL definition of BETWEEN is less than optimal. Ideally, SQL would follow the suggestion by KevinKirkpatrick.
    – Basil Bourque
    Nov 8, 2014 at 3:29
  • 2
    SQL is supposed to be English-like, and although inclusive and exclusive may be equally common, it's a query language for analysts and programmers. As a programmer, I think it's defined wrong, but that doesn't really matter, I just avoid using "BETWEEN" anyway. Not a big deal.
    – Kind Contributor
    Nov 10, 2014 at 5:40
  • it's just weird that for ORDER BY there are various variants like NULLS FIRST and NULLS LAST but nobody thought about a BETWEEN x AND y NONINCLUSIVE (not saying that anyone here is responsible for this though)
    – Daniel Alder
    May 29, 2020 at 8:37
5

The operator isn't called ∩[a,b), it's called BETWEEN, so it's considerably more appropriate for its semantics to be those of the English phrase "is between" than those of the mathematical predicate "is in semi-open interval".

Improve this answer
6
  • One needs to consider all applications, not just English applications for Integer sets. "between 1 and 10", "between noon and 1pm", "between 1.0 and 5.0" (grams). "between 5.50 and 10.30" (dollars). Continuous quantities would be logically (Englishly) assumed to be exclusive.
    – Kind Contributor
    Nov 10, 2014 at 5:42
  • 3
    The problem is that the BETWEEN operator doesn't use the semantics of the English phrase "is between". In English "between" is the time, space or interval that separates things (i.e. it's exclusive). If you try to kick a goal, the ball has to go between the posts to score. If you hit the post failing to pass between them - no score for you.
    – Disillusioned
    Feb 27, 2017 at 8:56
  • 1
    @CraigYoung as the accepted answer suggests (and I agree), " if I say "Pick a number between 1 and 10", most people will include the numbers 1 and 10 [in their range of possible answers]". In a spatial domain I agree with you, but for numbers I'd say it's different. Better for English Language & Usage than here though!
    – AakashM
    Feb 28, 2017 at 9:19
  • 1
    Between you and me, categorical claims about natural languages are usually unsafe.
    – AakashM
    Feb 28, 2017 at 12:28
  • 1
    @AakashM Don't worry, we can keep your dubious categorical claims about English semantics between us. ;)
    – Disillusioned
    Mar 1, 2017 at 11:59

Not the answer you're looking for? Browse other questions tagged or ask your own question.