Offensive Security Researcher & Quantum Cryptography Analyst

How to Use the BETWEEN Operator in MySQL Database

Abdul Wahab Junaid
How to Use the BETWEEN Operator in MySQL Database

BETWEEN is one of the first operators I teach anyone learning SQL, because it reads almost like plain English — and yet there are enough subtle behaviors around inclusivity, data types, and indexing that I still see experienced developers get tripped up by it. In this guide, I’ll cover the operator from the ground up, including the internals of how MySQL evaluates range conditions, so you understand not just how to write BETWEEN but when it’s the right choice at all.

Basic Syntax

SELECT id, name, price
FROM products
WHERE price BETWEEN 20 AND 50;

This is functionally equivalent to:

SELECT id, name, price
FROM products
WHERE price >= 20 AND price <= 50;

The most important thing to internalize immediately: BETWEEN is inclusive on both ends. A product priced at exactly 20 or exactly 50 is included in the results. I’ve seen this assumption get missed constantly by developers coming from languages where range functions are often exclusive on one end.

Using BETWEEN with Dates

This is probably where I use BETWEEN most often in real applications — filtering records within a date range.

SELECT id, order_date, total
FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';

Here’s the gotcha I warn every junior developer about: if order_date is a DATETIME column and contains time components, this query will miss orders that happened later on January 31st, because '2026-01-31' is implicitly treated as '2026-01-31 00:00:00'.

-- This misses anything after midnight on Jan 31st
SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';

-- Correct approach for DATETIME columns
SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01 00:00:00' AND '2026-01-31 23:59:59';

-- Even safer — avoids any fractional-second edge cases
SELECT * FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2026-02-01';

I actually prefer that last form — using >= and < with the start of the next period — over BETWEEN specifically for date ranges, because it sidesteps the inclusivity ambiguity entirely and handles any fractional seconds or timezone edge cases cleanly.

Using BETWEEN with Numbers

SELECT id, name, stock
FROM products
WHERE stock BETWEEN 10 AND 100;

I use this constantly for things like inventory threshold reports — finding products that are neither critically low nor overstocked.

Using NOT BETWEEN

SELECT id, name, price
FROM products
WHERE price NOT BETWEEN 20 AND 50;

This returns rows where price < 20 OR price > 50 — everything outside the range, again inclusive of the boundary exclusion (20 and 50 themselves are excluded from the results since they fall inside the excluded range).

Using BETWEEN with Strings

BETWEEN also works on string/text columns, comparing them based on the column’s collation:

SELECT id, name
FROM products
WHERE name BETWEEN 'A' AND 'M';

This returns products whose names fall alphabetically between “A” and “M” — though I use this pattern less often since it can behave unintuitively with case sensitivity and multi-word strings depending on collation rules. I usually prefer more explicit range logic or LIKE patterns for string matching instead.

How MySQL Evaluates BETWEEN Internally

graph TD
    A[WHERE column BETWEEN low AND high] --> B{Is column indexed?}
    B -->|Yes| C[Range scan using index]
    B -->|No| D[Full table scan, evaluate condition per row]
    C --> E[MySQL walks index between low and high bounds]
    D --> F[Every row checked individually - slow on large tables]

MySQL internally rewrites BETWEEN as the equivalent >= and <= conditions before optimization, so if the column has an index, MySQL performs a range scan — jumping directly to the starting bound in the index (a B-tree structure) and reading sequentially until it passes the upper bound, rather than examining every row in the table.

Verifying Index Usage with EXPLAIN

EXPLAIN SELECT id, name, price FROM products WHERE price BETWEEN 20 AND 50;

+----+-------------+----------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table    | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+----------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | products | range | idx_price     | idx_price | 5       | NULL | 1200 | Using where |
+----+-------------+----------+-------+---------------+-----------+---------+------+------+-------------+

type: range confirms MySQL is using the index efficiently for this bounded query, rather than scanning the whole table (type: ALL).

If no index exists on price, I’d add one:

CREATE INDEX idx_price ON products (price);

BETWEEN in Composite Index Scenarios

Range conditions have an important quirk in composite indexes that I always keep in mind: once a column in a composite index is used in a range condition (like BETWEEN), MySQL can’t efficiently use subsequent columns in that same index for further filtering or sorting in the same way it could with equality conditions.

CREATE INDEX idx_category_price_stock ON products (category_id, price, stock);

SELECT id, name FROM products
WHERE category_id = 5 AND price BETWEEN 20 AND 50 AND stock > 10;

Here, category_id = 5 (equality) and price BETWEEN 20 AND 50 (range) both use the index effectively, but the stock > 10 condition after the range column gets checked as a filter rather than as part of the index seek itself — this is often called the “range column limits index usage” behavior, and it’s a common surprise for developers who assume every column in a composite index gets equal treatment.

graph LR
    A[category_id = 5] -->|Equality - full index benefit| B[price BETWEEN 20 AND 50]
    B -->|Range - index benefit stops here| C[stock > 10]
    C -->|Evaluated as filter, not index seek| D[Result rows]

Real-World Scenario: A Reporting Dashboard

I built a dashboard widget showing “orders placed this quarter within a specific revenue range.” The initial query used BETWEEN on both a DATETIME column and a DECIMAL revenue column with a composite index covering both. Performance was fine at first, but as the orders table grew past a few million rows, I noticed the query started degrading. Running EXPLAIN, I found the date range (a BETWEEN on order_date) was listed first in the composite index, which meant the second range condition (on total) wasn’t getting the same index efficiency. Reordering the index to put the more selective equality-style filter (a specific region_id) first, followed by the date range, and moving the revenue range into a post-filter step resolved the slowdown.

Security Considerations

BETWEEN itself isn’t inherently a security risk, but I always apply the same rule as any other WHERE clause: never concatenate user-supplied range values directly into a query string.

// Never do this
const query = `SELECT * FROM products WHERE price BETWEEN ${minPrice} AND ${maxPrice}`;

// Always parameterize
const query = 'SELECT * FROM products WHERE price BETWEEN ? AND ?';
connection.query(query, [minPrice, maxPrice]);

Even though numeric injection risk is lower than string injection, I treat every user input the same way, without exception — parameterized, always.

Performance Tips

  1. Index the column used in BETWEEN if it’s used in WHERE clauses on tables of meaningful size.
  2. Prefer >= / < over BETWEEN for date ranges spanning a full day/period, to avoid inclusivity mistakes with time components.
  3. Be aware of the “range column” limitation in composite indexes — put equality filters before range filters in the index definition.
  4. Validate input ranges at the application layer — swap minPrice/maxPrice if a user submits them backwards, since BETWEEN low AND high returns no rows if low > high.
  5. Use EXPLAIN to confirm type: range rather than type: ALL on any BETWEEN query against a large table.

Troubleshooting Common Issues

ProblemLikely CauseFix
Query returns no results despite matching datalow > high swapped, or time component mismatch on DATETIMEValidate range order; use full timestamp bounds
BETWEEN on DATETIME misses end-of-day recordsImplicit midnight truncation on the date literalUse explicit 23:59:59 or < next_day pattern
Slow performance despite an index existingRange condition limiting composite index effectiveness on later columnsReorder composite index, put equality columns first
Unexpected results on string rangesCollation/case sensitivity in comparisonUse explicit COLLATE or switch to LIKE/explicit bounds

Frequently Asked Questions

Is BETWEEN inclusive or exclusive? Inclusive on both ends — price BETWEEN 20 AND 50 includes rows where price is exactly 20 or exactly 50.

Is BETWEEN slower than writing >= and <= manually? No — MySQL treats them identically at the optimizer level; it’s purely a readability choice.

Can I use BETWEEN with subqueries as the bounds? Yes: WHERE price BETWEEN (SELECT MIN(price) FROM budget_tier) AND (SELECT MAX(price) FROM budget_tier) — though I usually pull those values into variables first for clarity and to avoid running the subqueries per row in some execution plans.

Why did my date range query miss some rows? Almost always the DATETIME truncation issue described above — the end date literal is treated as midnight, not end-of-day.

Interview Questions on This Topic

  1. Is BETWEEN inclusive or exclusive of its boundary values?
  2. Why might a BETWEEN query on a DATETIME column miss records from the last day of the range?
  3. How does MySQL’s optimizer treat BETWEEN internally, and how does that affect index usage?
  4. What is the “range column” limitation in composite indexes, and how does it affect query performance?
  5. Why would you choose >= AND < over BETWEEN for a date range query?

Key Takeaways

References

Exit mobile version