Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
    
I'm curious, what convenient properties do they have? –  phant0m Aug 9 '12 at 18:51
    
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. –  james Aug 9 '12 at 18:58
1  
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. –  Todd Jan 17 '13 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 '13 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. –  Todd Nov 10 at 5:44

4 Answers 4

up vote 27 down vote accepted

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.

share|improve this answer
2  
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) –  Todd Jan 17 '13 at 0:35
    
@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 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. –  Todd Nov 10 at 5:39

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.

share|improve this answer
    
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 at 19:47
    
@BasilBourque: This may be due to infinite precision - e.g. lunch is between noon and 12:59:99.9999999999999.... –  Brendan Nov 8 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 at 3:29
    
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. –  Todd Nov 10 at 5:40

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?

share|improve this answer
    
This answer is sage advice. –  Basil Bourque Nov 7 at 19:54
    
@KevinKirkPatrick - Great answer! I suggest you also try to find the decision documentation as objective evidence of the original Why. –  Todd Nov 10 at 5:44

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".

share|improve this answer
    
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. –  Todd Nov 10 at 5:42

protected by gnat Nov 7 at 8:10

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

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