Is MySQL's `BETWEEN` Clause Inclusive of the Upper Bound?

MySQL "between" Clause: Inclusivity Considered

While utilizing the "between" clause in MySQL queries, it's essential to consider its non-inclusive nature regarding the ending value. For instance, executing a query like:

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

would retrieve results with dates of birth (DOB) ranging from '2011-01-01' to '2011-01-30', excluding records with a DOB of '2011-01-31'.

This behavior can be attributed to MySQL's interpretation of the "between" clause as a logical expression resembling:

(min <= expr AND expr <= max)

To account for this and include records where DOB is '2011-01-31', the query can be modified in two ways:

Method 1: Using Comparison Operators

By employing comparison operators, the query can explicitly check for both upper and lower bounds:

select * from person where dob >= '2011-01-01' and dob <= '2011-01-31'

Method 2: Utilizing the NOT BETWEEN Operator

Alternatively, the "NOT BETWEEN" operator can be used to exclude a specific range while including the desired endpoint:

select * from person where dob NOT BETWEEN '2011-01-02' and '2011-01-30'

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