BETWEEN Operator

BETWEEN checks whether a value falls within a lower and upper bound.

It is inclusive at both ends.

Core Concepts

flowchart TD
  A[Value] --> B{lower <= value <= upper}
  B -->|True| C[Row included]
  B -->|False| D[Row excluded]

Form	Equivalent logic
`x BETWEEN a AND b`	`x >= a AND x <= b`

-- 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 Nuance

BETWEEN is inclusive in SQLite: boundary values are included.

Pitfall

Assuming BETWEEN excludes endpoints and accidentally missing exact boundary rows.

Best Practice

Use BETWEEN for readability when both boundaries are meaningful and inclusive.

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