What is the usage of between in oracle

In Oracle, the between operator is used to select row data with values ​​within a range. When the BETWEEN operator is used to form search conditions for the rows returned by the statement, only rows whose values ​​are within the specified range are returned. , the syntax is "expression [NOT] BETWEEN low AND high".

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

What is the usage of between in oracle

The BETWEEN operator allows you to specify the range to be tested. When you use the BETWEEN operator to form search criteria for the rows returned by a 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 the lower limit value of the range to be tested and Upper limit. The low and hight values ​​can be literals or expressions.

expression - is the expression tested within the range defined by low and hight. To be able to be compared, 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

Please refer to the following products table:

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

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

In this example, we compare the value in the standard cost (standard_cost) column to the range between 500 (inclusive) and 600 (inclusive). 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;

Execute the above query statement and get the following results -

Recommended tutorial: "Oracle Video Tutorial 》

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