Lesson 12 of 61

5 mins read

SQL BETWEEN: Range Filtering for Numbers, Dates & Text

In Plain English

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.

The Mental Model

"`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

Inclusive Boundaries: Both min and max endpoints are included in matching results.

NOT BETWEEN: Selects values outside the designated range: `WHERE score NOT BETWEEN 40 AND 60`.

Index Range Scans: BETWEEN queries utilize B-Tree index range scans for exceptional performance.

Common Beginner Traps & How to Fix Them

Mistake: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'`.

Pro Tip: Always use half-open intervals (`>= start AND < next_month`) for timestamp columns to avoid missing end-of-day records.

Live Interactive Example

Hit Run Code to see it live

Filtering Numerical and Date Ranges with BETWEEN

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: BETWEEN inclusively matched salary boundaries and 2024 date stamps.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Filter Prices with BETWEEN

Select `name`, `price` from `items` where `price BETWEEN 20.0 AND 50.0`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.