MySQL BETWEEN Operator

Introduction

In this chapter, we will learn about the BETWEEN operator in MySQL. The BETWEEN operator is used to filter the result set within a specified range. This is particularly useful for selecting records that fall between two values, such as dates, numbers, or text. We will cover the syntax, examples, and important considerations for using the BETWEEN operator.

Syntax

The basic syntax for the BETWEEN operator is:

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

• column_name: The column to filter.
• value1 and value2: The range values. The result set includes records where column_name is between value1 and value2, inclusive.

To exclude the range values, you can use the NOT BETWEEN operator:

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

Using BETWEEN Operator

Example with Numbers

SELECT first_name, last_name, score
FROM students
WHERE score BETWEEN 70 AND 90;

This example retrieves rows where the score is between 70 and 90, inclusive.

Example with Dates

SELECT first_name, last_name, enrollment_date
FROM students
WHERE enrollment_date BETWEEN '2023-07-01' AND '2023-07-04';

This example retrieves rows where the enrollment_date is between July 1, 2023, and July 4, 2023, inclusive.

Example with NOT BETWEEN

SELECT first_name, last_name, score
FROM students
WHERE score NOT BETWEEN 70 AND 90;

This example retrieves rows where the score is not between 70 and 90.

Full Example

Let’s go through a full example where we create a table, insert data into it, and use the BETWEEN operator to filter records.

1. Create a Database:

CREATE DATABASE school;

2. Select the Database:

USE school;

3. Create a Table:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    score INT,
    enrollment_date DATE
);

4. Insert Data into the Table:

INSERT INTO students (first_name, last_name, score, enrollment_date) VALUES
('Rahul', 'Sharma', 85, '2023-07-01'),
('Priya', 'Singh', 90, '2023-07-02'),
('Amit', 'Kumar', 75, '2023-07-03'),
('Neha', 'Verma', 95, '2023-07-04'),
('Sahil', 'Mehta', 80, '2023-07-05');

5. Use the BETWEEN Operator with Numbers:

SELECT first_name, last_name, score
FROM students
WHERE score BETWEEN 70 AND 90;

Output

6. Use the BETWEEN Operator with Dates:

SELECT first_name, last_name, enrollment_date
FROM students
WHERE enrollment_date BETWEEN '2023-07-01' AND '2023-07-04';

Output

7. Use the NOT BETWEEN Operator:

SELECT first_name, last_name, score
FROM students
WHERE score NOT BETWEEN 70 AND 90;

Output

Important Considerations

• Inclusive: The BETWEEN operator includes the boundary values (value1 and value2) in the result set.
• Data Types: Ensure that the data types of value1 and value2 match the data type of the column_name to avoid errors.
• Performance: Using the BETWEEN operator can impact performance, especially on large datasets. Ensure appropriate indexing and optimize queries to improve performance.

Conclusion

The BETWEEN operator is used for filtering data within a specified range in MySQL queries. This chapter covered how to use the BETWEEN and NOT BETWEEN operators with numbers and dates, provided examples with SELECT statements, and discussed important considerations. In the next chapter, we will learn how to use the EXISTS operator to filter data based on the existence of rows in a subquery.