Python SQL BETWEEN: Range Filtering for Numbers, Dates & Text
The `BETWEEN` operator tests whether an expression falls within an inclusive range (both begin and end values included). It works seamlessly with numbers, dates, timestamps, and alphabetical text strings.
"`BETWEEN 10 AND 20` is like setting two bookends on a shelf at position 10 and position 20: all books between them (including the ones at 10 and 20) are selected."
Deep Dive: How It Works
Inclusive Semantics: `WHERE age BETWEEN 18 AND 30` is completely equivalent to `WHERE age >= 18 AND age <= 30`.
Value Ordering: In `BETWEEN min AND max`, `min` must be less than or equal to `max`. Writing `BETWEEN 30 AND 18` always returns false in standard SQL.
Date & Timestamp Caution: `BETWEEN '2026-01-01' AND '2026-01-31'` on a TIMESTAMP column defaults to `2026-01-31 00:00:00`, missing all records created later that afternoon!
Syntax Blueprint
SELECT column1 FROM table_name WHERE column1 BETWEEN min_value AND max_value;
BETWEEN is inclusive of both boundary values (min_value and max_value).
Core Rules to Remember
Common Beginner Traps & How to Fix Them
Using BETWEEN for datetime ranges on TIMESTAMP columns.Why it happens: Records at `2026-01-31 14:30:00` are greater than `2026-01-31 00:00:00`.
How to fix: Use half-open intervals: `WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01'`.
Live Interactive Example
Hit Run Code to see it liveYour Turn: Micro Challenge
No pressure! Edit the starter code below and test your solution with instant feedback.
Filter Prices with BETWEEN
- Select `name`, `price` from `items` where `price BETWEEN 20.0 AND 50.0`.
Finished reading and practicing?
Mark this lesson as completed to update your course progress.