MySQL BETWEEN Operator: Range Filters for Numbers, Text

BETWEEN low AND high is true when a value is between the two limits, both included. It is the same as >= low AND <= high.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    added_on DATE NOT NULL
);

INSERT INTO products (name, category, price, stock, added_on) VALUES
    ('Notebook',      'stationery',  80.00, 120, '2026-01-10'),
    ('Gel Pen',       'stationery',  15.50, 300, '2026-01-12'),
    ('Backpack',      'bags',       999.00,  25, '2026-02-01'),
    ('Marker Set',    'stationery', 145.00,   0, '2026-02-15'),
    ('Desk Lamp',     'home',       650.00,  40, '2026-03-03'),
    ('Water Bottle',  'home',       220.00,  75, '2026-03-20'),
    ('Laptop Sleeve', 'bags',       450.00,  18, '2026-04-02'),
    ('Sticky Notes',  'stationery',  35.00, 210, '2026-04-18');

query.sql

SQL

SELECT name, price FROM products WHERE price BETWEEN 100 AND 500 ORDER BY price;

Output

OUTPUT

+---------------+--------+
| name          | price  |
+---------------+--------+
| Marker Set    | 145.00 |
| Water Bottle  | 220.00 |
| Laptop Sleeve | 450.00 |
+---------------+--------+
3 rows in set

Products costing exactly 100 or exactly 500 would be included. The first number must be the smaller one:

query.sql

SQL

SELECT name, price FROM products WHERE price BETWEEN 500 AND 100;

Output

OUTPUT

Empty set

BETWEEN 500 AND 100 means >= 500 AND <= 100, which nothing satisfies. No error, only an empty result.

NOT BETWEEN

query.sql

SQL

SELECT name, price FROM products WHERE price NOT BETWEEN 50 AND 500 ORDER BY price;

Output

OUTPUT

+--------------+--------+
| name         | price  |
+--------------+--------+
| Gel Pen      |  15.50 |
| Sticky Notes |  35.00 |
| Desk Lamp    | 650.00 |
| Backpack     | 999.00 |
+--------------+--------+
4 rows in set

Dates

query.sql

SQL

SELECT name, added_on FROM products WHERE added_on BETWEEN '2026-02-01' AND '2026-03-31' ORDER BY added_on;

Output

OUTPUT

+--------------+------------+
| name         | added_on   |
+--------------+------------+
| Backpack     | 2026-02-01 |
| Marker Set   | 2026-02-15 |
| Desk Lamp    | 2026-03-03 |
| Water Bottle | 2026-03-20 |
+--------------+------------+
4 rows in set

The DATETIME trap

When a column has a time, BETWEEN with two dates misses most of the last day. A date with no time means midnight (00:00:00):

query.sql

SQL

CREATE TABLE events (id INT AUTO_INCREMENT PRIMARY KEY, happened_at DATETIME NOT NULL);
INSERT INTO events (happened_at) VALUES ('2026-03-31 09:00:00'), ('2026-03-31 18:30:00'), ('2026-04-01 00:00:00');

SELECT happened_at FROM events WHERE happened_at BETWEEN '2026-03-01' AND '2026-03-31';

Output

OUTPUT

Empty set

'2026-03-31' is read as '2026-03-31 00:00:00', so both events later that day are missing. The reliable way is a half-open range: from the start (included) to the next period’s start (excluded):

query.sql

SQL

SELECT happened_at FROM events WHERE happened_at >= '2026-03-01' AND happened_at < '2026-04-01' ORDER BY happened_at;

Output

OUTPUT

+---------------------+
| happened_at         |
+---------------------+
| 2026-03-31 09:00:00 |
| 2026-03-31 18:30:00 |
+---------------------+
2 rows in set

Use this pattern for every date range on a DATETIME or TIMESTAMP column. It never loses a row, and it works with fractional seconds too.

Text

BETWEEN also works on text, using the collation’s sort order:

query.sql

SQL

SELECT name FROM products WHERE name BETWEEN 'B' AND 'N' ORDER BY name;

Output

OUTPUT

+---------------+
| name          |
+---------------+
| Backpack      |
| Desk Lamp     |
| Gel Pen       |
| Laptop Sleeve |
| Marker Set    |
+---------------+
5 rows in set

'Notebook' is not included, because it sorts after the single letter 'N'. Text ranges are tricky, so use them with care.

Using BETWEEN with columns

Both limits can be columns or expressions:

query.sql

SQL

SELECT name, price FROM products WHERE price BETWEEN stock AND stock * 10 ORDER BY name;

Output

OUTPUT

+--------------+--------+
| name         | price  |
+--------------+--------+
| Water Bottle | 220.00 |
+--------------+--------+
1 row in set

Performance

BETWEEN on an indexed column becomes a range scan, which is efficient. Do not wrap the column in a function (WHERE YEAR(added_on) = 2026), because that disables the index (Sargable Conditions).

warning

Last day of the month

BETWEEN '2026-03-01' AND '2026-03-31' on a DATETIME column is a very common source of missing rows in monthly reports. Use >= first day AND < first day of next month.