MySQL - Between Operator

MySQL - The BETWEEN Operator

The BETWEEN Operator for Range Filtering

The BETWEEN operator in MySQL is a logical operator used in a WHERE clause to select values within a specified range. It is inclusive, meaning it includes the start and end values of the range.

Using BETWEEN is a more readable and concise way of writing a combination of ‘greater than or equal to’ AND ‘less than or equal to’ conditions. The expression column_name BETWEEN value1 AND value2 is equivalent to column_name >= value1 AND column_name <= value2.

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Here, value1 is the lower bound of the range and value2 is the upper bound. Both are included in the search.

Practical Examples with BETWEEN

Let’s use our customers table to demonstrate. First, a reminder of the data:

SELECT * FROM customers;

-- Result:
-- +----+----------+-----+-----------+----------+
-- | id | name     | age | address   | salary   |
-- +----+----------+-----+-----------+----------+
-- | 1  | Ramesh   | 32  | Ahmedabad |  2000.00 |
-- | 2  | Khilan   | 25  | Delhi     |  1500.00 |
-- | 3  | Kaushik  | 23  | Kota      |  2000.00 |
-- | 4  | Chaitali | 25  | Mumbai    |  6500.00 |
-- | 5  | Hardik   | 27  | Bhopal    |  8500.00 |
-- | 6  | Komal    | 22  | Hyderabad |  4500.00 |
-- | 7  | Muffy    | 24  | Indore    | 10000.00 |
-- +----+----------+-----+-----------+----------+

Example: Filtering by a Numeric Range

Let’s find customers whose age is between 25 and 30, inclusive.

SELECT * FROM customers
WHERE age BETWEEN 25 AND 30;

id	name	age	address	salary
2	Khilan	25	Delhi	1500.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00

Negating the Range: NOT BETWEEN

To select values that fall outside a specified range, you can use the NOT BETWEEN operator.

Example: Filtering Outside a Numeric Range

Let’s find customers whose salary is NOT between 2000.00 and 8000.00.

SELECT * FROM customers
WHERE salary NOT BETWEEN 2000.00 AND 8000.00;

id	name	age	address	salary
2	Khilan	25	Delhi	1500.00
5	Hardik	27	Bhopal	8500.00
7	Muffy	24	Indore	10000.00

Combining BETWEEN with Other Operators

You can combine BETWEEN with other logical operators like AND, OR, and IN to create more complex filters.

Example: Using BETWEEN with IN

Find customers whose salary is between 4000 and 10000 AND who live in ‘Hyderabad’ or ‘Bhopal’.

SELECT * FROM customers
WHERE (salary BETWEEN 4000 AND 10000)
  AND address IN ('Hyderabad', 'Bhopal');

id	name	age	address	salary
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00

Using BETWEEN in UPDATE and DELETE Statements

The BETWEEN operator is also very useful for modifying or deleting records that fall within a specific range.

Example: UPDATE with BETWEEN

Let’s give a salary increase to all customers aged between 22 and 24.

UPDATE customers
SET salary = salary * 1.10 -- 10% raise
WHERE age BETWEEN 22 AND 24;

To verify the change, you can run a SELECT query. You’ll see that the salaries for Komal (22), Kaushik (23), and Muffy (24) have been increased.

Common Pitfalls with Dates and Times

A common trap when using BETWEEN is with DATETIME or TIMESTAMP columns. A query like BETWEEN '2023-10-01' AND '2023-10-31' is interpreted as BETWEEN '2023-10-01 00:00:00' AND '2023-10-31 00:00:00'. This will exclude any records from October 31st that occurred after midnight.

The safer, recommended approach for date ranges is to use a combination of >= and <:

-- SAFE METHOD for date ranges
SELECT * FROM events
WHERE event_timestamp >= '2023-10-01' AND event_timestamp < '2023-11-01';

Using the BETWEEN Operator from Application Code

When using BETWEEN in your application, it’s critical to use prepared statements to pass the range values. This prevents SQL injection.

Example: Securely Querying a Range in Python

import mysql.connector
from mysql.connector import Error

def find_customers_in_age_range(min_age, max_age):
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='TUTORIALS',
            user='root',
            password='password'
        )
        cursor = connection.cursor(dictionary=True)

        sql_query = "SELECT id, name, age FROM customers WHERE age BETWEEN %s AND %s"

        # Pass values as a tuple to the execute method for parameterization
        cursor.execute(sql_query, (min_age, max_age))
        records = cursor.fetchall()

        print(f"Found {cursor.rowcount} customers between ages {min_age} and {max_age}:")
        for row in records:
            print(row)

    except Error as e:
        print(f"Error: {e}")
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

# Call the function with a specific age range
find_customers_in_age_range(25, 30)