BETWEEN

Definition

BETWEEN low AND high tests whether a value falls within a range, inclusive of both endpoints — shorthand for value >= low AND value <= high.

Running example — a products(id, name, category, price, stock) table, where stock can be NULL (not yet counted):

sql
SELECT name, price FROM products WHERE price BETWEEN 200 AND 1000;
-- exactly equivalent to:
SELECT name, price FROM products WHERE price >= 200 AND price <= 1000;

A product priced at EXACTLY 200 or EXACTLY 1000 is included — BETWEEN is inclusive on both ends, a detail worth remembering precisely since "between" in everyday English is sometimes read as exclusive.

Edge Cases and Pitfalls

• BETWEEN also works on dates and strings, not just numbers: order_date BETWEEN '2026-01-01' AND '2026-01-31' is a common way to filter to a calendar month — though be careful with TIMESTAMP/DATETIME columns that include a time component, since '2026-01-31' implicitly means midnight at the start of that day, potentially excluding timestamps later on the 31st.
• If low is greater than high (a reversed range), BETWEEN doesn't error — it simply matches nothing, since no value can simultaneously be >= low and <= high under those conditions.
• NOT BETWEEN inverts the test (value < low OR value > high) — also inclusive-boundary-aware, so a value exactly at low or high is excluded by NOT BETWEEN.

Key Takeaways

• BETWEEN low AND high is shorthand for >= low AND <= high — inclusive of both endpoints.
• It works on numbers, dates, and strings alike.
• A reversed range (low > high) silently matches zero rows rather than erroring.