Why Does MySQL's `BETWEEN` Clause Exclude the Upper Bound?

MySQL's "Between" Enigma: Why It Excludes the Ending Value

When executing a query using the "between" clause, users may encounter an unexpected exclusion of the ending value. This behavior, as illustrated by the query:

select * from person where dob between '2011-01-01' and '2011-01-31'

which returns results from '2011-01-01' to '2011-01-30', omitting records with '2011-01-31'.

To understand this behavior, we delve into the MySQL documentation, which clarifies that:

"This is equivalent to the expression
(min <= expr AND expr <= max) "

In this case, the "min" is '2011-01-01' and the "max" is '2011-01-31'. The expression "(min <= expr AND expr <= max)" evaluates to false for values equal to "max", hence the exclusion of '2011-01-31' from the results.

The above is the detailed content of Why Does MySQL's `BETWEEN` Clause Exclude the Upper Bound?. For more information, please follow other related articles on the PHP Chinese website!