Operators generally connect individual data items, such as operands or parameters, and return the results. In terms of syntax, an operator can appear before or after an operand, or between two operands. Special characters or keywords are generally used to represent operators. For example, slashes (/) are used to represent division operators. This topic discusses the operators that excludes logical or BOOLEAN operators. You cannot use these operators as the
HAVING conditions for queries or subqueries.
This topic describes the following operators in detail:
Hierarchical query operators
Unary and binary operators
Operators can be divided into two categories:
Unary operators: A unary operator performs an operation on only one operand. The following general format is used for unary operators:
Binary operators: A binary operator performs an operation on two operands. The following general format is used for binary operators:
operand 1 operator operand 2
Other operators in the special formats can accept more than two operands. If a NULL operand is provided for an operator, the result is always null. The only operator that does not comply with this rule is the concatenation (||) operator.
The precedences affect the order in which ApsaraDB for OceanBase calculates different operators in the same expression. When an expression that contains multiple operators is calculated, ApsaraDB for OceanBase first calculates the operator that has a higher precedence and then calculates the operator that has a lower precedence. If the precedences of two operators are equal, the operators that have the same precedence in the expression are calculated from left to right.
The following table lists the precedences of SQL operators from highest to lowest. Operators that are listed on the same line have the same precedence.
+, - (it is used as an unary operator), PRIOR, and CONNECT_BY_ROOT
Plus sign, unary minus, and the position in a hierarchy.
* and /
Multiplication and division.
+, - (it is used as a binary operator), and ||
Addition, subtraction, and concatenation.
After the database evaluates operators, it evaluates case expressions.
For more information, see SQL condition overview.
In the following example, multiplication takes precedence over addition. The database first multiplies 2 by 3 and then adds 1 to the result.
You can also use parentheses in an expression to limit operator precedences. ApsaraDB for OceanBase evaluates the expressions that are enclosed in parentheses, and then evaluates the expressions that are outside the parentheses.
SQL also supports the set operators
MINUS. Set operators merge rowsets that are returned by queries instead of individual data items. All the set operators have equal precedence.