Updated July 3, 2023
What is BETWEEN Operator in Oracle?
The Oracle BETWEEN operator is a range-based operator that works on range-based conditions and allows us to provide a range-based condition to fetch the data that falls under the given range. In this topic, we are going to learn about BETWEEN in Oracle.
Points of Concentration:
- This operator is used to display rows based on a range of values.
- The declared range is inclusive.
- The lower limit should be declared first.
- The negation of this operator is NOT BETWEEN…AND….
- This operator can be applied in a SELECT, INSERT, UPDATE, or DELETE statement.
- This operator can use NUMBER, CHARCTER, or DATE data types.
The syntax for BETWEEN Operator in Oracle
The syntax is mentioned below:
Syntax:
SELECT Column_1, Column_2,..., Column_N FROM Table_Name WHERE Column_Name [NOT] BETWEEN Value1 AND Value2;
Description:
- Col_1/2/n: The column(s) or calculation as per your requirement.
- Table_Name: As per your requirement.
- WHERE: It’s mandatory to use this operator.
- Column_Name: The condition will be applied to the column to filter the data.
- Value1/2: These two values will create an inclusive range to compare the data in the Column_Name column.
- NOT: This is a negation of the BETWEEN operator. It excludes the records which fall under the range.
Example(with/without Negation)
Let us see with the help of examples:
Without Negation
SQL> SELECT Ename, Deptno, Sal FROM Emp WHERE Sal BETWEEN 1250 AND 2975;
Output:
With Negation
SQL> SELECT Ename, Deptno, Sal FROM Emp WHERE Sal NOT BETWEEN 1250 AND 2975;
Output:
Explanation: The above two examples (without and with negation) clearly shows how the Oracle BETWEEN operator filters the data based on range condition.
Examples of BETWEEN Operators in Oracle
In this section, we’ll see the Implementation of Oracle BETWEEN Operator and its behavior. We will use the sample table (Emp) below with 14 records to understand the Oracle BETWEEN Operator behavior.
Query:
SQL> SELECT * from Emp;
Output:
Example #1 – with NUMBER data type
Code:
SQL > SELECT Ename, Deptno, Sal FROM Emp WHERE Sal BETWEEN 1250 AND 2975;
Output:
In the above SELECT statement, the BETWEEN operator creates an inclusive range of the given values (1250, 2975), searches for the values in the Sal column that fall in the range and fetches the records.
Code:
SQL > SELECT Ename, Deptno, Sal FROM Emp WHERE Sal NOT BETWEEN 1250 AND 2975;
Output:
The above SELECT statement is the opposite of the previous example, as the BETWEEN operator creates an inclusive range of the given values (1250, 2975), searches for the values in the Sal column, and fetches the records that fall in the field. But because of the NOT keyword, which is used with the BETWEEN operator, the records that fall in the range get excluded from the result and return only those not in or outside the range.
Example #2 – with DATE data type
Code:
SQL> SELECT Ename, Deptno, Hiredate FROM Emp WHERE Hiredate
BETWEEN '17-FEB-81' AND '23-JAN-82';
Output:
In the above SELECT statement, the BETWEEN operator works on the DATE data type, which searches for the records (based on the date range) in the Hiredate column, which is the date data type, and returns the records which fall under the given date range (inclusive).
Code:
SQL> SELECT Ename, Deptno, Hiredate FROM Emp WHERE Hiredate
NOT BETWEEN '17-FEB-81' AND '23-JAN-82';
Output:
The above SELECT statement negates the previous SELECT statement because the NOT keyword allows fetching the records outside the given date range and returns the records.
Tips:
- Values should be in the same format as the date data type column.
- If the value(s) is/are in a different format, then the column data format should be matched. Format the date data type column to match the value format using the functions To_date or To_char.
Example #3 – with VARCHAR data type
Code:
SQL> SELECT Ename, Deptno, Sal, Job FROM Emp WHERE Job
BETWEEN 'MANAGER' AND 'SALESMAN';
Output:
The BETWEEN operator creates an inclusive range of the given string values (‘MANAGER’ AND ‘SALESMAN’). But when it starts a range-based search for the records, it compares character-wise (like Dictionary words arrangement) and fetches the record(s). And the below SELECT statement is a negation of the above SELECT statement.
Code:
SQL> SELECT Ename, Deptno, Sal, Job FROM Emp WHERE Job
NOT BETWEEN 'MANAGER' AND 'SALESMAN';
Output:
Tips:
- The character functions (UPPER, LOWER, INITCAP, etc.) can match the value format with the column data format. See the example below.
Code:
SQL> SELECT Ename, Deptno, Sal, Job FROM Emp WHERE
'manager' AND LOWER ('PRESIDENT');
Output:
- If the format of values and column data in the BETWEEN operator doesn’t match, it will return a no-row selected output. See the example below.
Code:
SQL> SELECT Ename, Deptno, Sal, Job FROM Emp WHERE
LOWER (Job) BETWEEN 'manager' AND 'PRESIDENT';
Output:
In this SELECT statement, the LOWER function converts job column data into the lower case, but the BETWEEN operator contains one value (PRESIDENT) in the upper case, which does not match the column data format.
Example #4 – DML operation (INSERT, UPDATE, DELETE)
The BETWEEN operator can be applied to the data manipulation operation in the below example.
Before UPDATE
SQL> SELECT Ename, Deptno, Sal, Job FROM Emp WHERE
Job BETWEEN 'MANAGER' AND 'PRESIDENT';
Output:
UPDATE Query:
SQL> UPDATE Emp SET Sal= Sal*2 WHERE Job BETWEEN 'MANAGER' AND 'PRESIDENT';
After UPDATE
SQL> SELECT Ename, Deptno, Sal, Job FROM Emp WHERE
Job BETWEEN 'MANAGER' AND 'PRESIDENT';
Output:
Tips:
- The BETWEEN operator is handy while performing NON-EQUI Join on range-based conditions.
- Take extra care about column data format and values format in the BETWEEN operator; both should be matched.
- The function can match the column data format, and values format with each other.
Conclusion
Oracle BETWEEN … AND … is an operator that allows us to do a range-based search and fetch the record(s) that falls in the range. You can retrieve records that do not fall within the specified range by using the NOT keyword in conjunction with this operator. To exclude records within a specific range, use the operator with the NOT keyword.
Recommended Articles
This is a guide to BETWEEN in Oracle. Here we discuss the Implementation of the Oracle BETWEEN operator with appropriate syntax and respective examples. You may also have a look at the following articles to learn more –
