🌈 SQL BETWEEN Operator – Range Queries for Numbers, Dates & Text

🧲 Introduction – What is SQL BETWEEN?

The BETWEEN operator in SQL is used to filter values within an inclusive range. It works with numbers, text, and dates to return rows where the column’s value lies between two boundary values.

🎯 In this guide, you’ll learn:

• Syntax and usage of BETWEEN and NOT BETWEEN
• How to use it with numeric, date, and text types
• Best practices and real-world scenarios

---

✅ 1. Basic SQL BETWEEN Syntax

SELECT * FROM products
WHERE price BETWEEN 10 AND 50;

✅ Returns rows where price is greater than or equal to 10 and less than or equal to 50.

---

🔁 2. NOT BETWEEN to Exclude Ranges

SELECT * FROM employees
WHERE age NOT BETWEEN 30 AND 50;

✅ Returns employees younger than 30 or older than 50.

---

📆 3. Use BETWEEN with Dates

SELECT * FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

✅ Includes all orders made during 2023.

---

🔤 4. Use BETWEEN with Text (Lexical Range)

SELECT * FROM customers
WHERE last_name BETWEEN 'A' AND 'M';

✅ Filters names alphabetically between A and M.

---

🔧 5. Combining BETWEEN with AND / OR

SELECT * FROM payments
WHERE amount BETWEEN 100 AND 500
  AND method = 'Credit Card';

✅ Filters on both value range and category.

---

⚠️ 6. BETWEEN is Inclusive

• BETWEEN x AND y includes both x and y.
• To exclude boundary values, use > and < manually:

WHERE price > 10 AND price < 50;

---

📘 Best Practices

✅ Recommended	❌ Avoid This
Use BETWEEN for clean range queries	Rewriting as multiple AND conditions
Remember it includes boundaries	Expecting exclusive comparisons
Use proper data formats for dates	Comparing mismatched data types

---

📌 Summary – Recap & Next Steps

The BETWEEN operator provides a concise way to filter data within ranges, whether you’re checking dates, prices, scores, or names.

🔍 Key Takeaways:

• Use BETWEEN x AND y for inclusive ranges
• Combine with other conditions using AND/OR
• Works on numbers, text, and dates
• Use NOT BETWEEN to exclude a range

⚙️ Real-World Relevance:
Used in reporting timeframes, sales thresholds, alphabetic sorting, and age/range checks.

➡️ Next: Explore CASE WHEN to apply conditional logic in your queries.

---

❓ FAQ – SQL BETWEEN Operator

❓ Does BETWEEN include the boundary values?

✅ Yes. It includes both the start and end values.

❓ Can I use BETWEEN with dates?

✅ Yes. Make sure the date format matches the column’s data type.

❓ What happens if the first value is greater than the second?

✅ SQL still processes it, but it’s better to ensure x <= y.

❓ Can I use BETWEEN in a JOIN?

✅ Yes. Use it in the ON or WHERE clause as needed.

---

Share Now :