All Products
Search
Document Center

Operator overview

Last Updated: Jun 18, 2021

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 WHERE or HAVING conditions for queries or subqueries.

This topic describes the following operators in detail:

  • Arithmetic operators

  • Concatenation operators

  • Hierarchical query operators

  • Set 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:

operator operand
  • 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.

Operator precedences

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.

Operator

Operation method

+, - (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.

1+2*3

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 UNION, UNION ALL, INTERSECT, and MINUS. Set operators merge rowsets that are returned by queries instead of individual data items. All the set operators have equal precedence.