Operators are used to perform program code operations. This topic describes the operators that are supported by MaxCompute.
The following table describes the operator types that are supported by MaxCompute.
|Relational operators||Used for comparison operations.|
|Arithmetic operators||Used for arithmetic operations.|
|Bitwise operators||Used for operations on binary digits.|
|Logical operators||Used to connect multiple conditions. In most cases, logical operators are used to connect expressions or values of the BOOLEAN type.|
|A IS NULL||If A is NULL, TRUE is returned. Otherwise, FALSE is returned.|
|A IS NOT NULL||If A is not NULL, TRUE is returned. Otherwise, FALSE is returned.|
|A LIKE B||If A or B is NULL, NULL is returned. If String A matches Pattern B, TRUE is returned.
Otherwise, FALSE is returned.
|A RLIKE B||If String A matches String Constant or Regular Expression B, TRUE is returned. Otherwise, FALSE is returned. If B is an empty string, an error is returned. If A or B is NULL, NULL is returned.|
|A IN B||
|BETWEEN AND||The expression is
|IS [NOT] DISTINCT FROM||The expression is
SELECT * FROM user WHERE user_id = '0001'; SELECT * FROM user WHERE user_name <> 'maggie'; SELECT * FROM user WHERE age > '50'; SELECT * FROM user WHERE birth_day >= '1980-01-01 00:00:00'; SELECT * FROM user WHERE is_female is null; SELECT * FROM user WHERE is_female is not null; SELECT * FROM user WHERE user_id in (0001,0010); SELECT * FROM user WHERE user_name like 'M%';
'2019-02-16 00:00:01'is of the DATETIME type and
'2019-02-16'is of the STRING type. You must explicitly convert the date values into the same data type before you perform relational operations for comparison.
SELECT CAST('2019-02-16 00:00:01' AS STRING) > '2019-02-16'; SELECT CAST('2019-02-16 00:00:02' AS DATETIME) > '2019-02-16 00:00:01';
ABS(0.9999999999 - 1.0000000000) < 0.000000001 -- 0.9999999999 and 1.0000000000 have a precision of 10 decimal digits, whereas 0.000000001 has a precision of 9 decimal digits. -- 0.9999999999 is considered equal to 1.0000000000.
- ABS is a built-in function provided by MaxCompute. This function is used to obtain the absolute value of its input. For more information, see ABS.
- In most cases, a value of the DOUBLE type in MaxCompute can provide a precision of 14 decimal digits.
- If you compare a value of the STRING type with a value of the BIGINT type, the data types of the values are automatically converted into DOUBLE. The precision loss may occur during the comparison. To address this issue, you can use CAST STRING AS BIGINT to convert the STRING type into BIGINT.
|A+B||If A or B is NULL, NULL is returned. Otherwise, the result of A + B is returned.|
|A-B||If A or B is NULL, NULL is returned. Otherwise, the result of A - B is returned.|
|A*B||If A or B is NULL, NULL is returned. Otherwise, the result of A × B is returned.|
|A/B||If A or B is NULL, NULL is returned. Otherwise, the result of A/B is returned.
Note If A and B are of the BIGINT type, the return value is of the DOUBLE type.
|A%B||If A or B is NULL, NULL is returned. Otherwise, the remainder of A/B is returned.|
|+A||A is returned.|
|-A||If A is NULL, NULL is returned. Otherwise, -A is returned.|
SELECT age+10, age-10, age%10, -age, age*age, age/10 FROM user;
- You can use only the values of the STRING, BIGINT, or DOUBLE type to perform arithmetic operations. You cannot use the values of the DATATIME or BOOLEAN type to perform arithmetic operations.
- Values of the STRING type are implicitly converted into the DOUBLE type before arithmetic operations.
- If you use values of the BIGINT and DOUBLE types to perform arithmetic operations, the value of the BIGINT type is implicitly converted into the DOUBLE type before the operations. The return value is of the DOUBLE type.
- If A and B are of the BIGINT type, the return value is of the DOUBLE type after you perform the A/B operation. For other arithmetic operations, the return value is of the BIGINT type.
|A&B||The bitwise AND result of A and B is returned. For example, the result of 1&2 is 0, the result of 1&3 is 1, and the bitwise AND result of NULL and any value is NULL. A and B must be of the BIGINT type.|
|A|B||The bitwise OR result of A and B is returned. For example, the result of 1|2 is 3, the result of 1|3 is 3, and the bitwise OR result of NULL and any value is NULL. A and B must be of the BIGINT type.|
|A||B||This operator is used to join strings. For example,
|A and B||TRUE and TRUE=TRUE|
|TRUE and FALSE=FALSE|
|FALSE and TRUE=FALSE|
|FALSE and FALSE=FALSE|
|FALSE and NULL=FALSE|
|NULL and FALSE=FALSE|
|TRUE and NULL=NULL|
|NULL and TRUE=NULL|
|NULL and NULL=NULL|
|A or B||TRUE or TRUE=TRUE|
|TRUE or FALSE=TRUE|
|FALSE or TRUE=TRUE|
|FALSE or FALSE=FALSE|
|FALSE or NULL=NULL|
|NULL or FALSE=NULL|
|TRUE or NULL=TRUE|
|NULL or TRUE=TRUE|
|NULL or NULL=NULL|
|NOT A||If A is NULL, NULL is returned.|
|If A is TRUE, FALSE is returned.|
|If A is FALSE, TRUE is returned.|
All operators have the same precedence, and the operations are performed from left to right by default. If you want to preferentially process some operations, you must enclose the operations in parentheses (). Example:
a=1 and b=1 or c=1 -- a and b are calculated before c. a=1 and (b=1 or c=1) -- b and c are calculated before a.