All Products
Search
Document Center

MaxCompute:Operators

Last Updated:Jun 17, 2024

Operators are the key to SQL statements. Operators allow you to perform complex data retrieval and processing tasks. This helps you achieve more efficient data processing, conditional judgment, or other operations. This topic describes the formats and meanings of the operators supported by MaxCompute. This topic also provides examples on how to use these operators.

The following table describes the types of operators that are supported by MaxCompute.

Operator type

Description

Relational operators

Used for comparison operations.

Character operators

Used for string concatenation 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.

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 both A and B are NULL, TRUE is returned.

  • If A or B is NULL, FALSE 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 not equal 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. If String A matches Pattern B, TRUE is returned. Otherwise, FALSE is returned.

  • A percent sign (%) matches an arbitrary number of characters.

  • An underscore (_) matches a single character.

  • To match percent signs (%) or underscores (_), you must escape percent signs (%) or underscores (_) with single quotation marks ('). After the percent signs (%) or underscores (_) are escaped, '%' or '_' is used for the match.

'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

If String A matches String Constant B 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

  • If A is included in Set B, TRUE is returned. Otherwise, FALSE is returned.

  • If A is NULL, NULL is returned.

  • Set B must be a constant set and contain at least one element. All elements in Set B must be of the same data type.

BETWEEN AND

The expression is A [NOT] BETWEEN B AND C.

  • If A, B, or C is NULL, NULL is returned.

  • 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 is A IS [NOT] DISTINCT FROM B.

Common usage in statements:

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 you perform some relational operations, you must convert data types. Otherwise, NULL may be returned. For more information about data type conversions, see Type conversions.

Values of the DOUBLE type in MaxCompute are different in precision. Therefore, we recommend that you do not use an equal sign (=) for comparison between two values of the DOUBLE type. You can subtract a value of the DOUBLE type from another value of the DOUBLE type, and obtain the absolute value for comparison. If the absolute value is negligible, the two values of the DOUBLE type are considered equal. Example:

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 to be equal to 1.0000000000.
Note
  • 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.

Character operators

Operator

Description

A||B

Used for character concatenation operations. For example, a||b||c is equivalent to CONCAT(a, b, c).

Arithmetic operators

Operator

Description

A+B

If A or B is NULL, NULL is returned. Otherwise, the result of A plus B is returned.

A-B

If A or B is NULL, NULL is returned. Otherwise, the result of A minus B is returned.

A*B

If A or B is NULL, NULL is returned. Otherwise, the result of A multiplied by B is returned.

A/B

If A or B is NULL, NULL is returned. Otherwise, the result of A divided by 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 divided by B is returned.

+A

A is returned.

-A

If A is NULL, NULL is returned. Otherwise, -A is returned.

A DIV B

If A or B is NULL, NULL is returned. Otherwise, the result of A DIV B is returned.

Common usage in statements:

SELECT age+10, age-10, age%10, -age, age*age, age/10, age div 10 FROM user;
Note
  • Only parameter values of the STRING, BIGINT, DOUBLE, and TIMESTAMP_NTZ types can be used in arithmetic operations. Date values and values of the BOOLEAN type cannot be used in arithmetic operations. For more information about the TIMESTAMP_NTZ data type, see MaxCompute TIMESTAMP_NTZ data types.

  • 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.

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 the NULL value 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.

Operator priorities

The following table lists the precedence of operators in descending order. If operators have the same precedence, operations are performed from left to right by default.

Operator

Priority

IS (NOT) NULL

1

^

2

*, /, %, DIV

3

+, -

4

||

5

&

6

|

7

(NOT) LIKE, (NOT) RLIKE, =, ==, IS (NOT) DISTINCT FROM, <>, !=, <=, <, >=, >

8

(NOT) IN, (NOT) BETWEEN AND

9

NOT

10

AND

11

OR

12

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.