BETWEEN Operator in MySQL

The BETWEEN operator in MySQL is used to filter values within a range, including both the lower and upper values.

Syntax

SELECT column_name FROM table_name WHERE column_name BETWEEN lower_value AND upper_value;

• Includes both lower_value and upper_value.
• Works with numbers, dates, and text.

1. BETWEEN with Numbers

Example: Find employees with salaries between 40,000 and 80,000

SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000;

✅ Includes salaries exactly 40,000 and 80,000.

Same Query Without BETWEEN

SELECT * FROM employees WHERE salary >= 40000 AND salary <= 80000;

2. BETWEEN with Dates

Example: Find orders placed between January 1, 2024, and March 31, 2024

SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

✅ Includes orders on January 1st and March 31st.

Same Query Without BETWEEN

SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date <= '2024-03-31';

3. BETWEEN with Text (Alphabetical Range)

Example: Find customers whose names are between 'A' and 'M'

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

✅ Includes names starting with 'A' to 'M' (like "Aaron" to "Michael").
❌ May exclude names after 'M', depending on collation settings.

4. NOT BETWEEN

Use NOT BETWEEN to exclude a range.

Example: Find employees whose salary is NOT between 40,000 and 80,000

SELECT * FROM employees WHERE salary NOT BETWEEN 40000 AND 80000;

✅ Includes employees earning less than 40,000 or more than 80,000.

Key Points

✅ BETWEEN includes both lower and upper bounds.
✅ Works with numbers, dates, and text.
✅ NOT BETWEEN excludes the range.
✅ Can be replaced with >= and <= for the same effect.