BETWEEN demands arguments in ascending order? Why?

Question

I have the following dummy table.

I created the following query:

SELECT * FROM ALUNOS
WHERE IDADE BETWEEN 10 AND 15

Students A, C and D return.

Now, because when I invert the order in the BETWEEN

SELECT * FROM ALUNOS
WHERE IDADE BETWEEN 15 AND 10

Returns nothing?

Answer 1

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.

What is not true if it is a trade that opens from 22h to 6h in the morning. Between 6h and 22h it will be closed. Between 22h and 6h will be open. In other words, concepts are often relativized.

That said, let’s answer the question:

It is so, because who defined the function wanted so. The interpretation of the creator(s) of the function is that the data will be in ascending order. It’s not about them having a better mind than you or the other way around. If you create a language and define that your BETWEEN will work independent of parameter order, I believe you will be right too.

Anyway, if you need to know independent order, just do this:

a BETWEEN( b, c ) OR a BETWEEN( c, b )

It may not be as elegant as calling a function only, but in return, knowing the "limitation" of the function, it may be useful at some point when the order is important.

Exaggerating: I could make a third language with a BETWEEN that gave inverted result if the parameters were reversed, it would be perfect in the case of the example I gave of working hours. 10 BETWEEN( 5, 15 ) => true and 20 BETWEEN ( 15, 5 ) => true (as if reversing the parameters, worth the "turn" outside the numbering, starting from 15 and going to the positive infinity, and from the negative infinity ending in 5)

In the end, what counts is documentation. And when it’s up to date ;)

Answer 2

Because it is at all times of minor (10) to the greater (15).

BETWEEN begin_expression AND end_expression

https://technet.microsoft.com/pt-br/library/ms174081(v=sql.110). aspx

Example

SELECT [Product ID], [Product Name] FROM Products WHERE [Units In Stock] BETWEEN '0' AND '35'

Answer 3

It’s just a pattern.

But I believe it was so standardized because the reverse would give the same result.

It would be redundant and generate process costs when implementing in the compiler which parameter is higher or lower.

However simple and imperceptible the gain in performance is, saving 00000000000000.1s is a good saving.

Answer 4

Your break is incorrect.

There is no interval 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 all the records that NAY are in their interval (remembering that the initial interval should be less than the final interval).