mysql between question

Question

For the mysql "between" operator, is it necessary for the before and after value to be numerically in order?

like:

BETWEEN -10 AND 10
BETWEEN 10 AND -10

Will both of these work or just the first one?

Also, can I do:

WHERE thing<10 AND thing>-10

Will that work or do I have to use between?

Lastly, can I do:

WHERE -10<thing<10

?

Answer 1

I've already seen such things work with integers : WHERE -10

But it's better to avoid it. One reason is that it doesn't seem to work well with other types. And MySQL doesn't issue any warning. I've tried it with datetime columns, and the result was wrong.

My request looked like this one:

SELECT * FROM FACT__MODULATION_CONSTRAINTS constraints WHERE constraints.START_VALIDITY<= now() < constraints.END_VALIDITY

The result was not as expected. I got twice as many results as the same request with two inequalities (which returned correct results). Only the 1st part of the expression evaluated correctly.

Answer 2

Yes your between must be in order to return the excepted result.

Let's say you have a table with a row called mynumber that contains 10 rows :

MyNumber
--------
1
2
3
4
5
6
7
8
9
10

So

select * from thistable table where table.myNumber BETWEEN 1 and 5

will return

1
2
3
4
5

but

select * from thistable table where table.myNumber BETWEEN 5 and 1

return nothing.

Your 2nd question : yes it is the same thing. but beware in you example you will have to put <= and >= to be the same as between. if not, in our example, you would get

2
3
4

Hope it help

Answer 3

BETWEEN -10 AND 10

This will match any value from -10 to 10, bounds included.

BETWEEN 10 AND -10

This will never match anything.

WHERE thing<10 AND thing>-10

This will match any value from -10 to 10, bounds excluded.

Also, if thing is a non-deterministic expression, it is evaluated once in case of BETWEEN and twice in case of double inequality:

SELECT  COUNT(*)
FROM    million_records
WHERE   RAND() BETWEEN 0.6 AND 0.8;

will return a value around 200,000;

SELECT  COUNT(*)
FROM    million_records
WHERE   RAND() >= 0.6 AND RAND() <= 0.8;

will return a value around 320,000

Answer 4

First question:

Will both of these work or just the first one?

yes,both of these work

Second question:

Will that work or do I have to use between?

it also valid but as you can see just empty result

Answer 5

Please keep it to one question per post. Anyway:

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between

BETWEEN min AND max, in that order.

from the link:

This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type

The second alternative will also work, of course.

Answer 6

The min value must come before the max value. Also note that the end points are included, so BETWEEN is equivalent to:

WHERE thing>=-10 AND thing<=10