Back to: Oracle Tutorials for Beginners and Professionals
BETWEEN Operator in Oracle with Examples
In this article, I am going to discuss BETWEEN Operator in Oracle with Examples. Please read our previous article, where we discussed IN and NOT IN Operator in Oracle with Examples. At the end of this article, you will understand BETWEEN, and NOT BETWEEN Operators with examples.
BETWEEN Operator in Oracle:
The BETWEEN Operator in Oracle is used to get the values within a specified range. The BETWEEN operator is used to compare the value in a column. This operator tests a range of values with a column value. The BETWEEN operator is written as the word “BETWEEN” followed by a range of values.
Syntax: Expression BETWEEN value1 AND value2; where expression specifies a particular column and value1, value2 defines an inclusive range that expression is compared to.
Understanding BETWEEN Operator in Oracle:
Let us understand BETWEEN and NOT BETWEEN Operator in Oracle with Examples. We are going to use the following Employee table to understand the BETWEEN and NOT BETWEEN operators.
Please execute the below SQL query to drop the existing Employee table and create a new Employee table with the required sample data. We also set the linesize to get the output in the above format.
SET linesize 300; DROP Table Employee; CREATE TABLE Employee ( Id INT PRIMARY KEY, Name VARCHAR(15), Department VARCHAR(10), Salary NUMBER(8, 2), Gender VARCHAR(10), Age INT, City VARCHAR(10) ); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1001, 'John', 'IT', 35000, 'Male', 25, 'London'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1002, 'Smith', 'HR', 45000, 'Female', 27, 'Mumbai'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1003, 'James', 'Finance', 50000, 'Male', 28, 'Delhi'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike', 'Finance', 50000, 'Male', 28, 'London'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda', 'HR', 75000, 'Female', 26, 'Mumbai'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1006, 'Anurag', 'IT', 35000, 'Male', 25, 'London'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1007, 'Priyanla', 'HR', 45000, 'Female', 27, 'Mumbai'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1008, 'Sambit', 'IT', 50000, 'Male', 28, 'London'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1009, 'Pranaya', 'IT', 50000, 'Male', 28, 'London'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1010, 'Hina', 'HR', 75000, 'Female', 26, 'Mumbai');
Between Operator Example in Oracle:
If you want to fetch all the employees from the Employee table where the employee age is between 25 and 27, then we need to write the SELECT SQL Query using the Oracle Between Operator as shown below.
SELECT * FROM Employee WHERE Age BETWEEN 25 AND 27;
Once you execute the above SELECT Query, then you will get the following result set which includes only the employees whose age is between 25 and 27.
Points to Remember:
While working with Between Operator in Oracle we need to remember the following 4 points:
- Between Operator in Oracle returns true if the operand is within a range.
- The Between Operator will return records including the starting and ending values or you can say source and destination values.
- Between operator support only the AND operator in Oracle.
- The BETWEEN Operator takes the values from small to big range in the query.
- Supports with numbers and date values.
NOT BETWEEN Operator Example in Oracle:
The NOT BETWEEN Operator in Oracle is just the opposite of BETWEEN Operator. The BETWEEN operator in Oracle will return records within a range including the starting and ending values specified in the Between Operator. But if you use the NOT keyword along with the BETWEEN operator then it will return data where the column values are not in between the range values.
For example, the following SELECT SQL query will return all the employees from the employee table where the Age not between 25 and 27. The point that you also need to remember is it is also not going to return the employees whose age is 25 and 37.
SELECT * FROM Employee WHERE Age NOT BETWEEN 25 AND 27;
When you execute the above SQL statement, you will get the following result set.
Note: NOT BETWEEN Operator returns true if the value specified is not within the specified range. Supports with numbers and date values. Not between is an exclusive operator which eliminates range limits from
Example:
In this example, we are fetching all employee records whose salary is between 30000 and 70000.
SELECT * FROM Employee WHERE Salary BETWEEN 30000 AND 70000;
When you execute the above SELECT SQL statement, you will get the following result set.
Whenever the lower bound value is larger than the upper bound then it shows ‘no rows selected’. In the below example, we have specified the lower bound as 70000 and the upper bound value as 30000.
SELECT * FROM Employee WHERE Salary BETWEEN 70000 AND 30000;
When you execute the above SELECT SQL statement, you will get the following result set.
Note:
- Lower bound – ‘value ‘must be lower when compared to upper bound value
- Upper bound- ‘value’ must be higher when compared to lower bound value
Between Operator with String Data Type in Oracle:
If you use the BETWEEN Operator with other data types except for numbers and date, then also you will get No Rows Selected as output. In the below query, we are using Between Operator with string data type and hence we will get No Rows Selected as output.
SELECT * FROM Employee WHERE Department BETWEEN IT AND HR;
When you execute the above SELECT SQL statement, you will get the following result set.
In the next article, I am going to discuss LIKE Operator in Oracle with Examples. Here, in this article, I try to explain Between Operator in Oracle with Examples and I hope you enjoy this Between Operator in Oracle with Examples article. If you have any queries regarding the Oracle Between Operator, then please let us know by putting your query in the comment section.