Updated March 10, 2023
Introduction to DB2 between
DB2 between clause is the operator used in DB2 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 cases, we get a false value. In this article, we will study how we can make the use of between clauses in DB2, the usage of the function, and the implementation along with the examples.
Syntax
The syntax of the DB2 between operator is as shown below –
Expression/ column value BETWEEN lower value of range AND the higher value of the range
In the above syntax, the expression can be any value that evaluates to a single value that can be either string or number depending on the range of value specified in the lower and higher value of the 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
And
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 the NOT BETWEEN clause is as shown below –
Expression/ column value NOT BETWEEN lower value of range AND the higher value of the range
The NOT BETWEEN clause returns a true value when the specified expression value or the value of the column does not lie 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 the following conditions –
Column value or evaluated expression value < lower value of the range
Or
Column value or evaluated expression value > higher value of the range
Examples
Let us understand the implementation and usage of the between operators by having a look at some of the examples.
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 –
SELECT * FROM [employee_details];
The output of the execution of the above query statement is as shown below, retrieving all the rows of the employee_details table.
Now, to retrieve only specific data belonging to the group who have their salary between 10000 and 20000, we can make the use of the following query statement –
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 the execution of the above query statement is as shown below, retrieving all the rows of employee_details table whose salary is between 10000 and 20000 inclusive.
Example #2
Consider one more table named contact details present in our database and help store the additional contact details of the employees in it. Let us first see the contents of this table by executing the following query statement –
SELECT * FROM [contact_details]
The output of the execution of the above query statement is as shown below, retrieving all the rows of contact_details table –
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 the following query statement –
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 the execution of the above query statement is as shown below, retrieving all the rows of contact_details table whose employee id is between 108 and 112 inclusive.
Example #3
Let us take one more example where we will try to retrieve all the records of the contact details table with employees whose employee id does not lie between 108 and 112. This can be simply done by changing the above query statement and replacing the occurrence of BETWEEN operator with the NOT BETWEEN operator. Our query statement will now look as shown below –
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 the execution of the above query statement is as shown below, retrieving all the rows of contact_details table whose employee id is not between 108 and 112.
Example #4
We can even make the use of the BETWEEN operator in the query statement to retrieve the records having a 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 the operator in our query statement in the following way –
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 the execution of the 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 – DB2 between
We can make the use of the between the operator in where clause of select, delete or update statements in DB2. 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 DB2 between. Here we discuss how we can make the use of between clauses in DB2, the usage of the function, and the implementation along with the examples. You may also have a look at the following articles to learn more –
