This is kind of philosophical question...

Today i spent way too much time to debug a method which was generating a SQL command.

I figured out the error was because the code was generating something like:


which wasn't returning any value, while the correct form was:


My question is, if you can use the BETWEEN syntax which is more close to the natural language than WHERE X >= lowerBound AND X <= upperBound wouldn't make sense to have designed it to automatically detect which is the lower bound and the upper bound?

I mean, if i say i want numbers between 10 and 5 or between 5 and 10 in a natural language i guess everyone would tell me the number in the interval [5,10]

Am I pretending too much from sql ?

closed as primarily opinion-based by Dan Pichelman, durron597, GlenH7, user22815, haylem Aug 27 '15 at 15:02

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 4
    Between isn't standard between different databases (and not even implemented on others). How it behaves on the database you are using is something to check the documentation on. – user40980 Aug 26 '15 at 16:12
  • 1
    @MichaelT: BETWEEN is standard in SQL-92 (see section 8.3) and explains why OP's BETWEEN 10 AND 5 doesn't work as expected. – Blrfl Aug 26 '15 at 16:45
  • 1
    @Blrfl some databases extend that. Postgresql has BETWEEN SYMMETRIC There was also a period of time where I was working in the 90s where implementation of SQL-92 was inconsistent at best. Between remains one of those 'sometimes gives surprising results' and 'reading of the query may be non-intuitive, especially in situations where dates are involved'. I've gotten burned by it in the past. – user40980 Aug 26 '15 at 17:04
  • Yes, As @MichaelIT mentioned, the between function is meant to be read from left to right. – v115567 Aug 26 '15 at 18:14
  • @MichaelT: I was doing databases back then, too, and you're right; there was plenty left to be desired when it came to adhering to the standards. 20 years on, things are lots better. BETWEEN (A)SYMMETRIC is part of SQL-99. – Blrfl Aug 27 '15 at 13:50

Indeed, you are asking too much from your database.

Databases are designed for DBAs and developers. Those are not the sort of persons who would usually consider that a..b = b..a in a context of a database or a program, and would naturally tend to put the minimum first, and the maximum second.

Probably for that reason, the designers of the database you are using considered that the inverse, that is a..b with a > b, constitutes an edge case which is not worth special treatment. After all, the goal of the database engine is to be hugely fast, and that means removing edge cases from the code.

Less code means less risks of an error within it, less debugging, less logging, less testing and, ultimately, less CPU cycles and memory usage.

If the same persons were designing not a database, but a product targeting ordinary users, they may have taken the opposite decision, considering that a simple user would consider that “a is between b and c” is exactly the same as “a is between c and b”.

How did they decide when creating the spec? Maybe they made an educated guess. Maybe they couldn't care less and considered that they don't want to write more code. Or maybe they actually observed programmers using their database engine, and determined that the frequency of the mistake you did is too small to care about. For the same reason, many databases ten years ago would respond faster to select a, b from c than select * from c, but this is not always true any longer: by watching thousands of programmers using * when they shouldn't, designers of some database engines ended up handling this case specifically in order to give the same or very similar performance for both queries.

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