I have the following dummy table.
I created the following query:
SELECT * FROM ALUNOS
WHERE IDADE BETWEEN 10 AND 15
Returns students A, C, and D.
Now, because when I reverse the order in BETWEEN
SELECT * FROM ALUNOS
WHERE IDADE BETWEEN 15 AND 10
Does not return anything?
First of all, I understand that there is some logical motivation in the question. If something is between 1 and 3, in principle it is between 3 and 1.
Which is not true if it is a trade that opens from 10 pm to 6 am. Between 6 a.m. and 10 p.m. it will be closed. Between 10 pm and 6 am it will be open. In other words, concepts are often relativized.
This, let's answer the question:
It is so, because whoever defined the function wanted so. The interpretation of the creator (s) of the function is that the data will be in ascending order. It is not about them having a better reasoning than yours and the other way around. If you create a language and define that your BETWEEN will work regardless of parameter order, I believe it will be fine too.
Anyway, if you need to know order-independent, just do this:
a BETWEEN( b, c ) OR a BETWEEN( c, b )
It may not be as elegant as calling a function alone, but in compensation, knowing the "limitation" of the function, it can be useful sometime when the order is important.
Exaggerating: I could make a third language with a BETWEEN that would give inverted result if the parameters were inverted, would be perfect in case of the example I gave of hours of operation. (as if inverting the parameters, it was worth the "turn" outside the numbering, starting from 15 and going to positive infinity, and from negative infinity ending in 5)
In the end, what counts is documentation . And when it's up to date;)
Because always from minor (10) to major (15).
BETWEEN begin_expression AND end_expression
SELECT [Product ID], [Product Name] FROM Products WHERE [Units In Stock] BETWEEN '0' AND '35'
Your range is incorrect .
There is no range from 15 to 10.
BETWEEN is used to select values in a range.
If your goal is to select records that are not in this range you should use NOT BETWEEN so it will bring up all the NOT records in your range (remembering that the initial range should be less than final range).
It's just a pattern.
But I believe that this was standardized because the inverse would give the same result.
It would be redundant and would generate process costs by implementing in the compiler which parameter is greater or less.
As simple and imperceptible as the performance gain is, saving 00000000000000.1s is a good economy.