Tell me more ×
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.
up vote 4 down vote favorite

Semi-open 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
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 at 0:31
@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 at 20:53

3 Answers

up vote 16 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
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 at 0:35
up vote 5 down vote

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
up vote 5 down vote

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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