Using BETWEEN Condition in Oracle

This article is written about how to use the Oracle BETWEEN condition with syntax and examples.

Description

The Oracle BETWEEN situation is used to retrieve values within a range in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the BETWEEN circumstance in Oracle/PLSQL is:

expression BETWEEN value1 AND value2;

Parameters or Arguments

expression A column or calculation. value1 and value2 Two values that create an inclusive range that expression is compared to.

Note

The Oracle BETWEEN condition will return the data where expression is within the vary of value1 and value2 (inclusive).

Example – With Numeric

Let’s seem to be at some Oracle BETWEEN situation examples the use of numeric values. The following numeric instance uses the BETWEEN condition to retrieve values inside a numeric range.

For example:

SELECT *
FROM customers
WHERE customer_id BETWEEN 4000 AND 4999;

This Oracle BETWEEN instance would return all rows from the customers table where the customer_id is between 4000 and 4999 (inclusive). It is equivalent to the following SELECT statement:

SELECT *
FROM customers
WHERE customer_id >= 4000
AND customer_id <= 4999;

Example – With Date

Next, let’s seem to be at how you would use the Oracle BETWEEN situation with Dates. The following date instance uses the BETWEEN circumstance to retrieve values inside a date range.

For example:

SELECT *
FROM order_details
WHERE order_date BETWEEN TO_DATE ('2014/02/01', 'yyyy/mm/dd')
AND TO_DATE ('2014/02/28', 'yyyy/mm/dd');

This Oracle BETWEEN circumstance example would return all files from the order_details desk where the order_date is between Feb 1, 2014 and Feb 28, 2014 (inclusive). It would be equivalent to the following SELECT statement:

SELECT *
FROM order_details
WHERE order_date >= TO_DATE('2014/02/01', 'yyyy/mm/dd')
AND order_date <= TO_DATE('2014/02/28','yyyy/mm/dd');

Example – Using NOT Operator

The Oracle BETWEEN situation can additionally be combined with the Oracle NOT operator. Here is an instance of how you would mix the BETWEEN situation with the NOT Operator.

For example:

SELECT *
FROM customers
WHERE customer_id NOT BETWEEN 3000 AND 3500;

This Oracle BETWEEN instance would return all rows from the customers desk where the customer_id was NOT between 3000 and 3500, inclusive. It would be equivalent to the following SELECT statement:

SELECT *
FROM customers
WHERE customer_id < 3000
OR customer_id > 3500;