BETWEEN Operator for Ranges
What you'll learn: How to select rows where a column value falls within an inclusive range using the BETWEEN operator in SQL.
Why Use BETWEEN?
When you need to filter data within a specific range—like finding all orders between 100, or employees hired between two dates—you could write two separate conditions with >= and <=. But SQL gives you a cleaner shortcut: the BETWEEN operator.
How BETWEEN Works
The BETWEEN operator checks if a value falls within an inclusive range, meaning both the lower and upper boundaries are included in the results.
Basic syntax:
SELECT column_name
FROM table_name
WHERE column_name BETWEEN lower_value AND upper_value;
Think of BETWEEN like a bouncer checking if someone's age is "21 through 65." If you're exactly 21 or exactly 65, you're still in the valid range!
Practical Examples
Finding products in a price range:
SELECT product_name, price
FROM products
WHERE price BETWEEN 10 AND 50;
This returns products priced from 50, including items at exactly 50.
Finding dates within a period:
SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
BETWEEN vs Multiple Conditions
These two queries are equivalent:
WHERE age BETWEEN 25 AND 40
WHERE age >= 25 AND age <= 40
The BETWEEN version is simply more readable and concise.
Key Takeaway: Use BETWEEN to filter rows where a column value falls within an inclusive range (both endpoints included), making your range queries cleaner and easier to read than chaining multiple comparison operators.