# SQL BETWEEN

The BETWEEN is a logical operator in SQL, which fetches the records from the table within the range specified in the query. We can use this operator to select numbers, text, or dates.

The BETWEEN operator in SQL includes the starting and ending values.

BETWEEN operator is used in the WHERE clause with the following four SQL statements:

1. SELECT Statement
2. UPDATE Statement
3. DELETE Statement
4. INSERT Statement

## Syntax of BETWEEN operator in SQL

The BETWEEN operator returns True if the value of column_name is greater than or equal to the value1 and less than or equal to the value2.

## Examples of BETWEEN Operator in SQL

Example 1: Let's take the following STUDENT table, which helps you to understand the BETWEEN operator:

Roll_No Name Marks Age
1 Raman 95 20
2 Kapil 92 19
3 Arun 85 17
4 Ram 92 18
5 Suman 55 20
6 Sanjay 88 18
7 Sheetal 65 19
8 Rakesh 64 20

Suppose, we want to fetch the record of those students from the below student table whose 'marks' are greater than or equal to 80 and less than or equal to 100. For this operation, we have to perform the following query in Structured Query Language:

This query shows the following table in the SQL output:

Roll_No Name Marks Age
1 Raman 95 20
2 Kapil 92 19
3 Arun 85 17
4 Ram 92 18
6 Sanjay 88 18

Example 2: Let's take the following STUDENT table, which helps you to understand the NOT BETWEEN operator:

Roll_No Name Marks Age
1 Raman 95 20
2 Kapil 92 19
3 Arun 85 17
4 Ram 92 18
5 Suman 55 20
6 Sanjay 88 18
7 Sheetal 65 19
8 Rakesh 64 20

Suppose, we want to fetch the record of those students from the below student table whose marks are neither greater than 80 nor less than 100. For this operation, we have to perform the following query in Structured Query Language:

This query shows the following table in the SQL output:

Roll_No Name Marks Age
5 Suman 55 20
7 Sheetal 65 19
8 Rakesh 64 20

Example 3: Let's take the following EMPLOYEE table, which helps you to understand the BETWEEN operator with dates:

Emp_ID Name Emp_Salary Emp_Joining
1001 Vivek 9000 2021/01/02
1002 Saket 4000 2019/06/05
1003 Raman 3000 2020/02/05
1004 Suraj 6000 2020/04/01
1005 Seenu 5000 2019/12/04
1006 Shubham 10000 2021/02/05
1007 Anaya 4000 2020/12/20
1008 Parul 8000 2019/04/15

Suppose, we want to fetch the record of those employees from the above employee table whose joining data is between 2019/06/05 and 2020/12/20. For this operation, we have to type the following query in SQL:

This query shows the following table in the SQL output:

Emp_ID Name Emp_Salary Emp_Joining
1002 Saket 4000 2019/06/05
1005 Seenu 5000 2019/12/04
1007 Anaya 4000 2020/12/20
1008 Parul 8000 2019/04/15

## BETWEEN with UPDATE Statement

In SQL, we can also use BETWEEN operator in the WHERE clause with the UPDATE statement. The BETWEEN operator updates those records in the table which satisfy the range specified in the query.

### Examples of BETWEEN with UPDATE Statement

Here, we have taken the following two SQL examples which help you how to use BETWEEN operator with UPDATE statement for updating the existing records in the tables:

Example 1: Let's take the following STUDENT table, which helps you to understand the BETWEEN operator with an UPDATE statement:

Roll_No Name Marks Attendance
1 Raman 95 20
2 Kapil 92 17
3 Arun 85 17
4 Ram 92 18
5 Suman 55 19
6 Sanjay 88 17
7 Sheetal 65 19
8 Rakesh 64 20

Suppose, we want to update the Marks of those students whose Attendance is greater or equal to 18 and less than or equal to 20. For this operation, we have to perform the following query in Structured Query Language:

If you want to see the changes that occurred in the table, then you have to type the following query:

Roll_No Name Marks Attendance
1 Raman 98 20
4 Ram 98 18
5 Suman 98 19
7 Sheetal 98 19
8 Rakesh 98 20

Example 2: Let's take the following STUDENT table, which helps you to understand the NOT BETWEEN operator:

Roll_No Name Marks Attendance
1 Raman 95 20
2 Kapil 92 17
3 Arun 85 17
4 Ram 92 18
5 Suman 55 19
6 Sanjay 88 17
7 Sheetal 65 19
8 Rakesh 64 20

Suppose, we want to update the Marks of those students whose Attendance is neither greater and equal to 18 nor less than and equal to 20. For this operation, we have to type the following query in SQL:

If you want to see the changes that occurred in the table, you have to type the following query:

Roll_No Name Marks Attendance
2 Kapil 50 17
3 Arun 50 17
6 Sanjay 50 17

Example 3: Let's take the following EMPLOYEE table, which helps you to understand the BETWEEN operator with dates:

Emp_ID Name Emp_Salary Emp_Joining
1001 Vivek 9000 2021/01/02
1002 Saket 4000 2019/06/05
1003 Raman 3000 2020/02/05
1004 Suraj 6000 2020/04/01
1005 Seenu 5000 2019/12/04
1006 Shubham 10000 2021/02/05
1007 Anaya 4000 2020/12/20
1008 Parul 8000 2019/04/15

Suppose, we want to update the Salary of those employees who join the office between 2019/06/05 and 2020/12/20. For this operation, we have to type the following query in SQL:

If you want to see the changes that occurred in the table, then you have to type the following query:

Emp_ID Name Emp_Salary Emp_Joining
1002 Saket 25000 2019/06/05
1005 Seenu 25000 2019/12/04
1007 Anaya 25000 2020/12/20
1008 Parul 25000 2019/04/15

Next Topic#