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

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:

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:

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

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:

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

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:

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

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:

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:

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

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:

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

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: