The BETWEEN condition is used to determine whether the value of an expression falls within the range defined by two other expressions.
Syntax
The syntax for the BETWEEN condition is as follows:
expr1 [ NOT ] BETWEEN expr2 AND expr3
The value of expr1 NOT BETWEEN expr2 AND expr3 is equivalent to NOT (expr1 BETWEEN expr2 AND expr3), and the value of expr1 BETWEEN expr2 AND expr3 is equivalent to expr2 <= expr1 AND expr1 <= expr3.
The expr1, expr2, and expr3 parameters must all be numeric, character, or datetime expressions. If expr3 < expr2, the range is empty. If expr1 is NULL, the result is NULL. If expr1 is not NULL, the value is generally FALSE, and it is TRUE when the NOT keyword is used.
In SQL, expr1 may be evaluated multiple times. In PL/SQL, the expression is guaranteed to be evaluated only once.
If the expressions are not of the same data type, OceanBase Database implicitly converts them to a common data type. If the expressions cannot be converted to the same data type, an error is returned.
Examples
Query the information of employees whose salaries are between 2100 and 3500, and sort the results by employee number.
SELECT * FROM emp WHERE salary BETWEEN 2100 AND 3500 ORDER BY empno;