Topic 16 of 52
BETWEEN AND
Overview
BETWEEN provides an inclusive range filter that is cleaner and more readable than using two separate comparison operators. It works with numbers, dates, and strings.
Syntax
sql
-- Numeric range (inclusive on both ends)
WHERE price BETWEEN 1000 AND 5000
-- Equivalent to: WHERE price >= 1000 AND price <= 5000
-- Date range
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31'
-- Time-based (TIMESTAMP)
WHERE created_at BETWEEN '2025-06-01 00:00:00' AND '2025-06-30 23:59:59'
-- NOT BETWEEN
WHERE price NOT BETWEEN 1000 AND 5000
-- String range (alphabetical)
WHERE last_name BETWEEN 'A' AND 'M' -- surnames A through M
-- BETWEEN with columns
WHERE discount BETWEEN min_discount AND max_discountCommon Pitfalls
- BETWEEN is INCLUSIVE on both ends — BETWEEN 1 AND 10 includes both 1 and 10.
- For date ranges, be careful with timestamps — BETWEEN '2025-06-30' AND '2025-06-30' only matches the midnight timestamp, not the full day.
- Interview tip: For date-range queries, prefer >= and < over BETWEEN to avoid timestamp edge cases: WHERE date >= '2025-01-01' AND date < '2026-01-01'.
Real-World Example
Sales report for a date range and price tier analysis:
example
sql
-- Q2 2025 sales report
SELECT
DATE_TRUNC('week', o.created_at) AS week,
COUNT(*) AS orders,
SUM(o.total_amount) AS revenue
FROM orders o
WHERE
o.created_at BETWEEN '2025-04-01' AND '2025-06-30 23:59:59'
AND o.status = 'completed'
GROUP BY week
ORDER BY week;
-- Product price tier analysis
SELECT
CASE
WHEN price BETWEEN 0 AND 999 THEN 'Budget (₹0-999)'
WHEN price BETWEEN 1000 AND 4999 THEN 'Mid-Range (₹1K-5K)'
WHEN price BETWEEN 5000 AND 19999 THEN 'Premium (₹5K-20K)'
ELSE 'Luxury (₹20K+)'
END AS price_tier,
COUNT(*) AS product_count,
AVG(price) AS avg_price
FROM products
GROUP BY price_tier
ORDER BY MIN(price);