BETWEEN Operator
BETWEEN checks whether a value falls within a lower and upper bound.
It is inclusive at both ends.
Core Concepts
| Form | Equivalent logic |
|---|---|
x BETWEEN a AND b | x >= a AND x <= b |
Code Examples
-- Numeric range filter.
SELECT order_id, total
FROM orders_cmp
WHERE total BETWEEN 20 AND 100
ORDER BY total;
| order_id | total |
|---|---|
| 3 | 75.50 |
-- Equivalent expanded comparison.
SELECT order_id, total
FROM orders_cmp
WHERE total >= 20 AND total <= 100
ORDER BY total;
| order_id | total |
|---|---|
| 3 | 75.50 |
SQLite-Specific Nuances
SQLite Nuance
BETWEEN is inclusive in SQLite: boundary values are included.
Common Pitfalls / Best Practices
Pitfall
Assuming BETWEEN excludes endpoints and accidentally missing exact boundary rows.
Best Practice
Use BETWEEN for readability when both boundaries are meaningful and inclusive.
Quick Challenge
Return orders with totals between 19.99 and 80.00 inclusive.
View Solution
SELECT order_id, total
FROM orders_cmp
WHERE total BETWEEN 19.99 AND 80.00
ORDER BY total;
| order_id | total |
|---|---|
| 1 | 19.99 |
| 3 | 75.50 |