How to use the BETWEEN operator in SQL

In this entire SQL article, we will understand and learn about the BETWEEN operator concept and how to use it in SQL.

What is the BETWEEN operator in SQL?

The Between operator fetches records which exist between the starting and ending value in the given expression query. The SELECT between operators retrieves text, number, or date data.

The BETWEEN operator includes the starting value and the ending value.

The syntax of BETWEEN operators with the SELECT statement:

SELECT Column_Name_1, Column_Name_2, Column_Name_3 FROM Table_Name WHERE Column_Name BETWEEN VALUE_1 AND VALUE_2

Here, value_1 is starting value, and value_2 is its ending value.

The syntax of BETWEEN operators with the UPDATE statement:

UPDATE Table_Name SET Column_Name = value WHERE Column_Name BETWEEN VALUE_1 AND VALUE_2; 

The syntax of BETWEEN operators with the DELETE statement:

DELETE FROM TABLE_NAME WHERE COLUMN_NAME BETWEEN VALUE_1 AND VALUE_2;

There are following steps which help to learn for how to use the BETWEEN operator in the SQL query:

1 We have to create a newly named database. If you have already created a database (existing database), then use the old database by using the USE command.

2. After selecting the database, we will create a new table or use the existing table.

3 Add records in the newly created table using the INSERT statement

4 After adding records to the newly created table, we will display the data from the table using the SELECT statement.

Step 1: New Database or use old Database.

We have an existing database. So, we will use old database name, Company.

USE Company;

We can use the below syntax to create a new database that doesn't exist in the database.

CREATE DATABASE database_name;

After creating the new database, use the newly created database using the USE command.

Step 2: New Table or use old Table.

We have an old table. So, we will use the old table named Employees.

If you don’t have the old table, then use the below syntax to create the table.

CREATE TABLE table_name(

CREATE TABLE table_name(
Column_name_1 datatype(column size),
Column_name_2 datatype(column size),
Column_name_3 datatype(column size)
);

Step 3: Add new records to the new table.

Use below syntax to insert new records in the table:

INSERT INTO Table_Name VALUES(value_1, value_2, value_3);

The below syntax is used to display the data from the table:

SELECT * FROM Table_Name;

The following query will display the records of Employees

SELECT * FROM Employees;

The output of the above SELECT query is:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
1002	VAIBHAV	SHARMA	60000	NOIDA	C#	5
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	4
5002	SANKET	CHAUHAN	70000	HYDERABAD	JAVA	3
5003	ROSHAN	NEHTE	48500	CHANDIGARH	C#	5
6001	RAHUL	NIKAM	54500	BANGALORE	TESTING	4
6002	ATISH	JADHAV	60500	BANGALORE	C#	5
6003	NIKITA	INGALE	65000	HYDERABAD	ORACLE	1

Step 4: We are ready to use the BETWEEN operator in the queries.

Let’s understand the BETWEEN operator with the help of examples.

Example 1: Execute a query to fetch employee information from the employees' table where employee salary is between 48000 and 60000.

SELECT * FROM EMPLOYEES WHERE SALARY BETWEEN 48000 AND 60000;

In the above query, we fetched all the employee records from the employee table whose employees' Salary is between 48000 and 60000.

The output of the above query is shown as:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1002	VAIBHAV	SHARMA	60000	NOIDA	C#	5
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	4
5003	ROSHAN	NEHTE	48500	CHANDIGARH	C#	5
6001	RAHUL	NIKAM	54500	BANGALORE	TESTING	4

As we can see in the output, only those employees records are displayed whose employee salary is between 48000 and 60000.

Example 2: Execute a query to fetch employee information from the employees table where the employee city is between Chandigarh and Pune.

SELECT * FROM EMPLOYEES WHERE CITY BETWEEN 'CHANDIGARH' AND 'PUNE'; 

This query will displaye all the employee information from the employee table whose employees’ city is between ‘Chandigarh’ and ‘Pune’.

The output of the above query is shown as:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
1002	VAIBHAV	SHARMA	60000	NOIDA	C#	5
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	4
5002	SANKET	CHAUHAN	70000	HYDERABAD	JAVA	3
5003	ROSHAN	NEHTE	48500	CHANDIGARH	C#	5
6003	NIKITA	INGALE	65000	HYDERABAD	ORACLE	1

As we can see in the output, only those employees' records are displayed whose employee city is between 'Chandigarh' and 'Pune'.

Example 3: Execute a query to modify the employee salary by 1.2 whose employee city is between ‘Delhi’ and ‘Noida’.

UPDATE EMPLOYEES SET SALARY = SALARY * 1.2 WHERE CITY BETWEEN ‘DELHI’ AND ‘NOIDA’;

In the above statement, we increase the salary of those employees by 1.2 whose city is between 'Delhi' and 'Noida’. 

We will execute the SELECT query to verify whether the employee’s information is successfully modified or not.

SELECT * FROM EMPLOYEES WHERE CITY BETWEEN ‘DELHI’ AND ’NOIDA’;

 The output of the above query is shown as:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1002	VAIBHAV	SHARMA	72000	NOIDA	C#	5
1003	NIKHIL	VANI	60600	JAIPUR	FMW	2
2003	RUCHIKA	JAIN	60000	MUMBAI	C#	5
3002	ANUJA	WANRE	60600	JAIPUR	FMW	2
3003	DEEPAM	JAUHARI	70200	MUMBAI	JAVA	3
4001	RAJESH	GOUD	72600	MUMBAI	TESTING	4
4002	ASHWINI	BAGHAT	65400	NOIDA	JAVA	3
4003	RUCHIKA	AGARWAL	72000	DELHI	ORACLE	1
5001	ARCHIT	SHARMA	66600	DELHI	TESTING	4
5002	SANKET	CHAUHAN	84000	HYDERABAD	JAVA	3
6003	NIKITA	INGALE	72800	HYDERABAD	ORACLE	1

As we see in the output, employee records are updated whose city is between 'Delhi' and 'Noida’.

Example 4: Execute a query to remove the employee information from the employees' table of those employees whose Salary is between 50000 and 65000.

DELETE FROM EMPLOYEES WHERE SALARY BETWEEN 45000 AND 65000;

In the above statement, we are removing the employee information of those whose Salary is between 50000 and 65000.

We will execute the SELECT query to verify whether the employee’s information is successfully deleted or not.

SELECT * FROM EMPLOYEES;

The output of the above query is shown as:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
1002	VAIBHAV	SHARMA	72000	NOIDA	C#	5
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
3003	DEEPAM	JAUHARI	70200	MUMBAI	JAVA	3
4001	RAJESH	GOUD	72600	MUMBAI	TESTING	4
4002	ASHWINI	BAGHAT	65400	NOIDA	JAVA	3
4003	RUCHIKA	AGARWAL	72000	DELHI	ORACLE	1
5001	ARCHIT	SHARMA	66600	DELHI	TESTING	4
5002	SANKET	CHAUHAN	84000	HYDERABAD	JAVA	3
5003	ROSHAN	NEHTE	48500	CHANDIGARH	C#	5
6003	NIKITA	INGALE	78000	HYDERABAD	ORACLE	1

In the above output, the records are deleted of those employees whose Salary is between 50000 and 65000.