What is the MySQL BETWEEN Operator?

The BETWEEN operator in MySQL is used to filter rows where a column’s value falls within a specified range. It’s particularly useful for numerical ranges, dates, or other comparable data types.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
  • value1: The lower boundary of the range (inclusive).
  • value2: The upper boundary of the range (inclusive).

To exclude rows within the range, use NOT BETWEEN:

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

Output:

+-------------+---------------+--------+
| employee_id | employee_name | salary |
+-------------+---------------+--------+
| 1           | John Doe      | 60000  |
| 3           | Mary Johnson | 65000  |
| 5           | William Brown | 55000  |
+-------------+---------------+--------+

Examples of Using the BETWEEN Operator

1. Numeric Range

Retrieve employees with salaries between 40,000 and 70,000.

Query:

SELECT * FROM employees
WHERE salary BETWEEN 40000 AND 70000;

Example Output:

+----+---------+-------------+--------+
| id | name    | department  | salary |
+----+---------+-------------+--------+
| 1  | Alice   | HR          | 45000  |
| 2  | Bob     | Finance     | 55000  |
| 3  | Eve     | Marketing   | 40000  |
+----+---------+-------------+--------+

2. Date Range

Find orders placed between January 1, 2024, and March 31, 2024.

Query:

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

3. NOT BETWEEN

Retrieve employees with salaries outside the range of 40,000 to 70,000.

Query:

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

4. BETWEEN with Text

Find customers whose names are alphabetically between ‘A’ and ‘M’.

Query:

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

Using BETWEEN Operator in MySQL Workbench

  • Open MySQL Workbench.
  • Write a query using the BETWEEN operator in the SQL editor.
  • Click Execute to run the query.
  • View the filtered results in the result grid.

Best Practices

Inclusive Nature

Inclusive Nature
The BETWEEN operator includes both boundary values (value1 and value2). Use this knowledge to avoid unintended results.

Date Formats
Ensure date values are in the correct format (YYYY-MM-DD) to avoid mismatches or errors.

Column Indexing
Index columns used with BETWEEN to improve query performance.

 

Use for Ranges Only
Use BETWEEN for continuous ranges. For discrete values, consider the IN operator.

[WpProQuiz 9]