Beginner-Friendly Topic
Take your time - it's perfectly normal to re-read this topic 2-3 times. Try the interactive code editor below to run code yourself. Use the Q&A section to check your understanding before moving on. You've got this! 🚀
BETWEEN Operator
BETWEEN tests if a value falls within a range (inclusive on both ends). It's a cleaner way to write AND conditions for range checks. BETWEEN works with numbers, dates, and text. NOT BETWEEN gives the opposite.
BETWEEN Rules
- BETWEEN a AND b - value >= a AND value <= b (both inclusive)
- NOT BETWEEN a AND b - value < a OR value > b
- Works on numeric, date, and text columns
- Text BETWEEN uses alphabetical ordering: 'Apple' BETWEEN 'A' AND 'M'
- Always write the lower bound first: BETWEEN 10 AND 50 (not 50 AND 10)
BETWEEN Examples
-- Products priced between $50 and $700
SELECT name, price FROM products
WHERE price BETWEEN 50 AND 700;
-- Result: Headphones (89.99), Phone (599.99)
-- Equivalent using AND:
SELECT name, price FROM products
WHERE price >= 50 AND price <= 700;
-- Same result - BETWEEN is just cleaner syntax
-- NOT BETWEEN: products outside the range
SELECT name, price FROM products
WHERE price NOT BETWEEN 50 AND 700;
-- Result: Laptop (999.99)
-- Date range: orders placed in January 2024
SELECT id, created_at FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31 23:59:59';
-- Stock availability range
SELECT name, stock FROM products
WHERE stock BETWEEN 50 AND 150;
-- Result: Laptop (50), Phone (120)Quick Quiz
Tip
Tip
Practice BETWEEN Operator in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Inner query runs first, results used by outer query. CTEs are often more readable.
Common Mistake
Warning
A common mistake with BETWEEN Operator is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of BETWEEN Operator from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- BETWEEN tests if a value falls within a range (inclusive on both ends).
- BETWEEN a AND b - value >= a AND value <= b (both inclusive)
- NOT BETWEEN a AND b - value < a OR value > b
- Works on numeric, date, and text columns