What is the usage of oracle between

between is used to select row data with values ​​within a range, the syntax is "SELECT field name FROM table name WHERE expression [NOT] BETWEEN lower limit AND upper limit"; when this keyword is formed for the rows returned by the SELECT statement When searching for a condition, only rows whose values ​​fall within the specified range are returned.

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

The BETWEEN operator can be used to select row data with values ​​within a range in Oracle.

Oracle BETWEEN Operator Syntax

The BETWEEN operator allows you to specify a range to test. When you use the BETWEEN operator to form search conditions for the rows returned by a SELECT statement, only rows whose values ​​fall within the specified range are returned.

The following explains the syntax of the BETWEEN operator:

expression [ NOT ] BETWEEN low AND high

In the above syntax:

• low and high - low and hight specify what is to be tested The lower and upper limits of the range. The low and hight values ​​can be literals or expressions.

• expression - is an expression tested within the range defined by low and hight. To be able to compare, the data types of expression, low, and hight must be the same.

• AND - The AND operator acts as a placeholder to separate low and high values.

• If the value of expression (expression) is greater than or equal to the value of low and less than or equal to the value of hight, the BETWEEN operator returns true.

value >= low AND value <= high

NOT BETWEEN operator negates the result of BETWEEN operator.

Oracle BETWEEN Example

Let’s take a look at some examples of using the Oracle BETWEEN operator.

1. Oracle BETWEEN numerical example

Take the products table below as an example:

The following statement returns All products with a standard cost between 500 and 600:

SELECT
    product_name,
    standard_cost
FROM
    products
WHERE
    standard_cost BETWEEN 500 AND 600
ORDER BY
    standard_cost;

In this example, we compare and filter the range of values ​​in the standard cost (standard_cost) column between 500 and 600. This query only returns products whose standard cost is between the following ranges:

To query for products whose standard cost is not between 500 and 600, replace the NOT operator as follows Add to the above query:

SELECT
    product_name,
    standard_cost
FROM
    products
WHERE
    standard_cost NOT BETWEEN 500 AND 600
ORDER BY
    product_name;

2. Oracle BETWEEN date example

Take the orders table below as an example to demonstrate:

To query customer orders from December 1, 2016 to December 31, 2016, you can use the following statement:

SELECT
    order_id, customer_id, status, order_date
FROM
    orders
WHERE
    order_date BETWEEN DATE '2016-12-01' AND DATE '2016-12-31'
ORDER BY
    order_date;

After executing the statement, you can get the following results:

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of What is the usage of oracle between. For more information, please follow other related articles on the PHP Chinese website!