Oracle between clauses

In this tutorial, you will learn how to use the Oracle BETWEEN operator to select row data in a range of values.

Introduction to the Oracle between operator

BETWEENoperator allows you to specify the scope to test. When using the BETWEEN operator to form a search condition for the rows returned by the SELECT statement, only the rows whose values are within the specified range are returned.

BETWEENThe syntax for the following description operator:

expression [ NOT ] BETWEEN low AND high

In the syntax above,

• Low and high- low and hight specifies the lower and upper values of the range to be tested. lowand hight The value can be either text or an expression.
• expression-is low and hight defines the range of expressions that are tested. In order to be able to compare, the expression low and hight data types must be the same.
• AND The and-operator acts low as hight a placeholder for the values that are delimited.

If the value of the expression ( expression ) is greater than or equal to low the value, the hight operator is returned if it is less than or equal to the value BETWEEN true .

value >= low AND value <= high

NOT BETWEENBETWEENThe result of the operator negation operator.

Oracle between sample

Let's look at BETWEEN some examples of using Oracle operators.

1. Oracle between numerical example

See the following table in the sample database products :

The following statement returns all the 500 products for which the standard cost is 600 between:

SELECT    product_name,    standard_costFROM    productsWHERE    standard_cost BETWEEN 500 AND 600ORDER BY    standard_cost;

In this example, we standard_cost compare the values in the standard Cost () column 500 to the range between (included) and 600 (inclusive). The query returns only products that have standard costs between the following ranges:

To query 500 600 for products that are not in and between standard costs, NOT add operators to the above query as follows:

SELECT    product_name,    standard_costFROM    productsWHERE    standard_cost NOT BETWEEN 500 AND 600ORDER BY    product_name;

Execute the above query statement to get the following results-

2. Oracle between date example

We use the tables in the sample database orders to demonstrate:

The following query statement will return orders from the customer for the period from December 1, 2016 to December 31, 2016:

SELECT    order_id, customer_id, status, order_dateFROM    ordersWHERE    order_date BETWEEN DATE ‘2016-12-01‘ AND DATE ‘2016-12-31‘ORDER BY    order_date;

Execute the above query statement to get the following results-

In this tutorial, you learned how to use the Oracle BETWEEN operator to select a specific range of row data.

Oracle between clauses