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:
- SELECT Statement
- UPDATE Statement
- DELETE Statement
- INSERT Statement
Syntax of BETWEEN operator in SQL
SELECT column_Name1, column_Name2 ...., column_NameN FROM table_Name WHERE column_name BETWEEN value1 and value2 ; 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
In this article, we have taken the following different SQL examples which help you how to use BETWEEN operator:
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:
SELECT * FROM STUDENT WHERE Marks BETWEEN 80 and 100 ; 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:
SELECT * FROM STUDENT WHERE Marks NOT BETWEEN 80 and 100 ; 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:
SELECT * FROM EMPLOYEE WHERE Emp_Joining BETWEEN '2019/06/05' and '2020/12/20' ; 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.
Syntax of BETWEEN with UPDATE Statement
UPDATE table_name SET column_Name1 = value1, column_Name2 = value2, ...., column_NameN = valueN WHERE ColumnName BETWEEN value1 and value2; 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:
UPDATE STUDENT SET Marks = 98 WHERE Attendance BETWEEN 18 and 20; If you want to see the changes that occurred in the table, then you have to type the following query:
- SELECT * FROM STUDENT WHERE Attendance BETWEEN 18 and 20 ;
| 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:
UPDATE STUDENT SET Marks = 50 WHERE Attendance NOT BETWEEN 18 and 20; If you want to see the changes that occurred in the table, you have to type the following query:
SELECT * FROM STUDENT WHERE Attendance NOT BETWEEN 18 and 20 ; | 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:
UPDATE EMPLOYEE SET Emp_Salary = 25000 WHERE Emp_Joining BETWEEN '2019/06/05' and '2020/12/20' ; If you want to see the changes that occurred in the table, then you have to type the following query:
SELECT * FROM EMPLOYEE WHERE Emp_Joining BETWEEN '2019/06/05' and '2020/12/20' ; | 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 |
Leave a Reply