EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Oracle Tutorial BETWEEN in Oracle
 

BETWEEN in Oracle

Priya Pedamkar
Article byPriya Pedamkar

Updated July 3, 2023

BETWEEN in Oracle

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

BETWEEN in oracle output 1

With Negation

SQL> SELECT Ename, Deptno, Sal FROM Emp WHERE Sal NOT BETWEEN 1250 AND 2975;

Output:

BETWEEN in oracle output 2

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:

BETWEEN in oracle output 3

Example #1 – with NUMBER data type

Code:

SQL > SELECT Ename, Deptno, Sal FROM Emp WHERE Sal BETWEEN 1250 AND 2975;

Output:

BETWEEN in oracle output 4

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:

BETWEEN in oracle output 5

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:

BETWEEN in oracle output 6

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:

output 7

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:

output 8

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:

output 9

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:

output 10

  • 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:

 output 11

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:

output 12

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:

output 13

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 –

  1. Oracle Operators
  2. Oracle Aliases
  3. Guide to BETWEEN in SQL
  4. Oracle Procedures

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW