How to Use BETWEEN in SQL

In this SQL article, you will learn how to use BETWEEN keyword in the SQL statements in the database. Here, we will also discuss the NOT BETWEEN operator in detail.

What is Between in SQL?

The BETWEEN keyword is an operator in Structured Query Language. It allows the database users to access the values within the specified range.

We can easily use BETWEEN operator in the WHERE clause of INSERT, UPDATE, SELECT, and DELETE SQL queries.

This operator evaluates to TRUE when the column value is less than or equal to a high value and greater than or equals to a low value. The starting and ending value is included in the BETWEEN operator.

Syntax of BETWEEN logical operator:

In the syntax, Value_1 specifies the beginning value of the range, and Value_2 specifies the ending value of the range.

If you want to use the BETWEEN operator in the SQL statements, you have to follow the following steps in the same sequence:

1. Create a database in the system.
2. Create the new SQL table.
3. Insert the data in the table.
4. View the Inserted data.
5. Use the BETWEEN operator to view the data of the table in multiple ways.

Now, we are going to explain each step one by one in brief with the best SQL example:

Step 1: Create a ssimple new database

First, you have to create a new database in the Structured Query Language database. The following CREATE statement creates the new IIT_College Database:

Step 2: Create a new table

Now, use the following SQL syntax to create the new table in the database:

The following CREATE statement creates the Faculty_Info table in the IIT_College Database:

Step 3: Insert the Data into the table

The following INSERT queries insert the records of Faculties in the Faculty_Info table:

Step 4: View the Inserted Data

The following SELECT statement displays the records of the Faculty_Info table on the computer screen:

Step 5: Use BETWEEN operator

The following query uses BETWEEN operator with numeric data:

This SELECT query shows all faculties in the output whose salary lies between 25000 and 40000.

The result of the above statement is shown in the following output:

The following query uses BETWEEN logical operator with character or text values:

This query shows the record of all those Faculties whose City name starts with any letter between D and L.

The following query uses BETWEEN logical operator with Date format

This query shows the record of all those Faculties whose joining is between 2020-01-02 and 2021-06-21.

The following query uses the DISTINCT clause with BETWEEN operator in SELECT query:

The following query updates the data of the table using BETWEEN operator in the UPDATE command:

This UPDATE query updates the salary of those faculties whose joining date is between

2019-07-15 and 2021-01-02.

To check the result of the above query, type the following SELECT command:

What is NOT BETWEEN in SQL?

The NOT BETWEEN is an operator in Structured Query Language, which is just opposite to the BETWEEN operator. It allows us to access those values from the table which do not come in the given range.

The NOT BETWEEN operator can be used in the INSERT, UPDATE, SELECT, and DELETE SQL queries.

The syntax for using the NOT BETWEEN operator in Structured Query Language is given below:

If you want to use the NOT BETWEEN operator in the statements of SQL, you have to follow the given steps below one by one in the same sequence:

1. Create a database in the system.
2. Create the new SQL table.
3. Insert the data in the table.
4. View the Inserted data.
5. Use the NOT BETWEEN operator to view the specific data of different columns.

Step 1: Create the Simple new database

First, you have to make a new database in Structured Query Language.

The following query creates the new Civil_Industry Database in SQL server:

Step 2: Create the New table

The following query creates the Worker_Info table in the Civil_Industry Database:

Step 3: Insert the Values

The following INSERT queries insert the records of workers in the Worker_Info table:

Step 4: View the Table's Data

The following query shows the data of the Worker_Info table.

Step 4: Use NOT BETWEEN operator

The below statement uses the NOT BETWEEN operator with numeric data:

This SELECT query shows all Workers in the output whose salary is not greater than or equal to 25000 less than or equals 40000.

The result of the above query is shown in the following output:

The following query uses NOT BETWEEN logical operator with character or text values:

This query shows the record of all those Workers whose City names do not start with any letter between D and L.