Range Filtering with BETWEEN0%

Range Filtering with BETWEEN

Beginner12 min read•Updated: 2026-09-12

Range Filtering with BETWEEN: Inclusive Numeric and Date Ranges

A frequent query pattern is filtering values that fall within a specified range: finding products priced between ₹500 and ₹2,000, filtering transactions that occurred between two dates, or identifying students scoring between 75% and 90%. In SQL, this is expressed cleanly using the BETWEEN operator.

1. Syntax of the `BETWEEN` Operator

1SELECT column_1, column_2
2FROM table_name
3WHERE column_name BETWEEN lower_boundary AND upper_boundary;

The Cardinal Rule: `BETWEEN` is Strictly Inclusive!

In standard SQL and MySQL, the BETWEEN operator is always inclusive:

1WHERE price BETWEEN 100 AND 500
2-- Is mathematically and logically 100% equivalent to:
3WHERE price >= 100 AND price <= 500

Both boundary endpoints (100 and 500) are included in the results!

2. Using `BETWEEN` with Numbers and Dates

1-- Numeric Range: Find employees with salaries between ₹40,000 and ₹80,000
2SELECT employee_id, full_name, salary
3FROM employees
4WHERE salary BETWEEN 40000.00 AND 80000.00;
5 
6-- Date Range: Find all orders placed in Q1 2026 (Jan 1 to Mar 31)
7SELECT order_id, customer_id, order_date, total_amount
8FROM orders
9WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31';

3. The `DATETIME` Trap with `BETWEEN`!

When filtering columns of type DATETIME or TIMESTAMP, using BETWEEN can cause subtle data omission bugs:

1-- Suppose an order was placed on '2026-03-31 15:30:00'.
2SELECT * FROM orders
3WHERE order_timestamp BETWEEN '2026-01-01' AND '2026-03-31';

Why did the order placed on March 31 vanish?

Because the literal '2026-03-31' is implicitly converted by MySQL to '2026-03-31 00:00:00' (midnight at the very beginning of the day)! Any order placed after midnight on March 31 is greater than the upper boundary and gets excluded!

The Production Best Practice for Date Ranges:

Always use half-open intervals with >= and <:

1SELECT * FROM orders
2WHERE order_timestamp >= '2026-01-01 00:00:00'
3  AND order_timestamp < '2026-04-01 00:00:00'; -- Clean, safe, and includes all of March 31!

4. Inverting Ranges with `NOT BETWEEN`

To retrieve rows that fall outside a given range:

1-- Find items that are NOT in the mid-range price tier:
2SELECT product_id, product_name, price
3FROM products
4WHERE price NOT BETWEEN 1000.00 AND 5000.00;
5-- Equivalent to: WHERE price < 1000.00 OR price > 5000.00

5. Best Practices & Common Pitfalls

Lower Boundary Must Come First: Writing WHERE price BETWEEN 500 AND 100 will return 0 rows! In SQL, BETWEEN val1 AND val2 is defined as col >= val1 AND col <= val2. If val1 > val2, the condition is mathematically impossible to satisfy.
Alphabetical Ranges with Strings: You can use BETWEEN on text strings (e.g., WHERE last_name BETWEEN 'A' AND 'D'), but be careful: a name like 'Dhoni' is alphabetically greater than 'D' and will be excluded!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Is the BETWEEN operator in SQL inclusive or exclusive of its boundary values?

Question 2

What will the query SELECT * FROM products WHERE price BETWEEN 1000 AND 500; return?

Question 3

Why can querying a DATETIME column with BETWEEN '2026-01-01' AND '2026-01-31' fail to return records placed in the afternoon of January 31?

Question 4

What is WHERE score NOT BETWEEN 50 AND 100 logically equivalent to?

Question 5

Can BETWEEN be used to filter character strings alphabetically in MySQL?

Next Lesson

Membership Filtering with IN Operator

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Logical Operators: AND, OR, NOT	Membership Filtering with IN Operator

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Membership Filtering with IN Operator

Range Filtering with BETWEEN

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Range Filtering with BETWEEN: Inclusive Numeric and Date Ranges

1. Syntax of the `BETWEEN` Operator

1SELECT column_1, column_2
2FROM table_name
3WHERE column_name BETWEEN lower_boundary AND upper_boundary;

The Cardinal Rule: `BETWEEN` is Strictly Inclusive!

In standard SQL and MySQL, the BETWEEN operator is always inclusive:

1WHERE price BETWEEN 100 AND 500
2-- Is mathematically and logically 100% equivalent to:
3WHERE price >= 100 AND price <= 500

Both boundary endpoints (100 and 500) are included in the results!

2. Using `BETWEEN` with Numbers and Dates

1-- Numeric Range: Find employees with salaries between ₹40,000 and ₹80,000
2SELECT employee_id, full_name, salary
3FROM employees
4WHERE salary BETWEEN 40000.00 AND 80000.00;
5 
6-- Date Range: Find all orders placed in Q1 2026 (Jan 1 to Mar 31)
7SELECT order_id, customer_id, order_date, total_amount
8FROM orders
9WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31';

3. The `DATETIME` Trap with `BETWEEN`!

When filtering columns of type DATETIME or TIMESTAMP, using BETWEEN can cause subtle data omission bugs:

1-- Suppose an order was placed on '2026-03-31 15:30:00'.
2SELECT * FROM orders
3WHERE order_timestamp BETWEEN '2026-01-01' AND '2026-03-31';

Why did the order placed on March 31 vanish?

The Production Best Practice for Date Ranges:

Always use half-open intervals with >= and <:

1SELECT * FROM orders
2WHERE order_timestamp >= '2026-01-01 00:00:00'
3  AND order_timestamp < '2026-04-01 00:00:00'; -- Clean, safe, and includes all of March 31!

4. Inverting Ranges with `NOT BETWEEN`

To retrieve rows that fall outside a given range:

1-- Find items that are NOT in the mid-range price tier:
2SELECT product_id, product_name, price
3FROM products
4WHERE price NOT BETWEEN 1000.00 AND 5000.00;
5-- Equivalent to: WHERE price < 1000.00 OR price > 5000.00

5. Best Practices & Common Pitfalls

Lower Boundary Must Come First: Writing WHERE price BETWEEN 500 AND 100 will return 0 rows! In SQL, BETWEEN val1 AND val2 is defined as col >= val1 AND col <= val2. If val1 > val2, the condition is mathematically impossible to satisfy.
Alphabetical Ranges with Strings: You can use BETWEEN on text strings (e.g., WHERE last_name BETWEEN 'A' AND 'D'), but be careful: a name like 'Dhoni' is alphabetically greater than 'D' and will be excluded!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Is the BETWEEN operator in SQL inclusive or exclusive of its boundary values?

Question 2

What will the query SELECT * FROM products WHERE price BETWEEN 1000 AND 500; return?

Question 3

Why can querying a DATETIME column with BETWEEN '2026-01-01' AND '2026-01-31' fail to return records placed in the afternoon of January 31?

Question 4

What is WHERE score NOT BETWEEN 50 AND 100 logically equivalent to?

Question 5

Can BETWEEN be used to filter character strings alphabetically in MySQL?

Next Lesson

Membership Filtering with IN Operator

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Logical Operators: AND, OR, NOT	Membership Filtering with IN Operator

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Range Filtering with BETWEEN: Inclusive Numeric and Date Ranges

1. Syntax of the BETWEEN Operator

The Cardinal Rule: BETWEEN is Strictly Inclusive!

2. Using BETWEEN with Numbers and Dates

3. The DATETIME Trap with BETWEEN!

Why did the order placed on March 31 vanish?

The Production Best Practice for Date Ranges:

4. Inverting Ranges with NOT BETWEEN

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Membership Filtering with IN Operator

Related Lessons

Practice Quiz

Loading Page Content...

Range Filtering with BETWEEN: Inclusive Numeric and Date Ranges

1. Syntax of the BETWEEN Operator

The Cardinal Rule: BETWEEN is Strictly Inclusive!

2. Using BETWEEN with Numbers and Dates

3. The DATETIME Trap with BETWEEN!

Why did the order placed on March 31 vanish?

The Production Best Practice for Date Ranges:

4. Inverting Ranges with NOT BETWEEN

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Membership Filtering with IN Operator

Related Lessons

Practice Quiz

1. Syntax of the `BETWEEN` Operator

The Cardinal Rule: `BETWEEN` is Strictly Inclusive!

2. Using `BETWEEN` with Numbers and Dates

3. The `DATETIME` Trap with `BETWEEN`!

4. Inverting Ranges with `NOT BETWEEN`

1. Syntax of the `BETWEEN` Operator

The Cardinal Rule: `BETWEEN` is Strictly Inclusive!

2. Using `BETWEEN` with Numbers and Dates

3. The `DATETIME` Trap with `BETWEEN`!

4. Inverting Ranges with `NOT BETWEEN`