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?
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.
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:
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.
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)
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?
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.
protected by gnat Nov 7 '14 at 8:10
Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?