All Products
Search
Document Center

BETWEEN conditions

Last Updated: Jun 18, 2021

A BETWEEN condition determines whether the value of an expression falls in the interval that is defined by other two expressions.

Syntax

expr1 [ NOT ] BETWEEN expr2 AND expr3

NOT (expr1 BETWEEN expr2 AND expr3)

The expr1, expr2, and expr3 expressions must be the numeric, character, or datetime expressions. In SQL, expr1 may be calculated for multiple times. If an expression appears in Procedural Language for SQL (PL/SQL), make sure that expr1 is calculated only once. If the data types of expressions are not the same, ApsaraDB for OceanBase implicitly converts the expressions to the unified data type. Otherwise, the system returns an error.

Examples

Query the information about employees whose salary ranges from 2,000 to 3,000, and sort the result by using employee IDs.

SELECT * FROM employees WHERE salary BETWEEN 2000 AND 3000 ORDER BY employee_id;