MySQL BETWEEN

Last Updated: 27 July, 2023

In MySQL, the BETWEEN clause is used to retrieve rows that fall within a specified range. It is often used in conjunction with the WHERE clause to filter results based on a particular condition.

The syntax for using the BETWEEN clause is as follows:

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

Here's an example that demonstrates the usage of the BETWEEN clause:

Suppose we have a table called employees with the following structure:

Table: employees

  +----+-----------+------------+--------+------------+---------------+
  | ID | Name      | Department |   Age  |   Salary   |  JoiningDate  | 
  +----+-----------+------------+--------+------------+---------------+
  | 1  | Rajesh    | Sales      |   43   |    54,000  |   2023-06-11  |
  | 2  | Kamal     | HR         |   35   |    38,000  |   2021-01-23  |
  | 3  | Vimal     | Sales      |   52   |    68,000  |   2022-04-08  |
  | 4  | Hina      | Marketing  |   38   |    72,000  |   2018-08-16  |
  | 5  | Jyanti    | Finance    |   29   |    60,000  |   2010-02-24  |
  | 6  | Sanjay    | HR         |   31   |    55,000  |   2023-01-29  |
  | 7  | Amar      | Finance    |   27   |    40,000  |   2023-07-19  | 
  +----+-----------+------------+--------+------------+---------------+

In this employees table, if we need to fetch employee records whose age is between 30 and 50 years, we will write the following query:

  mysql> SELECT * FROM employees WHERE Age BETWEEN 30 AND 50;
  +----+--------+------------+------+--------+-------------+
  | ID | Name   | Department | Age  | Salary | JoiningDate |
  +----+--------+------------+------+--------+-------------+
  |  1 | Rajesh | Sales      |   43 |  54000 | 2023-06-11  |
  |  2 | Kamal  | HR         |   35 |  38000 | 2021-01-23  |
  |  4 | Hina   | Marketing  |   38 |  72000 | 2018-08-16  |
  |  6 | Sanjay | HR         |   31 |  55000 | 2023-01-29  |
  +----+--------+------------+------+--------+-------------+
  4 rows in set (0.06 sec)

This query retrieves all columns (*) from the "employees" table where the "age" column falls within the range of 30 and 50 (inclusive).

If we need to retrieve records between two dates, we will write the query as given below.

  mysql> SELECT * FROM employees 
         WHERE JoiningDate BETWEEN '2022-01-01' AND '2023-05-31';
  +----+--------+------------+------+--------+-------------+
  | ID | Name   | Department | Age  | Salary | JoiningDate |
  +----+--------+------------+------+--------+-------------+
  |  3 | Vimal  | Sales      |   52 |  68000 | 2022-04-08  |
  |  6 | Sanjay | HR         |   31 |  55000 | 2023-01-29  |
  +----+--------+------------+------+--------+-------------+
  2 rows in set (0.00 sec)

Keep in mind that the BETWEEN clause includes both the lower and upper boundaries. If we want to exclude either end of the range, we can use the < or > operators instead.