BETWEEN usage and examples

Overview

BETWEEN expresses an inclusive range and is equivalent to using >= for the lower bound and <= for the upper bound. It works well for amounts, identifiers, and complete dates. For timestamps, an upper value such as 23:59:59 is fragile, so prefer a half-open range from the start through, but not including, the next period. Reversed bounds are not automatically swapped.

Basic syntax

SELECT * FROM orders WHERE amount BETWEEN 500 AND 1500;

Examples

BETWEEN example

Return orders whose amount is between 500 and 1500 and verify that both boundary values are included.

Sample table

Table：orders

orders
id	customer_id	product_id	name	department	amount	status	created_at	discount
1	101	201	Alice	Sales	1200	paid	2026-01-03	0
2	102	202	Bob	IT	900	pending	2026-01-08	50
3	101	201	Cindy	Sales	1500	paid	2026-02-02	100
4	104	203	David	HR	700	cancelled	2026-02-15	0

Run result

result
id	customer_id	product_id	name	department	amount	status	created_at	discount
1	101	201	Alice	Sales	1200	paid	2026-01-03	0
2	102	202	Bob	IT	900	pending	2026-01-08	50
3	101	201	Cindy	Sales	1500	paid	2026-02-02	100
4	104	203	David	HR	700	cancelled	2026-02-15	0

Common issues

Are boundary values included?

Check the operator precisely: BETWEEN includes both ends, while adding or omitting equals changes a range.

How do I avoid mistakes with AND and OR?

Use parentheses to express business groups instead of relying on remembered precedence.

Why does a condition not match NULL?

NULL represents unknown, so a comparison may not be TRUE; use IS NULL or an explicit NULL strategy.

Keep reading

SELECT WHERE DISTINCT ORDER BY LIMIT LIKE OFFSET IS NULL

BETWEEN