PL/SQL BETWEEN

Introduction to PL/SQL BETWEEN

PL/SQL BETWEEN clause is the operator used in PL/SQL to recognize whether a particular value lies between the specified values of the parameters that are specified in the between function. The between function returns a Boolean value that can be either true or false. If the specified value is between the range of the values specified in ascending format in the parameters of the between, we get true or in other case, we get a false value. Here we will see how we can make the use of between clause in PL/SQL, the usage of the function and the implementation along with the examples.

Syntax of PL/SQL BETWEEN

The syntax of the PL/SQL BETWEEN operator is given below:

Expression/ column value BETWEEN lower value of range AND higher value of range

In the above syntax, the expression can be any value which evaluates to a single value which can be either string or number depends on the range of value that we have specified in lower and higher value of range.

The between clause or operator will return a true value only when we have specified the range such that the value fits in it and satisfies the following conditions:

• Column value or evaluated expression value > = lower value of range
• Column value or evaluated expression value < = higher value of range

NOT BETWEEN

There is one more version of BETWEEN which is NOT BETWEEN. This operator works in a reverse manner than that of the BETWEEN operator. It is just the negation of the BETWEEN operator.

The syntax of NOT BETWEEN clause is as shown below:

Expression/ column value NOT BETWEEN lower value of range AND higher value of range

The NOT BETWEEN clause returns a true value when the specified expression value or the value of the column does not lies in between the lower and higher values of the range and false if the value is within the range.

This means that the NOT BETWEEN clause satisfies following conditions:

• Column value or evaluated expression value < lower value of range
• Column value or evaluated expression value > higher value of range

Examples of PL/SQL BETWEEN

Given below are the examples of PL/SQL BETWEEN:

Example #1

Suppose, we have one table named employee_details that stores the details and information about the employees of a particular multinational company. We want to retrieve the details of only that employees and staff whose salary is between the range of 10000 and 20000. In this case, we can make the use of between operator in where clause of our query statement. Firstly, let us retrieve all the data of the table employee_details and study its contents by using the following query statement.

Code:

SELECT * FROM [employee_details];

The output of the execution of the above query statement is as shown below retrieving all the rows of employee_details table.

Output:

Now, to retrieve only specific data belonging to the group who have their salary between 10000 and 20000, we can make the use of following query statement.

Code:

SELECT
f_name as "First Name", l_name as "Last Name",
email_id as "Email Id", mobile_number as "Mobile Number",
joining_date as "Joining Date", salary
FROM [employee_details] WHERE salary
BETWEEN 10000 AND 20000;

The output of execution of above query statement is as shown below retrieving all the rows of employee_details table whose salary is between 10000 and 20000 inclusive.

Output:

Example #2

Consider one more table named contact details which is present in our database and helps to store the additional contact details of the employees in it. Let us firstly see the contents of this table by executing the following query statement.

Code:

SELECT * FROM [contact_details]

The output of execution of above query statement is as shown below retrieving all the rows of contact_details table.

Output:

Now, to retrieve only specific data belonging to the group who have their employee ids between 108 and 112, we can make the use of following query statement.

Code:

SELECT employee_id,
employee_name as "Name of Employee",
city as "Living Area",
mobile_number as "Contact Details"
FROM [contact_details] WHERE employee_id
BETWEEN 108 AND 112;

The output of execution of above query statement is as shown below retrieving all the rows of contact_details table whose employee id is between 108 and 112 inclusive.

Output:

Example #3

Let us take one more example where we will try to retrieve all the records of the contact details table having the employees whose employee id does not lie between the range of 108 and 112. This can be simply done by changing the above query statement and replacing the occurrence of BETWEEN operator with NOT BETWEEN operator.

Code:

SELECT employee_id, employee_name as "Name of Employee",
city as "Living Area", mobile_number as "Contact Details"
FROM [contact_details] WHERE
employee_id NOT BETWEEN 108 AND 112;

The output of execution of above query statement is as shown below retrieving all the rows of contact_details table whose employee id is not between 108 and 112.

Output:

Example #4

We can even make the use of the BETWEEN operator in the query statement to retrieve the records having the certain column whose values are actually not numbers but strings. Let us consider the same table employee_details. Suppose, we want to retrieve the records whose name of the employee begins with a character that lies between K and T. In that case, we can use our between operator in our query statement in the following way.

Code:

SELECT
f_name as "First Name", l_name as "Last Name",
email_id as "Email Id", mobile_number as "Mobile Number",
joining_date as "Joining Date", salary
FROM [employee_details] WHERE f_name
BETWEEN 'K' AND 'T';

The output of execution of above query statement is as shown below retrieving all the rows of employee_details table whose employee names begin with a character that is in between K and T.

Conclusion

We can make the use of the between operator in where clause of select, delete of update statements in PL/SQL. The between operator helps us to specify the range that a certain column should belong to while retrieving the result set.

Recommended Articles

This is a guide to PL/SQL BETWEEN. Here we discuss the introduction, NOT BETWEEN and examples of PL/SQL BETWEEN respectively. You may also have a look at the following articles to learn more –

1. Triggers in PL/SQL
2. PL/SQL Collections
3. PL/SQL Data Types
4. Loops in PL/SQL