BETWEEN range filtering in MySQL - Step-by-Step Execution

Concept Flow - BETWEEN range filtering

Start Query

↓

Check BETWEEN condition

↓

Value >= Lower Bound?

No→Exclude Row

↓

Value <= Upper Bound?

No→Exclude Row

↓

Include Row

↓

Return Filtered Rows

The query checks if a value is between two bounds inclusive, including rows that satisfy both conditions.

Execution Sample

MySQL

SELECT * FROM products WHERE price BETWEEN 10 AND 20;

This query selects products with prices from 10 to 20 inclusive.

Execution Table

Step	Row ID	price	Check price >= 10	Check price <= 20	Include Row?
1	1	5	False	N/A	No
2	2	10	True	True	Yes
3	3	15	True	True	Yes
4	4	20	True	True	Yes
5	5	25	True	False	No
6	End	N/A	N/A	N/A	Query complete

💡 All rows checked; only rows with price between 10 and 20 inclusive are included.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	After Row 5	Final
price	N/A	5	10	15	20	25	N/A
Include Row?	N/A	No	Yes	Yes	Yes	No	N/A

Key Moments - 2 Insights

Why is the row with price 10 included even though it equals the lower bound?

BETWEEN includes the boundary values, so price >= 10 is True for 10, making the row included as shown in execution_table row 2.

Why is the row with price 25 excluded even though it is greater than 10?

Because price <= 20 is False for 25, the row is excluded as shown in execution_table row 5.

Visual Quiz - 3 Questions

Look at the execution table, what is the result of the check price >= 10 for Row ID 1?

A True

B N/A

C False

D Unknown

💡 Hint

Refer to execution_table row 1, column 'Check price >= 10'

At which row does the condition price <= 20 become False?

A Row 5

B Row 4

C Row 2

D Row 3

💡 Hint

Check execution_table column 'Check price <= 20' for the first False value

If the lower bound changed from 10 to 15, which row would now be excluded?

A Row with price 15

B Row with price 10

C Row with price 20

D Row with price 25

💡 Hint

Look at variable_tracker for price and inclusion when lower bound is 10; changing to 15 excludes prices below 15

Concept Snapshot

BETWEEN syntax: value BETWEEN low AND high
Includes rows where value >= low AND value <= high
Bounds are inclusive
Useful for filtering ranges easily
Equivalent to: value >= low AND value <= high

Full Transcript

The BETWEEN operator filters rows where a column's value falls within a specified range, including the boundaries. The query checks each row's value against the lower and upper bounds. If the value is greater than or equal to the lower bound and less than or equal to the upper bound, the row is included in the result. For example, selecting products with prices BETWEEN 10 AND 20 includes prices 10, 15, and 20 but excludes 5 and 25. This behavior is shown step-by-step in the execution table and variable tracker. Remember, BETWEEN is inclusive of the boundary values.