How does MySQL implement the `BETWEEN`?

Question

Are the range boundries included in MySQL's between? For example if I do

date between '2013/12/02' AND '2013/12/01'

will they be included as well? Also which one is better? either I should use BETWEEN or should I rely on <= and >=?

h2ooooooo · Accepted Answer · 2013-12-07T14:04:56.653

0

As per the manual:

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. Otherwise type conversion takes place according to the rules described in Section 12.2, “Type Conversion in Expression Evaluation”, but applied to all the three arguments.

edited Dec 07 '13 at 14:04

answered Dec 07 '13 at 13:51

h2ooooooo

39,111
8
68
102

Why do you think there's a mistake in the manual? Looks fine to me. – Matt Gibson Dec 07 '13 at 13:55
1

@MattGibson Because "If expr is greater than or equal to min and expr is less than or equal to max" is not the same as "MIN less-than-or-equal-to EXPR && MAX less-than-or-equal-to EXPR." **Ah**. Of course you're right. Stop the presses. Didn't realize that it wasn't `min <= expr AND max <= expr`. – h2ooooooo Dec 07 '13 at 14:04

score 0 · Answer 2 · edited May 23 '17 at 11:49

0

Between has better efficiency. It fixes an issue where MYSQL may have unneeded reevaluations of the conditions.

See this answer: Why use the BETWEEN operator when we can do without it?

edited May 23 '17 at 11:49

Community

1
1

answered Dec 07 '13 at 13:51

DaBaer

204
1
7

score 0 · Answer 3 · answered Dec 07 '13 at 14:09

If your date values are actually datetime values, and the time component is not always '00:00:00', then neither suggestion in the question is good. You want a variation of the second one. Specifically, you want

where yourfield >= your start date
and your field < the day after your end date

How does MySQL implement the `BETWEEN`?

3 Answers3