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:

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

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz

SQL BETWEEN

Syntax of BETWEEN operator in SQL

Examples of BETWEEN Operator in SQL

BETWEEN with UPDATE Statement

Syntax of BETWEEN with UPDATE Statement

Examples of BETWEEN with UPDATE Statement

Comment / Reply From

You May Also Like

Popular Post

Language

Newsletter