SQL Between Operator

SQL Between operator is a logical operator in the Structured Query Language. The Between operator is used to retrieve data within the range specified in the condition in the query.

The SELECT between operators is used to retrieve the data: text, number, or date.

Between means within the two values, so between operators includes two values: a start value and an end value.

We can use between operator with Select statement in the where clause

We can also use between operator with Delete statement and update statement.

In Delete Statement, we use between operator to delete the values between the two values or specified within the range and with Update statement to modify the values.

Syntax of BETWEEN Operator in SQL

SELECT COLUMNNAME1, COLUMNAME2 FROM TABLENAME WHERE COLUMNNAME BETWEEN VAL_1 AND VAL_2

Here, val_1 is starting value, and val_2 is its ending value

Let's understand the BETWEEN operator in SQL with examples.

BETWEEN OPERATOR WITH SELECT STATEMENT: -

SELECT statements are used to retrieve the data from the SQL table. We used BETWEEN operator with the SELECT statement to fetch the records between two values from the SQL table.

Consider the following tables along with the given records.

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID	AGE	DOJ
1001	Vaibhav	Sharma	65000	PUNE	ORACLE	1	23	2021-09-20
1002	Nikhil	Vani	60000	NOIDA	ORACLE	1	21	2021-09-23
1003	Vaibhavi	Mishra	50000	JAIPUR	FMW	2	22	2021-09-30
2001	Ruchika	Jain	55500	CHANDIGARH	ORACLE	1	22	2021-09-30
2002	Prachi	Sharma	65500	PUNE	FMW	2	23	2021-09-20
2003	Bhavesh	Jain	50000	MUMBAI	TESTING	4	21	2021-09-23
3001	Deepam	Jauhari	55500	PUNE	JAVA	3	21	2021-09-23
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2	22	2021-09-30
3003	Pranoti	Shende	58500	MUMBAI	JAVA	3	23	2021-09-20
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4	23	2021-09-20

Example 1: Write a query to display the records from the employee table whose employee salary is between 48500 and 60000.

SELECT * FROM EMPLOYEE WHERE SALARY BETWEEN 48500 AND 60000;

In the above statement, we fetched all the details from the employee table whose employee salary is between 48500 and 60000.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID	AGE	DOJ
1002	Nikhil	Vani	60000	NOIDA	ORACLE	1	21	2021-09-23
1003	Vaibhavi	Mishra	50000	JAIPUR	FMW	2	22	2021-09-30
2001	Ruchika	Jain	55500	CHANDIGARH	ORACLE	1	22	2021-09-30
2003	Bhavesh	Jain	50000	MUMBAI	TESTING	4	21	2021-09-23
3001	Deepam	Jauhari	55500	PUNE	JAVA	3	21	2021-09-23
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2	22	2021-09-30
3003	Pranoti	Shende	58500	MUMBAI	JAVA	3	23	2021-09-20

Example 2: Write a query to display employee id, first name, last name, salary, and city from employee table whose employee city is between Mumbai and Pune.

SELECT EMPLOYEEID, FIRST_NAME, LAST_NAME, SALARY, CITY FROM EMPLOYEE WHERE CITY BETWEEN 'MUMBAI' AND 'PUNE';

In the above statement, we fetched the employee id, employee names, employee city, and salary from the employee table of those employees whose city standard between Mumbai and Pune.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY
1001	Vaibhav	Sharma	65000	PUNE
1002	Nikhil	Vani	60000	NOIDA
2002	Prachi	Sharma	65500	PUNE
2003	Bhavesh	Jain	50000	MUMBAI
3001	Deepam	Jauhari	55500	PUNE
3003	Pranoti	Shende	58500	MUMBAI
4001	RAJESH	GOUD	60500	MUMBAI

Example 3: Write a query to display employee id, first name, last name, salary, and department and manager id from employee table whose employee department is between Java and Oracle or salary between 45000 and 55000.

SELECT EMPLOYEEID, FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT, MANAGERID FROM EMPLOYEE WHERE DEPARTMENT BETWEEN 'JAVA' AND 'ORACLE' OR SALARY BETWEEN 45000 AND 55000;

We have used multiple BETWEEN operators and OR operators in the above statement. We fetched the employee id, first name, last name, salary, department, and manager id from the employee table. The first BETWEEN operator is used to fetch the above details of those employees whose department name is between Java and Oracle. After the first BETWEEN operator, it will switch to another BETWEEN operator, which is used to fetch the above details of those employees whose salary is between 45000 and 55000. The above statement will display both BETWEEN operator fetched records because we used OR operator in the statement.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	DEPARTMENT	MANAGERID
1001	Vaibhav	Sharma	65000	ORACLE	1
1002	Nikhil	Vani	60000	ORACLE	1
1003	Vaibhavi	Mishra	50000	FMW	2
2001	Ruchika	Jain	55500	ORACLE	1
2003	Bhavesh	Jain	50000	TESTING	4
3001	Deepam	Jauhari	55500	JAVA	3
3002	ANUJA	WANRE	50500	FMW	2
3003	Pranoti	Shende	58500	JAVA	3

Example 4: Write a query to display employee id, first name, last name, salary, and city and manager id from employee table whose employee city is between Chandigarh and Pune and salary between 45000 and 60000.

SELECT EMPLOYEEID, FIRST_NAME, LAST_NAME, SALARY, CITY, MANAGERID FROM EMPLOYEE WHERE CITY BETWEEN 'CHANDIGARH' AND 'PUNE' AND SALARY BETWEEN 45000 AND 60000;

We have used multiple BETWEEN operators and OR operators in the above statement. We fetched the employee id, first name, last name, salary, city, and manager id from the employee table. The first BETWEEN operator is used to fetch the above details of those employees whose city name is between Chandigarh and Pune. After the first BETWEEN operator, it will switch to another BETWEEN operator, which is used to fetch the above details of those employees whose salary is between 45000 and 60000. The above statement will display only those employee records whose city name between Chandigarh and Pune and Salary between 45000 and 60000 because we used AND operator.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	MANAGERID
1002	Nikhil	Vani	60000	NOIDA	1
1003	Vaibhavi	Mishra	50000	JAIPUR	2
2001	Ruchika	Jain	55500	CHANDIGARH	1
2003	Bhavesh	Jain	50000	MUMBAI	4
3001	Deepam	Jauhari	55500	PUNE	3
3002	ANUJA	WANRE	50500	JAIPUR	2
3003	Pranoti	Shende	58500	MUMBAI	3

Example 5: Write a query to display Employee id, First Name, Last Name, Salary, and city from employee table where employee salary between 50000 and 65000 order by city.

SELECT EMPLOYEEID, FIRST_NAME, LAST_NAME, SALARY, CITY, MANAGERID FROM EMPLOYEE WHERE CITY BETWEEN 'CHANDIGARH' AND 'PUNE' AND SALARY BETWEEN 45000 AND 60000;

In the above statement, we fetched the employee id, first name, last name, salary, and city from the employee table of those employees whose salary is between 50000 and 65000. We displayed the result in ascending order by the city column.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY
2001	Ruchika	Jain	55500	CHANDIGARH
1003	Vaibhavi	Mishra	50000	JAIPUR
3002	ANUJA	WANRE	50500	JAIPUR
2003	Bhavesh	Jain	50000	MUMBAI
3003	Pranoti	Shende	58500	MUMBAI
4001	RAJESH	GOUD	60500	MUMBAI
1002	Nikhil	Vani	60000	NOIDA
1001	Vaibhav	Sharma	65000	PUNE
3001	Deepam	Jauhari	55500	PUNE

Example 6: Write a query to display employee id, first name, last name, salary, and city where employee salary is between 45000 and 65000 and where employee city only “Mumbai”,” Pune”.

SELECT EMPLOYEEID, FIRST_NAME, LAST_NAME, SALARY, CITY, MANAGERID FROM EMPLOYEE WHERE CITY BETWEEN 'CHANDIGARH' AND 'PUNE' AND SALARY BETWEEN 45000 AND 60000;

In the above statement, we fetched the employee id, first name, last name, salary, and city from the employee table of those employees whose employee salary is between 45000 and 65000 and city include only Mumbai and Pune.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	CITY	SALARY
1001	Vaibhav	Sharma	PUNE	65000
2003	Bhavesh	Jain	MUMBAI	50000
3001	Deepam	Jauhari	PUNE	55500
3003	Pranoti	Shende	MUMBAI	58500
4001	RAJESH	GOUD	MUMBAI	60500

BETWEEN OPERATOR WITH UPDATE STATEMENT

The UPDATE statement modifies the data present inside the SQL tables. We will use BETWEEN operator with the UPDATE statement to modify the records which satisfy the range specified in the statement.

Syntax of BETWEEN operator with the UPDATE statement:

UPDATE TABLE_NAME SET COLUMN_NAME = VALUES WHERE COLUMN_NAME BETWEEN VALUE1 AND VALUE2;

Consider the following tables along with the given records.

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID	AGE	DOJ
1001	Vaibhav	Sharma	65000	PUNE	ORACLE	1	23	2021-09-20
1002	Nikhil	Vani	60000	NOIDA	ORACLE	1	21	2021-09-23
1003	Vaibhavi	Mishra	50000	JAIPUR	FMW	2	22	2021-09-30
2001	Ruchika	Jain	55500	CHANDIGARH	ORACLE	1	22	2021-09-30
2002	Prachi	Sharma	65500	PUNE	FMW	2	23	2021-09-20
2003	Bhavesh	Jain	50000	MUMBAI	TESTING	4	21	2021-09-23
3001	Deepam	Jauhari	55500	PUNE	JAVA	3	21	2021-09-23
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2	22	2021-09-30
3003	Pranoti	Shende	58500	MUMBAI	JAVA	3	23	2021-09-20
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4	23	2021-09-20

Example 1: Write a query to modify the employee salary by 1.2 times whose employee city is between Mumbai and Noida.

UPDATE EMPLOYEE SET SALARY = SALARY * 1.2 WHERE CITY BETWEEN ‘MUMBAI’ AND ‘NOIDA’;

In the above statement, we increase the employee salary by 1.2 of those whose city is between Mumbai and Pune. 

To cross-check whether the salary of employees is updated or not successfully, we will use the SELECT statement:

SELECT EMPLOYEEID, SALARY, CITY FROM EMPLOYEE WHERE CITY BETWEEN ‘MUMBAI’ AND ’NOIDA’;

EMPLOYEEID	SALARY	CITY
1002	86400	NOIDA
2003	86400	MUMBAI
3003	84240	MUMBAI
4001	87210	MUMBAI

Example 2: Write a query to update the employee city whose date of joining is between 2021-09-20 and 2021-09-22 and salary is between 78500 and 85000.

UPDATE EMPLOYEE SET CITY = 'DELHI' WHERE DOJ BETWEEN '2021-09-20' AND '2021-09-22' AND SALARY BETWEEN 78500 AND 85000;

We have used multiple BETWEEN operators in the statement in the above statement. We are setting employee city to Delhi of those employees whose joining date is between '2021-09-20' AND '2021-09-22' and another between will check whose salary is between 78500 and 85000. If both the BETWEEN operator satisfy the condition for the employee only, then the employee city name will modify.

To cross-check whether the salary of employees is updated or not successfully, we will use the SELECT statement:

SELECT EMPLOYEEID, CITY, SALARY, DOJ FROM EMPLOYEE WHERE DOJ BETWEEN '2021-09-20' AND '2021-09-22' AND SALARY BETWEEN 78500 AND 85000;

EMPLOYEEID	CITY	SALARY	DOJ
2002	DELHI	786400	2021-09-20
3003	DELHI	84240	2021-09-20

BETWEEN OPERATOR WITH DELETE STATEMENT

The DELETE statement is used to delete the records from the SQL table. We use BETWEEN operator with a DELETE statement to delete the records that satisfy the range specified in the statement.

Syntax of BETWEEN operator with the DELETE statement:

DELETE FROM TABLE_NAME WHERE COLUMN_NAME BETWEEN VALUE1 AND VALUE2;

Consider the following tables along with the given records.

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID	AGE	DOJ
1001	Vaibhav	Sharma	78000	PUNE	ORACLE	1	23	2021-09-20
1002	Nikhil	Vani	86400	NOIDA	ORACLE	1	21	2021-09-23
1003	Vaibhavi	Mishra	50000	JAIPUR	FMW	2	22	2021-09-30
2001	Ruchika	Jain	55500	CHANDIGARH	ORACLE	1	22	2021-09-30
2002	Prachi	Sharma	78600	DELHI	FMW	2	23	2021-09-20
2003	Bhavesh	Jain	86400	MUMBAI	TESTING	4	21	2021-09-23
3001	Deepam	Jauhari	66600	PUNE	JAVA	3	21	2021-09-23
3002	ANUJA	WANRE	60600	JAIPUR	FMW	2	22	2021-09-30
3003	Pranoti	Shende	84240	DELHI	JAVA	3	23	2021-09-20
4001	RAJESH	GOUD	87120	MUMBAI	TESTING	4	23	2021-09-20

Example 1: Write a query to delete the employee details from the employee table of those employees whose salary is between 45000 and 65000.

DELETE FROM EMPLOYEE WHERE SALARY BETWEEN 45000 AND 65000;

In the above statement, we are deleting the employee details of those employees whose salary is between 45000 and 65000.

To cross-check whether the employee details are deleted or not successfully, we will use the SELECT statement:

SELECT * FROM EMPLOYEE;

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID	AGE	DOJ
1001	Vaibhav	Sharma	78000	PUNE	ORACLE	1	23	2021-09-20
1002	Nikhil	Vani	86400	NOIDA	ORACLE	1	21	2021-09-23
2002	Prachi	Sharma	78600	DELHI	FMW	2	23	2021-09-20
2003	Bhavesh	Jain	86400	MUMBAI	TESTING	4	21	2021-09-23
3001	Deepam	Jauhari	66600	PUNE	JAVA	3	21	2021-09-23
3003	Pranoti	Shende	84240	DELHI	JAVA	3	23	2021-09-20
4001	RAJESH	GOUD	87120	MUMBAI	TESTING	4	23	2021-09-20

Example 2: Write a query to delete the employee details from the employee table of those employees whose city is between Delhi and Pune.

DELETE FROM EMPLOYEE WHERE CITY BETWEEN ‘DELHI’ AND ‘PUNE’;

In the above statement, we are deleting the employee details of those employees whose city is between Delhi and Pune.

To cross-check whether the employee details are deleted or not successfully, we will use the SELECT statement:

SELECT * FROM EMPLOYEE;