BETWEEN Operator

Overview

The BETWEEN operator checks if an expression falls within a range of values, including both the lower and upper bounds.

Learning Objectives

• Write inclusive boundary filters using BETWEEN.
• Formulate negative ranges with NOT BETWEEN.
• Prevent pitfalls when working with date ranges and time components.

Detailed Concept Explanation

BETWEEN is a shorthand for matching range limits: WHERE price >= 10.0 AND price <= 50.0

Can be written cleanly as: WHERE price BETWEEN 10.0 AND 50.0

Inclusive Bounds

The boundary parameters are inclusive. Both the lower value and upper value are included in the results.

Time Stamp Pitfalls

When comparing dates, BETWEEN '2026-01-01' AND '2026-01-05' is equivalent to BETWEEN '2026-01-01 00:00:00' AND '2026-01-05 00:00:00'. Any event occurring at '2026-01-05 14:30:00' will not be included because it falls after the midnight limit of the upper bound.

Code Examples

SQL

-- Filter orders with transaction amounts between $50 and $200
SELECT order_id, amount 
FROM orders 
WHERE amount BETWEEN 50.0 AND 200.0;

-- Exclude employees hired in a specific year range
SELECT name, hire_date 
FROM employees 
WHERE hire_date NOT BETWEEN '2020-01-01' AND '2022-12-31';

Best Practices

• Order of Limits: The smaller value must always be written before the larger value: BETWEEN lower AND upper. Writing BETWEEN 200 AND 50 will return zero rows.
• Explicit Dates Bounds: For datetime types, use explicit timestamp limits or inequalities like < '2026-01-06' instead of relying on BETWEEN to capture the final day.

Interview Perspective

Interactive Challenges

Summary

BETWEEN matches ranges inclusively. Pay extra attention to midnight cutoffs when filtering datetimes.