MySQL BETWEEN Operator

MySQL BETWEEN Operator: Inclusive Range Filtering

The MySQL BETWEEN operator filters column values that fall within a continuous range defined by a start value and an end value. Both endpoints are inclusive, meaning BETWEEN a AND b matches every value from a up through b.

Basic Syntax

SELECT columns
FROM table_name
WHERE column_name BETWEEN start_value AND end_value;

You can negate the range using NOT BETWEEN start_value AND end_value to select rows strictly outside both bounds. Keep in mind that NULL values evaluate to unknown and never match either BETWEEN or NOT BETWEEN.

Practical SQL Examples

This query finds mid-priced products in demo_products between 25 and 150 dollars, and shows how to filter a DATE or DATETIME column safely:

-- Inclusive numeric range ($25.00 through $150.00)
SELECT id, product_name, price
FROM demo_products
WHERE price BETWEEN 25.00 AND 150.00
ORDER BY price ASC;

-- Safe half-open interval for DATETIME columns
SELECT id, customer_id, total_amount, created_at
FROM demo_orders
WHERE created_at >= '2026-08-01 00:00:00'
  AND created_at <  '2026-09-01 00:00:00';

Common Mistakes and Best Practices

• Watch the DATETIME Midnight Trap: Writing created_at BETWEEN '2026-08-01' AND '2026-08-31' treats '2026-08-31' as '2026-08-31 00:00:00', silently dropping every order placed after midnight on August 31! Always use half-open intervals starting at midnight of the first day and ending strictly before midnight of the next month.
• Put the Smaller Value First: Writing WHERE price BETWEEN 150 AND 25 always returns zero rows because no number can be at least 150 and at most 25 simultaneously.

Frequently Asked Questions

Are the boundary values included when using BETWEEN?

Yes, BETWEEN is inclusive on both ends, while NOT BETWEEN excludes both boundaries.

Can I use BETWEEN with text strings?

Yes, BETWEEN 'A' AND 'M' filters alphabetically using the column's collation, though 'M' excludes 'Ma' because 'Ma' sorts after 'M'.

Does BETWEEN use B-tree indexes efficiently?

Yes, BETWEEN compiles directly to a standard index range scan just like explicit lower and upper comparison bounds.