This topic describes the operators that are supported in Tablestore SQL, including arithmetic operators, relational operators, logical operators, and bitwise operators.

Arithmetic operators

Arithmetic operators can be used in SELECT or WHERE clauses to compute values.

Operator Relation Description
A+B Addition Returns the result by adding A and B.
A-B Subtraction Returns the result by subtracting B from A.
A*B Multiplication Returns the result by multiplying A by B.
A/B or A DIV B Division Returns the result by dividing A by B.
A%B or A MOD B Remainder Returns the result by computing the remainder of dividing A by B.

Relational operators

Relational operators are used to determine the row data that meets the specified conditions in a table.
  • If the comparison result is true (TRUE), 1 is returned.
  • If the comparison result is false (FALSE), 0 is returned.

Relational operators can be used in WHERE clauses as specific conditions. If the condition is met, 1 is returned. If the condition is not met, 0 is returned.

Operator Relation Description
A:=B Assignment Assigns the value of B to A.
A=B Equal to Returns 1 if A is equal to B, and returns 0 in other cases.
A!=B or A<>B Not equal to Returns 1 if A is not equal to B, and returns 0 in other cases.
A>B Greater than Returns 1 if A is greater than B, and returns 0 in other cases.
A<B Less than Returns 1 if A is less than B, and returns 0 in other cases.
A>=B Greater than or equal to Returns 1 if A is greater than or equal to B, and returns 0 in other cases.
A<=B Less than or equal to Returns 1 if A is less than or equal to B, and returns 0 in other cases.
BETWEEN A AND B BETWEEN Returns 1 if the value is greater than or equal to A and less than or equal to B, and returns 0 in other cases.
Not BETWEEN A AND B NOT BETWEEN Returns 1 if the value is greater than B or less than A, and returns 0 in other cases.
A LIKE B LIKE Returns 1 if A matches B, and returns 0 in other cases. The LIKE operator performs the string matching operation. A is a string, and B is a matching pattern.

The underscore (_) wildcard in the pattern substitutes for exactly one character in a string. The percent sign (%) wildcard in the pattern substitutes for zero or more characters in a string.

A NOT LIKE B NOT LIKE Returns 1 if A does not match B, and returns 0 in other cases. The NOT LIKE operator performs the string mismatching operation. A is a string, and B is a matching pattern.

The underscore (_) wildcard in the pattern substitutes for exactly one character in a string. The percent sign (%) wildcard in the pattern substitutes for one or more characters in a string.

Logical operators

Logical operators are used to determine whether expressions are true or false.
  • If the expression is true (TRUE), 1 is returned.
  • If the expression is false (FALSE), 0 is returned.

Logical operators can be used in WHERE clauses to construct complex conditions. If the condition is met, 1 is returned. If the condition is not met, 0 is returned.

Operator Relation Description
A AND B or A&&B Logical AND Returns 1 if both A and B are TRUE, and returns 0 in other cases.
A OR B Logical OR Returns 1 if at least one of A and B is TRUE, and returns 0 in other cases.
A XOR B Logical XOR Returns 1 if A and B are not TRUE or FALSE at the same time, and returns 0 in other cases.
NOT A or ! A Logical NOT Returns 1 if A is FALSE, and returns 0 in other cases.

Bitwise operators

Bitwise operators are used to compute binary data. The bitwise operation converts the operand into a binary number for bitwise operation, and then converts the computing result from a binary number to a decimal number.

Operator Relation Description
A&B Bitwise AND Returns the result based on the bitwise AND operation of A and B.
A|B Bitwise OR Returns the result based on the bitwise OR operation of A and B.
A^B Bitwise XOR Returns the result based on the bitwise XOR operation of A and B.
~A Bitwise NOT Returns the result based on the bitwise inversion of A.