The BETWEEN condition is used to determine whether the value of an expression falls within an interval defined by another two expressions.
Syntax
The syntax of the BETWEEN condition is follows:
expr1 [ NOT ] BETWEEN expr2 AND expr3
expr1 NOT BETWEEN expr2 AND expr3 is equivalent to NOT (expr1 BETWEEN expr2 AND expr3), and expr1 BETWEEN expr2 AND expr3 is equivalent to expr2 <= expr1 AND expr1 <= expr3.
expr1, expr2, and expr3 must all be numeric, character, or datetime expressions. If expr3 < expr2, the interval is empty. If NULL is specified for expr1, the result is NULL. If expr1 is not NULL, this value is generally FALSE, and is TRUE when the NOT keyword is specified.
In SQL, expr1 may be evaluated more than once. If an expression appears in PL, expr1 is calculated only once.
If the expressions are not all of the same data type, OceanBase Database implicitly converts the expressions to the same data type. If the expressions cannot be converted to the same data type, an error is returned.
Examples
Query employees whose salary ranges from 2,100 to 3,500 and sort them by employee ID.
SELECT * FROM emp WHERE salary BETWEEN 2100 AND 3500 ORDER BY empno;