Which MaxCompute operators are supported by Dataphin-Dataphin(Dataphin)-阿里云帮助中心

Dataphin supports MaxCompute relational, arithmetic, bitwise, and logical operators for use in program code.

Relational operators

Operator	Description
A=B	Returns NULL if either A or B is NULL. Returns TRUE if A equals B; otherwise, returns FALSE.
A<>B	Returns NULL if either A or B is NULL. Returns TRUE if A does not equal B; otherwise, returns FALSE.
A<B	Returns NULL if either A or B is NULL. Returns TRUE if A is less than B; otherwise, returns FALSE.
A<=B	Returns NULL if either A or B is NULL. Returns TRUE if A is less than or equal to B; otherwise, returns FALSE.
A>B	Returns NULL if either A or B is NULL. Returns TRUE if A is greater than B; otherwise, returns FALSE.
A>=B	Returns NULL if either A or B is NULL. Returns TRUE if A is greater than or equal to B; otherwise, returns FALSE.
A IS NULL	Returns TRUE if A is NULL; otherwise, returns FALSE.
A IS NOT NULL	Returns TRUE if A is not NULL; otherwise, returns FALSE.
A LIKE B	Returns NULL if either A or B is NULL. Returns TRUE if string A matches pattern B; otherwise, returns FALSE. A percent sign (%) matches any number of characters. An underscore (_) matches exactly one character. To match a literal percent sign (%) or underscore (_), precede it with an escape character, represented as `‘%’` or `‘_’`. `'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	Returns NULL if either A or B is NULL. Returns TRUE if string A matches string constant or regular expression B; otherwise, returns FALSE. Raises an error if B is empty.
A IN B	Returns TRUE if B is a collection and A is an element of B; otherwise, returns FALSE. Returns NULL if A is NULL. If B consists solely of a NULL element (A IN (NULL)), NULL is returned. Set B must be a constant set with at least one element, and all elements must share the same data type. Note If Set B includes a NULL element and other elements, the data type of NULL is considered the same as that of the other elements in Set B.
BETWEEN AND	The expression is `A [NOT] BETWEEN B AND C`. Returns NULL if A, B, or C is NULL. Returns TRUE if A is greater than or equal to B and less than or equal to C; otherwise, returns FALSE.
IS [NOT] DISTINCT FROM	The expression is `A IS [NOT] DISTINCT FROM B`. For more information, see Realtime Compute for Apache Flink Blink Product Announcements (New Purchases Discontinued) and Realtime Compute for Apache Flink Blink Product Announcements (New Purchases Discontinued).

Arithmetic operators

Operator	Description
A+B	Returns NULL if either A or B is NULL; otherwise, returns the sum of A and B.
A-B	Returns NULL if either A or B is NULL; otherwise, returns the difference between A and B.
A*B	Returns NULL if either A or B is NULL; otherwise, returns the product of A and B.
A/B	Returns NULL if either A or B is NULL; otherwise, returns the quotient of A divided by B. Note The return value is of the DOUBLE type when both A and B are BIGINT.
A%B	Returns NULL if either A or B is NULL; otherwise, returns the remainder of A divided by B.
+A	Returns A.
-A	Returns NULL if A is NULL; otherwise, returns the negation of A.
A DIV B	Returns NULL if either A or B is NULL; otherwise, returns the integer division of A by B.

Example:

SELECT age+10, age-10, age%10, -age, age*age, age/10, age div 10 FROM user;

Note

Arithmetic operations support only STRING, BIGINT, DOUBLE, and TIMESTAMP_NTZ types. BOOLEAN and date types are not supported. For more information on the TIMESTAMP_NTZ data type, see TIMESTAMP_NTZ data type.
STRING values are implicitly converted to DOUBLE before arithmetic operations.
In arithmetic operations involving BIGINT and DOUBLE types, the BIGINT value is implicitly converted to DOUBLE, and the result is of type DOUBLE.
If both A and B are BIGINT, A/B returns a DOUBLE value. Other arithmetic operations return BIGINT.

Bitwise operators

Operator	Example	Description
BITAND &	`BITAND(A,B)` `A&B`	The bitwise AND result of A and B is returned. For example, the result of `1&2` is 0, the result of `BITAND(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.
BITOR \|	`BITOR(A,B)` `A\|B`	The bitwise OR result of A and B is returned. For example, the result of `1\|2` is 3, the result of `BITOR(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.
BITNOT ~	`BITNOT(A)` `~A`	The bitwise NOT result of A is returned. For example, the result of `~1` is -2, the result of `BITNOT(7)` is -8, and the bitwise NOT result of NULL is NULL. A must be of the BIGINT type.
BITXOR ^	`BITXOR(A,B)` `A^B`	The bitwise XOR result of A and B is returned. For example, the result of `1^2` is 3, the result of `BITXOR(4,5)` is 1, and the bitwise XOR result of NULL and any value is NULL. A and B must be of the BIGINT type.

Note

Bitwise operators do not support implicit type conversions. You can use only values of the BIGINT type in bitwise operations.

Logical operators

Operator	Description
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.

Note

Logical operators do not support implicit type conversions. You can use only the values of the BOOLEAN type in logical operations.