In MySQL, the BETWEEN operator is used to filter the result set within a specific range of values. It is commonly used in WHERE clauses to specify a range of values for a column. The BETWEEN operator is inclusive, meaning it includes the boundary values in the result set.

Syntax

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

• column_name: The column to filter.
• value1: The starting value of the range.
• value2: The ending value of the range.

Key Concepts

1. Range Filtering

   The BETWEEN operator is used to select values within a specified range. For numeric, date, or time columns, it filters rows where the column value is between the given values, inclusive of the boundaries.

   Example

   To find all employees with a salary between 40,000 and 60,000:

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

   This query retrieves employees whose salaries fall within the specified range.

2. Inclusive Range

   The BETWEEN operator includes both the lower and upper bounds of the range in the result set. This means that if a column value is equal to either value1 or value2, it will be included in the results.

3. Date and Time Ranges

   BETWEEN can also be used with date and time columns to filter records within a date or time range.

   Example

   To find all orders placed between January 1, 2024, and January 31, 2024:

   SELECT order_id, order_date
   FROM orders
   WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';
   

   This query retrieves orders placed within the specified date range.

4. Usage with Strings

   Although BETWEEN is often used with numbers and dates, it can also be used with strings. For strings, BETWEEN compares based on alphabetical order.

   Example

   To find products with names between 'Apple' and 'Orange':

   SELECT product_name
   FROM products
   WHERE product_name BETWEEN 'Apple' AND 'Orange';
   

   This query retrieves products with names that fall alphabetically between 'Apple' and 'Orange'.

Examples

1. Numeric Range

   To find employees with ages between 25 and 40:

   SELECT name, age
   FROM employees
   WHERE age BETWEEN 25 AND 40;
   

2. Date Range

   To find employees hired between March 1, 2023, and March 31, 2023:

   SELECT name, hire_date
   FROM employees
   WHERE hire_date BETWEEN '2023-03-01' AND '2023-03-31';
   

3. String Range

   To find products with names between 'A' and 'M':

   SELECT product_name
   FROM products
   WHERE product_name BETWEEN 'A' AND 'M';
   

Best Practices

• Ensure Range Order: BETWEEN assumes the first value is less than or equal to the second value. Ensure correct order to avoid unexpected results.
• Use with Dates and Times: When using BETWEEN with dates and times, ensure the format matches the column’s data type.
• Indexes: Index columns used with BETWEEN to improve query performance, especially with large datasets.