BETWEEN for Ranges
Use BETWEEN for inclusive numeric and date ranges, and know when to prefer explicit >= and < comparisons for half-open intervals.
BETWEEN for Ranges is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
BETWEEN Is Inclusive
BETWEEN a AND b matches values where x >= a AND x <= b. Both endpoints are inclusive.
SELECT * FROM orders WHERE total BETWEEN 100 AND 500;
-- equivalent to:
SELECT * FROM orders WHERE total >= 100 AND total <= 500;Date Ranges
BETWEEN works on dates and timestamps too:
SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';The Inclusive-Endpoint Gotcha
When using a TIMESTAMP column, BETWEEN '2024-12-31' ends at midnight on the 31st, NOT the end of the day. Orders placed at 14:00 on Dec 31 are excluded.
-- WRONG — misses orders later on Dec 31:
WHERE created_at BETWEEN '2024-12-01' AND '2024-12-31';
-- RIGHT — half-open interval:
WHERE created_at >= '2024-12-01'
AND created_at < '2025-01-01';Prefer Half-Open Intervals for Time
The half-open pattern >= start AND < next_period is unambiguous, works for any granularity, and chains naturally for consecutive periods.
WHERE created_at >= date_trunc('month', NOW())
AND created_at < date_trunc('month', NOW()) + INTERVAL '1 month';NOT BETWEEN
Inverse of BETWEEN — excludes the range:
SELECT * FROM products WHERE price NOT BETWEEN 10 AND 20;
-- equivalent to: price < 10 OR price > 20Range Queries and Indexes
A B-tree index helps with range queries. Composite indexes use the range column last for best selectivity:
CREATE INDEX orders_user_total_idx ON orders (user_id, total);
-- Efficient: equality on user_id, range on total
SELECT * FROM orders
WHERE user_id = 42 AND total BETWEEN 100 AND 500;Strings and BETWEEN
BETWEEN compares strings lexicographically. Be careful with case:
SELECT * FROM users WHERE last_name BETWEEN 'A' AND 'C';
-- Includes 'Adams' and 'Bond', excludes 'C' itself only if 'C' < 'Ca...'Range Types (PostgreSQL)
PostgreSQL has dedicated range types for half-open intervals:
SELECT int4range(10, 20) @> 15; -- true (15 in [10,20))
SELECT tsrange('2024-01-01', '2025-01-01') @> NOW()::TIMESTAMP;Validating Ranges with CHECK
Make sure start <= end at insert time:
CREATE TABLE bookings (
id BIGSERIAL PRIMARY KEY,
start_at TIMESTAMPTZ NOT NULL,
end_at TIMESTAMPTZ NOT NULL,
CHECK (start_at < end_at)
);Numeric Bucketing
Combine BETWEEN with CASE to build buckets:
SELECT id, total,
CASE
WHEN total BETWEEN 0 AND 99 THEN 'small'
WHEN total BETWEEN 100 AND 999 THEN 'medium'
ELSE 'large'
END AS bucket
FROM orders;BETWEEN SYMMETRIC
SQL standard extension that doesn't care which side is smaller:
SELECT 5 BETWEEN SYMMETRIC 10 AND 1; -- TRUERecap
BETWEEN is convenient inclusive-range shorthand.
- Beware of timestamp endpoints — prefer half-open
>= start AND < end - Composite indexes accelerate range queries
- CHECK constraints enforce valid ranges
Quick Check
Are both endpoints of BETWEEN a AND b inclusive in standard SQL?
Frequently asked questions
Is the “BETWEEN for Ranges” lesson free?
Yes — the full text of “BETWEEN for Ranges” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “BETWEEN for Ranges”?
Use BETWEEN for inclusive numeric and date ranges, and know when to prefer explicit >= and < comparisons for half-open intervals. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “BETWEEN for Ranges” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.