Practice Now
MySQL
SQL
Database
Coding
Programming

MySQL between clause not inclusive?

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Databases

Introduction

MySQL's BETWEEN clause is inclusive on both ends — it includes rows where the column value equals either boundary. WHERE x BETWEEN 1 AND 10 is equivalent to WHERE x >= 1 AND x <= 10. The common misconception that BETWEEN is not inclusive usually stems from datetime comparisons where the time component causes unexpected filtering. A DATE column compared with BETWEEN '2025-01-01' AND '2025-01-31' works as expected, but a DATETIME column misses records after midnight on January 31st because '2025-01-31' is interpreted as '2025-01-31 00:00:00'.

BETWEEN Is Inclusive

sql
1-- BETWEEN includes both endpoints
2SELECT * FROM products WHERE price BETWEEN 10 AND 50;
3-- Equivalent to:
4SELECT * FROM products WHERE price >= 10 AND price <= 50;
5
6-- Both 10 and 50 are included in results
sql
1-- Verify with a simple example
2SELECT 5 BETWEEN 1 AND 10;   -- 1 (true)
3SELECT 1 BETWEEN 1 AND 10;   -- 1 (true — lower bound included)
4SELECT 10 BETWEEN 1 AND 10;  -- 1 (true — upper bound included)
5SELECT 11 BETWEEN 1 AND 10;  -- 0 (false)

The DATETIME Problem

This is where BETWEEN appears non-inclusive:

sql
1-- Table with DATETIME column
2CREATE TABLE orders (
3    id INT AUTO_INCREMENT PRIMARY KEY,
4    created_at DATETIME,
5    amount DECIMAL(10,2)
6);
7
8INSERT INTO orders (created_at, amount) VALUES
9('2025-01-15 10:30:00', 100),
10('2025-01-31 08:00:00', 200),
11('2025-01-31 14:30:00', 300),  -- This gets MISSED
12('2025-01-31 23:59:59', 400);  -- This gets MISSED
13
14-- Seems like it should get all January orders...
15SELECT * FROM orders
16WHERE created_at BETWEEN '2025-01-01' AND '2025-01-31';

The query above misses orders after midnight on January 31st because '2025-01-31' is cast to '2025-01-31 00:00:00'. Only the 08:00:00 record at id=2 would be missed too — actually, all records on Jan 31 after 00:00:00 are excluded except exactly at midnight.

sql
-- What MySQL actually evaluates:
WHERE created_at >= '2025-01-01 00:00:00' AND created_at <= '2025-01-31 00:00:00'
-- Records at 08:00, 14:30, 23:59 on Jan 31 are EXCLUDED

Fix 1: Use the Next Day as Upper Bound

sql
1-- Include all of January 31st
2SELECT * FROM orders
3WHERE created_at BETWEEN '2025-01-01' AND '2025-02-01';
4-- But this includes midnight of Feb 1 (00:00:00)
5
6-- Better: use less-than for the upper bound
7SELECT * FROM orders
8WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';

The >= and < pattern is the standard approach for datetime ranges. It includes everything up to but not including the next day.

Fix 2: Use Explicit Time in BETWEEN

sql
-- Include the full last day
SELECT * FROM orders
WHERE created_at BETWEEN '2025-01-01 00:00:00' AND '2025-01-31 23:59:59';

This works for DATETIME (1-second precision) but misses fractional seconds if using DATETIME(6). For microsecond precision, use '2025-01-31 23:59:59.999999'.

Fix 3: Use DATE() Function

sql
-- Cast DATETIME to DATE for comparison
SELECT * FROM orders
WHERE DATE(created_at) BETWEEN '2025-01-01' AND '2025-01-31';

DATE() strips the time component, so all records on January 31st are included. However, this prevents MySQL from using an index on created_at — the function is applied to every row (full table scan).

Fix 4: Use DATE Column Type

If you only need date precision, use the DATE type instead of DATETIME:

sql
1CREATE TABLE events (
2    id INT AUTO_INCREMENT PRIMARY KEY,
3    event_date DATE,
4    name VARCHAR(100)
5);
6
7-- BETWEEN works as expected with DATE columns
8SELECT * FROM events
9WHERE event_date BETWEEN '2025-01-01' AND '2025-01-31';
10-- Includes all dates from Jan 1 through Jan 31

BETWEEN with Integers

sql
1-- Integers: fully inclusive, no surprises
2SELECT * FROM employees WHERE age BETWEEN 25 AND 35;
3-- Returns employees aged 25, 26, 27, ..., 34, 35
4
5SELECT * FROM products WHERE id BETWEEN 100 AND 200;
6-- Returns 101 rows (100 through 200 inclusive)

Integer BETWEEN behaves exactly as expected — both boundaries are included.

BETWEEN with Strings

sql
1-- String BETWEEN uses lexicographic (alphabetical) comparison
2SELECT * FROM users WHERE last_name BETWEEN 'A' AND 'M';
3-- Includes 'Anderson', 'Baker', 'Lee', 'M' (exactly)
4-- Excludes 'Martin' (because 'Martin' > 'M')
5
6-- To include all names starting with M:
7SELECT * FROM users WHERE last_name BETWEEN 'A' AND 'N';
8-- Or use LIKE:
9SELECT * FROM users WHERE last_name >= 'A' AND last_name < 'N';

String comparison is case-sensitive depending on the column's collation. With utf8_general_ci (case-insensitive), 'a' equals 'A'.

NOT BETWEEN

sql
1-- Exclude a range
2SELECT * FROM products WHERE price NOT BETWEEN 10 AND 50;
3-- Equivalent to:
4SELECT * FROM products WHERE price < 10 OR price > 50;
5-- Excludes 10 and 50 (boundaries are part of the excluded range)

Index Usage with BETWEEN

sql
1-- BETWEEN uses indexes efficiently
2CREATE INDEX idx_price ON products(price);
3
4-- This query uses the index for range scan
5EXPLAIN SELECT * FROM products WHERE price BETWEEN 10 AND 50;
6-- type: range, key: idx_price
7
8-- But DATE() function prevents index use
9EXPLAIN SELECT * FROM orders WHERE DATE(created_at) BETWEEN '2025-01-01' AND '2025-01-31';
10-- type: ALL (full scan — DATE() wraps the column)
11
12-- This uses the index:
13EXPLAIN SELECT * FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';
14-- type: range, key: idx_created_at

Common Pitfalls

  • DATETIME implicit cast: BETWEEN '2025-01-01' AND '2025-01-31' on a DATETIME column casts the strings to '2025-01-01 00:00:00' and '2025-01-31 00:00:00', excluding most of January 31st. Use >= AND < with the next day instead.
  • Using DATE() in WHERE: WHERE DATE(created_at) BETWEEN ... prevents index usage because MySQL cannot use a B-tree index when the column is wrapped in a function. Use range comparisons on the raw column.
  • BETWEEN with NULL: If the column contains NULL, BETWEEN returns NULL (not TRUE or FALSE). NULL rows are excluded from results. Use IS NULL separately if needed: WHERE (x BETWEEN 1 AND 10) OR x IS NULL.
  • Reversed boundaries: BETWEEN 10 AND 1 returns no rows — the lower bound must be less than or equal to the upper bound. MySQL does not swap them automatically.
  • Microsecond precision: DATETIME(6) stores microseconds. BETWEEN '2025-01-31 00:00:00' AND '2025-01-31 23:59:59' misses records with fractional seconds like 23:59:59.500000. Use < '2025-02-01' to be safe.

Summary

  • MySQL BETWEEN is inclusive on both ends: x BETWEEN a AND b means x >= a AND x <= b
  • The "not inclusive" issue is almost always a DATETIME problem — date strings cast to midnight
  • Use WHERE col >= start AND col < next_day for datetime ranges
  • Avoid DATE() in WHERE clauses — it prevents index usage
  • BETWEEN works correctly for integers, dates (DATE type), and strings
  • Always specify explicit timestamps when comparing against DATETIME/TIMESTAMP columns
Related reading
Course
Beginner
27 lessons
10 hours
System Design Fundamentals

Build a strong foundation in designing scalable, reliable distributed systems.

View the course
Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Codemia
© 2026 Codemia
Resources
Legal
Social
All Rights Reserved.