SQL - BETWEEN Operator

---

BETWEEN is often used to indicate a range or an interval of time, space, or position. In other words, it describes a situation in which something is positioned between two things/ends.

BETWEEN operator in SQL

The BETWEEN operator is a logical operator in SQL, that is used to retrieve the data within a specified range or a time interval. The retrieved values can be integers, characters, or dates.

Let us understand in a better way by using the below example table −

Suppose we want to list out the names from the above table who are aged BETWEEN 20 and 30. So, we will get “Varma(21)”, “Nikhil(25)”, and “Bhargav(29)” as result.

Syntax

Following is the syntax of the BETWEEN operator in SQL −

SELECT column_name1, column_name2, column_name3,……column_nameN
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Here,

• value1 is the beginning value of the range.
• value2 is the ending value of the range.

Example

First of all, let us create a table named “EMPLOYEE” using the following query −

SQL> CREATE TABLE EMPLOYEE(
   ID INT NOT NULL, 
   NAME VARCHAR(15) NOT NULL, 
   AGE INT NOT NULL, 
   ADDRESS CHAR(25), 
   SALARY DECIMAL(10, 2), 
   JOIN_DATE DATE, 
   PRIMARY KEY(ID)
);

Once the table is created, let us insert some values into the table using the following queries −

INSERT INTO EMPLOYEE(ID, NAME, AGE, ADDRESS, SALARY, JOIN_DATE) VALUES(2, 'Ramesh', 21, 'Hyderabad', 25550.12, '2023/01/02');
INSERT INTO EMPLOYEE(ID, NAME, AGE, ADDRESS, SALARY, JOIN_DATE) VALUES(1, 'khilan', 22, 'Nijamabad', 57500.84, '2022/01/14');
INSERT INTO EMPLOYEE(ID, NAME, AGE, ADDRESS, SALARY, JOIN_DATE) VALUES(4, 'Kaushik', 18, 'Bangolore', 47275.43, '2023/03/15');
INSERT INTO EMPLOYEE(ID, NAME, AGE, ADDRESS, SALARY, JOIN_DATE) VALUES(5, 'chaitali', 23, 'Ranchi', 40700.76, '2022/04/18');
INSERT INTO EMPLOYEE(ID, NAME, AGE, ADDRESS, SALARY, JOIN_DATE) VALUES(6, 'Hardhik', 19, 'Noida', 44200.09, '2023/06/04');
INSERT INTO EMPLOYEE(ID, NAME, AGE, ADDRESS, SALARY, JOIN_DATE) VALUES(3, 'komal', 23, 'Chennai', 44200.09, '2023/10/08');

Let us verify whether the table is created or not using the following query −

SQL> SELECT * FROM EMPLOYEE;

As we can see in the below output, the table has been created in the database.

+----+----------+-----+-------------+------------+----------------+
| ID |   NAME   | AGE |   ADDRESS   |   SALARY   |   JOIN_DATE    |
+----+----------+-----+-------------+------------+----------------+
| 1  | Khilan   | 22  |  Nizamabad  |   57500.84 |   2022-01-14   |
| 2  | Ramesh   | 21  |  Hyderabad  |   25550.12 |   2023-01-02   |
| 3  | komal    | 23  |  Chennai    |   44200.09 |   2023-10-08   |
| 4  | Kaushik  | 18  |  Bangalore  |   47275.43 |   2023-03-15   |
| 5  | chaitali | 23  |  Ranchi     |   40700.76 |   2022-04-18   |
| 6  | Hardhik  | 19  |  Noida      |   44200.09 |   2023-06-04   |
+----+----------+-----+-------------+------------+----------------+

Now, we are trying to use the BETWEEN operator to retrieve employees whose age (numeric data) is between 18 and 22.

SQL> SELECT * FROM EMPLOYEE 
WHERE AGE BETWEEN 18 AND 22;  

Output

When we execute the above query, the output is obtained as follows −

+----+----------+-----+-------------+------------+----------------+
| ID |   NAME   | AGE |   ADDRESS   |   SALARY   |   JOIN_DATE    |
+----+----------+-----+-------------+------------+----------------+
| 1  | Khilan   | 22  |  Nizamabad  |   57500.84 |   2022-01-14   |
| 2  | Ramesh   | 21  |  Hyderabad  |   25550.12 |   2023-01-02   |
| 4  | Kaushik  | 18  |  Bangalore  |   47275.43 |   2023-03-15   |
| 6  | Hardhik  | 19  |  Noida      |   44200.09 |   2023-06-04   |
+----+----------+-----+-------------+------------+----------------+

Example

Here, we are trying to use the BETWEEN operator with ‘characters’. Let us retrieve the employees whose names are starting in between the alphabets "A" and "L" using the following query −

SQL> SELECT * FROM EMPLOYEE
WHERE NAME BETWEEN 'A' AND 'L';  

Output

When we execute the above query, the output is obtained as follows −

+----+----------+-----+-------------+------------+----------------+
| ID |   NAME   | AGE |   ADDRESS   |   SALARY   |   JOIN_DATE    |
+----+----------+-----+-------------+------------+----------------+
| 1  | Khilan   | 22  |  Nizamabad  |   57500.84 |   2022-01-14   |
| 3  | komal    | 23  |  Chennai    |   44200.09 |   2023-10-08   |
| 4  | Kaushik  | 18  |  Bangalore  |   47275.43 |   2023-03-15   |
| 5  | chaitali | 23  |  Ranchi     |   40700.76 |   2022-04-18   |
| 6  | Hardhik  | 19  |  Noida      |   44200.09 |   2023-06-04   |
+----+----------+-----+-------------+------------+----------------+

Example

Now, we are trying to use the BETWEEN operator with ‘dates’. Let us retrieve the employees who joined between '2023-01-01' and '2023-03-31' using the following query −

SQL> SELECT * FROM EMPLOYEE
WHERE ORDER_DATE BETWEEN '2023-01-01' AND '2023-03-31';

Output

When we execute the above query, the output is obtained as follows −

+----+----------+-----+-------------+------------+----------------+
| ID |   NAME   | AGE |   ADDRESS   |   SALARY   |   JOIN_DATE    |
+----+----------+-----+-------------+------------+----------------+
| 2  | Ramesh   | 21  |  Hyderabad  |   25550.12 |   2023-01-02   |
| 4  | Kaushik  | 18  |  Bangalore  |   47275.43 |   2023-03-15   |
+----+----------+-----+-------------+------------+----------------+

BETWEEN operator with IN operator

In SQL, we can combine the BETWEEN operator and the IN operator to select values that are within a range and also match with specified values.

Example

In the following query, we are selecting all the employees whose salary is between 40000 and 50000. In addition; we are not retrieving the employees who are living in Chennai, Ranchi, and Hyderabad using IN operator in SQL.

SQL> SELECT * FROM EMPLOYEE
WHERE SALARY BETWEEN 40000 AND 45000
AND ADDRESS IN ('chennai', 'Ranchi', 'Hyderabad');

Output

On executing the above query, the output is displayed as follows −

+----+----------+-----+-------------+------------+----------------+
| ID |   NAME   | AGE |   ADDRESS   |   SALARY   |   JOIN_DATE    |
+----+----------+-----+-------------+------------+----------------+
| 3  | komal    | 23  |  Chennai    |   44200.09 |   2023-10-08   |
| 5  | chaitali | 23  |  Ranchi     |   40700.76 |   2022-04-18   |
+----+----------+-----+-------------+------------+----------------+

BETWEEN operator with UPDATE statement

We can also use the BETWEEN operator in an UPDATE statement to update values within the specified range. The UPDATE statement is used to modify existing data in a database table.

Example

Let us try to update the salaries of the employees whose age lies between 20 to 25 using the following query −

SQL> UPDATE EMPLOYEE SET SALARY = 60000 
WHERE AGE BETWEEN 20 AND 25;  

Output

The output for the above query is produced as given below −

(4 rows affected)

Verification

Let us verify whether the salaries are updated or not using the following query −

SQL> SELECT * FROM EMPLOYEE;  

The table for the above query produced as given below −

+----+----------+-----+-------------+------------+----------------+
| ID |   NAME   | AGE |   ADDRESS   |   SALARY   |   JOIN_DATE    |
+----+----------+-----+-------------+------------+----------------+
| 1  | Khilan   | 22  |  Nizamabad  |   60000.00 |   2022-01-14   |
| 2  | Ramesh   | 21  |  Hyderabad  |   60000.00 |   2023-01-02   |
| 3  | komal    | 23  |  Chennai    |   60000.00 |   2023-10-08   |
| 4  | Kaushik  | 18  |  Bangalore  |   47275.43 |   2023-03-15   |
| 5  | chaitali | 23  |  Ranchi     |   60000.00 |   2022-04-18   |
| 6  | Hardhik  | 19  |  Noida      |   44200.09 |   2023-06-04   |
+----+----------+-----+-------------+------------+----------------+

BETWEEN operator with DELETE statement

We can also use the BETWEEN operator in a DELETE statement to delete rows within a specified range.

Example

Now, let us try to delete the employees whose age is between 18 and 20 using the DELETE command.

SQL> DELETE FROM EMPLOYEE WHERE AGE BETWEEN 18 AND 20;  

Output

If we compile and run the above query, the result is produced as follows −

(2 rows affected)

Verification

Let us verify whether the specified aged employees are deleted or not using the following query −

SQL> SELECT * FROM EMPLOYEE;

The table for the above query produced as given below −

+----+----------+-----+-------------+------------+----------------+
| ID |   NAME   | AGE |   ADDRESS   |   SALARY   |   JOIN_DATE    |
+----+----------+-----+-------------+------------+----------------+
| 1  | Khilan   | 22  |  Nizamabad  |   60000.00 |   2022-01-14   |
| 2  | Ramesh   | 21  |  Hyderabad  |   60000.00 |   2023-01-02   |
| 3  | komal    | 23  |  Chennai    |   60000.00 |   2023-10-08   |
| 5  | chaitali | 23  |  Ranchi     |   60000.00 |   2022-04-18   |
+----+----------+-----+-------------+------------+----------------+

NOT BETWEEN operator in SQL

The NOT BETWEEN operator in SQL works exactly opposite to BETWEEN operator. This is used to retrieve the data which is not present in the specified range or time interval.

Let us understand in a better way by using the below example table −

Suppose we want to list out the students from the above table who are aged not between 20 and 30. So, we will get “Prudhvi(45) and Ganesh(33)” as result.

Syntax

Following is the syntax of the NOT BETWEEN operator in SQL −

SELECT column_name1, column_name2, column_name3,……column_nameN
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;

Example

Consider the previously created EMPLOYEE table and let us try to retrieve the employees whose age is not greater than equal to 18 and less than equal to 22 (numeric data) using the following query.

SQL> SELECT * FROM EMPLOYEE 
WHERE AGE NOT BETWEEN 18 AND 22;

Output

When we execute the above query, the output is obtained as follows −

+----+----------+-----+-------------+------------+----------------+
| ID |   NAME   | AGE |   ADDRESS   |   SALARY   |   JOIN_DATE    |
+----+----------+-----+-------------+------------+----------------+
| 3  | komal    | 23  |  Chennai    |   60000.00 |   2023-10-08   |
| 5  | chaitali | 23  |  Ranchi     |   60000.00 |   2022-04-18   |
+----+----------+-----+-------------+------------+----------------+

Example

Let us list the employees whose joining date is not between '2023-01-01' and '2023-03-31' using the following query −

SQL> SELECT * FROM EMPLOYEE
WHERE JOIN_DATE NOT BETWEEN '2023-01-01' AND '2023-03-31';

Output

On executing the above query, the output is displayed as follows −

+----+----------+-----+-------------+------------+----------------+
| ID |   NAME   | AGE |   ADDRESS   |   SALARY   |   JOIN_DATE    |
+----+----------+-----+-------------+------------+----------------+
| 1  | Khilan   | 22  |  Nizamabad  |   60000.00 |   2022-01-14   |
| 3  | komal    | 23  |  Chennai    |   60000.00 |   2023-10-08   |
| 5  | chaitali | 23  |  Ranchi     |   60000.00 |   2022-04-18   |
| 6  | Hardhik  | 19  |  Noida      |   44200.09 |   2023-06-04   |
+----+----------+-----+-------------+------------+----------------+

NOT BETWEEN operator with IN operator

In SQL, we can use the NOT BETWEEN operator in combination with the IN operator to select values that are outside a range and also do not match with the specified values.

Example

In the following query, we are selecting the employees whose salary is NOT between 40000 and 50000. In addition; we are not retrieving the employees who are living in Chennai, Ranchi, and Hyderabad using IN operator in SQL.

SQL> SELECT * FROM EMPLOYEE
WHERE SALARY NOT BETWEEN 40000 AND 45000
AND ADDRESS IN ('chennai', 'Ranchi', 'Hyderabad');

Output

On executing the above query, the output is displayed as follows −

+----+----------+-----+-------------+------------+----------------+
| ID |   NAME   | AGE |   ADDRESS   |   SALARY   |   JOIN_DATE    |
+----+----------+-----+-------------+------------+----------------+
| 2  | Ramesh   | 21  |  Hyderabad  |   60000.00 |   2023-01-02   |
+----+----------+-----+-------------+------------+----------------+