Operators are used in code to perform computations. This topic describes four types of operators: relational operators, arithmetic operators, bitwise operators, and logical operators.

Relational operators

Operator Description
A=B If A or B is NULL, NULL is returned. If A is equal to B, TRUE is returned. Otherwise, FALSE is returned.
A<>B If A or B is NULL, NULL is returned. If A is unequal to B, TRUE is returned. Otherwise, FALSE is returned.
A<B If A or B is NULL, NULL is returned. If A is less than B, TRUE is returned. Otherwise, FALSE is returned.
A<=B If A or B is NULL, NULL is returned. If A is less than or equal to B, TRUE is returned. Otherwise, FALSE is returned.
A>B If A or B is NULL, NULL is returned. If A is greater than B, TRUE is returned. Otherwise, FALSE is returned.
A>=B If A or B is NULL, NULL is returned. If A is greater than or equal to B, TRUE is returned. Otherwise, FALSE is returned.
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. A is a string and B is the pattern to be matched. If A matches B, TRUE is returned. Otherwise, FALSE is returned. The percent sign (%) and underscore (_) act as wildcard characters. A percent sign (%) matches zero or more characters, and an underscore (_) matches any single character. To match an actual percent sign (%) or underscore (_), use single quotation marks (') to escape the corresponding character, such as '%' and '_'.
'aaa' like 'a__'= TRUE 
'aaa' like 'a%'= TRUE
'aaa' like 'aab'= FALSE 
'a%b' like 'a\\%b'= TRUE 
'axb' like 'a\\%b'= FALSE               
A RLIKE B A is a string, and B is a string constant or regular expression. If A matches B, TRUE is returned. Otherwise, FALSE is returned. If B is empty, an error is reported, and the computation is stopped. If A or B is NULL, NULL is returned.
A IN B
  • B is a set. If A is in B, TRUE is returned. Otherwise, FALSE is returned.
  • If A is NULL, NULL is returned.
  • If B contains only one element NULL, that is, A IN (NULL), NULL is returned. If B contains the NULL element and other elements, the data type of NULL is considered to be the same as that of other elements in B.
  • B must be a constant set that contains at least one item. The data types of all items must be the same.
BETWEEN AND The expression is A [NOT] BETWEEN B AND C. If A, B, or C is empty, the returned result is empty. If A is greater than or equal to B and less than or equal to C, TRUE is returned. Otherwise, FALSE is returned.
IS [NOT] DISTINCT FROM The expression A IS DISTINCT FROM B is equivalent to the expression NOT(A<=>B), and the expression A IS NOT DISTINCT FROM B is equivalent to the expression A<=>B.
The following are some statements where relational operators are used:
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%';
Before performing some relational computations, you need to convert the data types. Otherwise, NULL may be returned. For more information, see Type conversions. For example, 2019-02-16 00:00:01 is of the datetime type, whereas 2019-02-16 is of the string type. Before performing a comparison, you must convert the data type.
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';
Values of the double type have precision differences. Therefore, we recommend that you do not use the equal sign (=) to compare two values of the double type. You can deduct a value of the double type from the other value of the double type and use the absolute value of the difference to determine whether the two values are equal. If the absolute value is negligible, the two values of the double type are considered equal. For example:
abs(0.9999999999 - 1.0000000000) < 0.000000001
 -- 0.9999999999 and 1.0000000000 have 10 decimal digits, whereas 0.000000001 has 9 decimal digits.
 -- 0.9999999999 is considered equal to 1.0000000000.
Note
  • ABS is a built-in function provided by MaxCompute to take the absolute value of its input. For more information, see ABS.
  • Generally, a value of the double type in MaxCompute can retain 14 valid digits.
  • If you compare a value of the string type with that of the bigint type, the values are automatically converted to those of the double type. This may cause loss of precision during comparison. In this case, you can use the cast (string as bigint) function to convert the value of the string type to that of the bigint type before comparison.

Arithmetic operators

Operator Description
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 result of A % B is returned.
+A A is returned.
-A If A is NULL, NULL is returned. Otherwise, -A is returned.
The following is a statement where arithmetic operators are used:
select age+10, age-10, age%10, -age, age*age, age/10 from user;
Note
  • You can only use values of the string, bigint, or double type to perform arithmetic computations. You cannot use values of the datatime or Boolean type to perform arithmetic computations.
  • Values of the string type are implicitly converted to the double type before computations.
  • If you use values of the bigint and double types to perform an arithmetic computation, the value of the bigint type is implicitly converted to that of the double type before the computation. The return value is of the double type.
  • If both A and B are of the bigint type, the return value is of the double type after you perform the A / B computation. For other arithmetic computations, the return values are still of the bigint type.

Bitwise operators

Operator Description
A & B The bitwise AND result of A and B is returned. For example, the result of 1 & 2 is 0, and the result of 1 & 3 is 1. The bitwise AND result of NULL and any value is NULL. Both 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, and the result of 1 | 3 is 3. The bitwise OR result of NULL and any value is NULL. Both A and B must be of the bigint type.
A || B This operator is used to join strings. For example, the expression a || b || c is equivalent to the expression concat(a, b, c).
Note Bitwise computations do not support implicit type conversion. You can only use values of the bigint type in bitwise computations.

Logical operators

Operator Description
A and B TRUE and TRUE=TRUE
TRUE and FALSE=FALSE
FALSE and TRUE=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 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.
Note Logical computations do not support implicit type conversion. You can only use values of the Boolean type in logical computations.