BETWEEN condition

The BETWEEN condition is used to determine whether the value of an expression lies within the interval defined by two other expressions.

Syntax

expr1 [ NOT ] BETWEEN expr2 AND expr3

Usage instructions

• The value of expr1 NOT BETWEEN expr2 AND expr3 is equivalent to NOT (expr1 BETWEEN expr2 AND expr3).
• The value of expr1 BETWEEN expr2 AND expr3 is equivalent to expr2 <= expr1 AND expr1 <= expr3.
• All three expression parameters, expr1, expr2, and expr3, must be numeric, character, or datetime expressions.
• If expr3 < expr2, the interval is empty.
• If expr1 is NULL, the result is NULL.
• If the expressions are not all of the same data type, OceanBase Database implicitly converts them to a unified data type.

Examples

Query employee information with salaries in the range of 2,100 to 3,500 and sort the results by employee ID.

obclient> CREATE TABLE emp (
  empno INT,
  salary DECIMAL(10, 2)
);
obclient> INSERT INTO emp VALUES
  (1, 2500),
  (2, 4000),
  (3, 1800);

obclient> SELECT * FROM emp WHERE salary BETWEEN 2100 AND 3500 ORDER BY empno;