This topic describes the operators that are supported in Tablestore SQL, including arithmetic, relational, logical, and bitwise operators.
Arithmetic operators
Arithmetic operators can be used in SELECT or WHERE clauses to compute values. The following table describes arithmetic operators.
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 after 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. The following table describes relational operators.
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. |
IN (A,B...) | Equal to one of the values in a list | Returns 1 if the value is equal to one of the values in the list, 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 character in the pattern substitutes for exactly one character in a string. The percent sign (%) wildcard character 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 character in the pattern substitutes for exactly one character in a string. The percent sign (%) wildcard character in the pattern substitutes for zero 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. The following table describes logical operators.
Operator | Relation | Description |
A AND B or A&&B | Logical AND | Returns 1 if 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, and then converts the computing result from a binary number to a decimal number. The following table describes bitwise operators.
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. |