BETWEEN Operator
SQL · Filtering Rows
Theory
BETWEEN checks whether a value falls inside a range, including both ends. salary BETWEEN 50000 AND 75000 is exactly the same as salary >= 50000 AND salary <= 75000, so ₹50,000 and ₹75,000 both match. NOT BETWEEN keeps values outside the range.
The low value must come first: BETWEEN 75000 AND 50000 matches nothing. It works with numbers, dates and text (text uses alphabetical order). With timestamps be careful: order_date BETWEEN '2024-01-01' AND '2024-01-31' misses times later on 31 January, because '2024-01-31' means midnight. For time ranges the safer pattern is order_date >= '2024-01-01' AND order_date < '2024-02-01'. A NULL value never matches.
Why we use it
Range filters (salary bands, date windows, score ranges) are very common. BETWEEN states them in a short, readable way and avoids writing two comparisons joined by AND.
When to use it
Use BETWEEN for inclusive ranges on numbers or plain dates. For timestamps or when you need an exclusive upper bound, use >= and < instead. For a fixed list of values, use IN.
How it works
- Read a row
- Compare the value with the low bound
- Is value >= low? Yes: Compare with the high bound. No: Discard the row.
- Is value <= high? Yes: Keep the row (ends included). No: Discard the row.
- Move to the next row
Syntax
SELECT columns FROM table
WHERE column BETWEEN val1 AND val2;Example
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000;Key points
- Inclusive of both boundary values
- Works on numbers, dates and text
- Write the low value first, then the high value
- For timestamps prefer >= start AND < next_day
- NOT BETWEEN keeps values outside the range