BETWEEN Operator

Learning Focus

Use this lesson to understand BETWEEN Operator with practical syntax and examples.

Concept Overview

What Is the BETWEEN Operator?

The BETWEEN operator in MySQL is used to filter results within a specified range. It checks whether a value lies between two other values, inclusive of the endpoints.

column_name BETWEEN value1 AND value2

This is equivalent to:

column_name >= value1 AND column_name <= value2

Why It Matters

• Simplifies range-based filtering
• Enhances readability and maintainability of SQL queries
• Makes logical conditions concise and intuitive

Broader Context

The BETWEEN operator belongs to MySQL's conditional filtering tools, alongside =, IN, LIKE, and comparison operators (<, >, <=, >=, !=). It is especially useful in WHERE clauses when working with:

• Numeric ranges (e.g., age, salary)
• Date ranges (e.g., between two booking dates)
• Text ranges (alphabetical)

---

Basic Syntax & Rules

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Key Notes

• value1 and value2 define the range (inclusive).
• You can use NOT BETWEEN to exclude a range.
• Works with INTEGER, DECIMAL, DATE, and TEXT (alphabetical).
• Order of value1 and value2 matters: BETWEEN 10 AND 5 returns nothing if 10 > 5.

Limitations

• BETWEEN includes boundaries; to exclude them, use > and < manually.
• Not case-insensitive for text unless the collation is.

---

Step-by-Step Examples

Example 1: Filter Employee Salaries

SELECT first_name, salary
FROM employees
WHERE salary BETWEEN 40000 AND 60000;

Expected Output:

+------------+--------+
| first_name | salary |
+------------+--------+
| Alice      | 45000  |
| John       | 59000  |
+------------+--------+

Example 2: Filter by Hire Date

SELECT first_name, hire_date
FROM employees
WHERE hire_date BETWEEN '2022-01-01' AND '2023-01-01';

Expected Output:

+------------+------------+
| first_name | hire_date  |
+------------+------------+
| Sarah      | 2022-03-15 |
| Mike       | 2022-12-10 |
+------------+------------+

Example 3: Alphabetical Range

SELECT product_name
FROM products
WHERE product_name BETWEEN 'L' AND 'S';

Expected Output:

+----------------+
| product_name   |
+----------------+
| Monitor        |
| Speaker        |
| Router         |
+----------------+

Example 4: Using NOT BETWEEN

SELECT first_name, age
FROM customers
WHERE age NOT BETWEEN 18 AND 30;

---

Practical Use Cases

1. HR Systems

Problem: Find employees hired in a specific period

Query:

SELECT * FROM employees
WHERE hire_date BETWEEN '2022-01-01' AND '2022-12-31';

2. E-Commerce

Problem: List products in mid-range price

Query:

SELECT * FROM products
WHERE price BETWEEN 50 AND 100;

3. Booking Systems

Problem: Find bookings within a date range

Query:

SELECT * FROM reservations
WHERE booking_date BETWEEN '2025-06-01' AND '2025-07-01';

4. Retail Analytics

Problem: Generate monthly report for mid-range selling products

Query:

SELECT * FROM sales
WHERE total_amount BETWEEN 100 AND 500;

5. School Management

Problem: Identify students aged between 10 and 14

Query:

SELECT * FROM students
WHERE age BETWEEN 10 AND 14;

---

Common Mistakes & Troubleshooting

Mistake	Explanation	Solution
Using values in reverse: BETWEEN 100 AND 50	No results returned if lower value comes after higher	Always use: BETWEEN lower AND higher
Expecting exclusive results	BETWEEN includes both endpoints	Use > and < if you want exclusivity
Wrong data type (e.g., string for date)	May lead to logic errors or empty results	Ensure values match column type
Forgetting date format	'01-01-2022' may not work	Use 'YYYY-MM-DD' format for dates

---

Best Practices

• Use BETWEEN for inclusive range filtering only
• For exclusive ranges, prefer: > value1 AND < value2
• Avoid using on text fields unless you understand collation behavior
• Use indexes on columns filtered with BETWEEN for faster performance
• Be cautious when using BETWEEN with floating-point numbers due to rounding

---

Hands-On Practice

Sample Data Setup

CREATE TABLE orders (
  order_id INT,
  customer_name VARCHAR(100),
  total_amount DECIMAL(10,2),
  order_date DATE
);

INSERT INTO orders VALUES
(1, 'Alice', 150.00, '2025-06-01'),
(2, 'Bob', 85.50, '2025-06-15'),
(3, 'Charlie', 250.75, '2025-07-01'),
(4, 'Diana', 310.10, '2025-07-10');

Exercise 1: Easy

Question: Retrieve all orders with total amount between 100 and 300.

Answer:

SELECT * FROM orders
WHERE total_amount BETWEEN 100 AND 300;

Exercise 2: Intermediate

Question: Get all orders placed in June 2025.

Answer:

SELECT * FROM orders
WHERE order_date BETWEEN '2025-06-01' AND '2025-06-30';

Exercise 3: Advanced

Question: Find all customers whose order amount was NOT between 100 and 300.

Answer:

SELECT customer_name
FROM orders
WHERE total_amount NOT BETWEEN 100 AND 300;

---

Connection to Other Concepts

• Prerequisites:
  • Understanding of WHERE clause
  • Familiarity with comparison operators (<, >, =)
• Related Concepts:
  • IN, NOT IN (for list filtering)
  • LIKE, REGEXP (for pattern matching)
  • IS NULL, IS NOT NULL (for null checks)
• What's Next:
  • CASE WHEN for conditional logic
  • Aggregate functions + GROUP BY + HAVING for grouped filtering
  • Subqueries for dynamic range filtering

---

Concept Map

flowchart LR
    A[Schema Context] --> B[BETWEEN Operator]
    B --> C[Query Pattern]
    C --> D[Validation]
    D --> E[Production Use]

Common Pitfalls

Pitfall	Consequence	Prevention
Executing queries without validating sample rows	Logic errors reach production data or reports	Start with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behavior	Aggregations and filters return misleading results	Test with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schema	Queries fail due to missing columns/indexes	Verify structure with DESCRIBE table_name; and adapt query design

Quick Reference

SELECT * FROM orders WHERE total BETWEEN 100 AND 500;
SELECT * FROM events WHERE event_date BETWEEN "2025-01-01" AND "2025-12-31";

What's Next

• Previous: IN Operator - Review the previous lesson to reinforce context.
• Next: Aliases (AS) - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.