All Products
Search
Document Center

Arithmetic operators

Last Updated: Jun 18, 2021

Arithmetic operators perform operations, such as negation, addition, subtraction, multiplication, and division, on one or two parameters. Some of arithmetic operators are also used to calculate date and time and interval values. The parameters of arithmetic operators must be resolved to the numeric data type or a data type that can be directly converted into the numeric data type by the database.

Unary arithmetic operators return the same data type as the data type of the parameters. For binary arithmetic operators, ApsaraDB for OceanBase determines the parameter that has the highest numeric precedence in the expression, and then converts the remaining parameters to the data type of this parameter.

The following table describes arithmetic operators.

Operator

Description

+, -

If the operators represent positive and negative, they are unary operators.

+, -

If the operators represent addition and subtraction operations, they are binary operators.

*, /

The binary operators. They represent multiplication and division operations.

Two consecutive minus signs (--) cannot be used in arithmetic expressions to indicate double negation or the subtraction of a negative value. This is because the characters -- are used to specify comments in SQL statements. You can separate two consecutive minus signs with spaces or parentheses. For more information about the comments in SQL statements, see Comments.

The following example shows an SQL query that uses the + and - operators to represent positive and negative:

SELECT * FROM order_items WHERE quantity = -1 ORDER BY order_id, line_item_id, product_id; 
SELECT * FROM employees WHERE -salary < 0 ORDER BY employee_id;

The following example shows an SQL query that uses the + and - operators to represent addition and subtraction operations:

SELECT hire_date FROM employees WHERE SYSDATE - hire_date > 365 ORDER BY hire_date;

The following example shows an SQL query that uses the * and / operators to represent multiplication and division operations:

UPDATE employees SET salary = salary * 1.1;
UPDATE employees SET salary = salary / 2;