MaxCompute SQL operators - relational, arithmetic, bitwise, logical - MaxCompute

Overview

MaxCompute supports the following types of operators:

Operator type	Description
Relational operators	Compare values and return a BOOLEAN result.
String operators	Concatenate strings.
Arithmetic operators	Perform mathematical calculations.
Bitwise operators	Perform operations on binary representations of integers.
Logical operators	Combine multiple conditions. Typically used with BOOLEAN expressions.

Relational operators

Relational operators compare two values and return TRUE, FALSE, or NULL. Unless otherwise noted, if either operand is NULL, the result is NULL.

Operator	Description
`A = B`	Returns TRUE if A equals B. Returns NULL if A or B is NULL.
`A<=>B`	NULL-safe equality. Returns TRUE if both A and B are NULL. Returns FALSE if only one is NULL. Otherwise, returns TRUE if A equals B, or FALSE if not.
`A<>B`	Returns TRUE if A is not equal to B. Returns NULL if A or B is NULL.
`A<B`	Returns TRUE if A is less than B. Returns NULL if A or B is NULL.
`A<=B`	Returns TRUE if A is less than or equal to B. Returns NULL if A or B is NULL.
`A>B`	Returns TRUE if A is greater than B. Returns NULL if A or B is NULL.
`A>=B`	Returns TRUE if A is greater than or equal to B. Returns NULL if A or B is NULL.
`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`	Pattern matching. Returns TRUE if string A matches pattern B. Returns NULL if A or B is NULL. The `%` wildcard matches zero or more characters. The `_` wildcard matches exactly one character. To match a literal `%` or `_`, escape it with single quotation marks (`'%'` or `'_'`).
`A RLIKE B`	Regular expression matching. Returns TRUE if string A matches the regular expression B. Returns NULL if A or B is NULL. Returns an error if B is an empty string. `A RLIKE B` is equivalent to `A REGEXP B`.
`A IN B`	Returns TRUE if A is in set B. Returns NULL if A is NULL. Set B must be a constant set that contains at least one element, and all elements must be of the same data type.
`A [NOT] BETWEEN B AND C`	Returns TRUE if A is greater than or equal to B and less than or equal to C (or the inverse with NOT). Returns NULL if A, B, or C is NULL.
`A IS [NOT] DISTINCT FROM B`	Compares two values, treating NULLs as equal.

LIKE pattern examples

'aaa' LIKE 'a__'    -- TRUE (underscore matches one character)
'aaa' LIKE 'a%'     -- TRUE (percent matches zero or more characters)
'aaa' LIKE 'aab'    -- FALSE
'a%b' LIKE 'a\\%b'  -- TRUE (escaped percent matches literal %)
'axb' LIKE 'a\\%b'  -- FALSE

Usage examples

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%';

Important notes

Note

Mismatched types may return NULL in comparisons. Convert operands to compatible types before comparing. Type conversions.

Comparing DOUBLE values

Do not compare DOUBLE values with = directly due to limited precision. Instead, check whether the absolute difference is smaller than a threshold:

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.

ABS is a built-in function that returns the absolute value of its input.
A DOUBLE value in MaxCompute typically provides 14 decimal digits of precision.

Comparing STRING and BIGINT values

Comparing STRING with BIGINT implicitly converts both to DOUBLE, which may lose precision. Use CAST(string_value AS BIGINT) to convert the STRING to BIGINT first.

String operators

Operator	Description
`A\|\|B`	Concatenates strings A and B. For example, `a\|\|b\|\|c` is equivalent to `CONCAT(a, b, c)`.

Example

SELECT first_name || ' ' || last_name AS full_name FROM employees;

Arithmetic operators

Arithmetic operators perform mathematical calculations on numeric values.

Operator	Description
`A+B`	Returns the sum of A and B. Returns NULL if A or B is NULL.
`A-B`	Returns the difference of A minus B. Returns NULL if A or B is NULL.
`A*B`	Returns the product of A multiplied by B. Returns NULL if A or B is NULL.
`A/B`	Returns the quotient of A divided by B. Returns NULL if A or B is NULL. If both A and B are of the BIGINT type, the result is of the DOUBLE type.
`A%B`	Returns the remainder of A divided by B. Returns NULL if A or B is NULL.
`+A`	Returns A (unary plus).
`-A`	Returns the negation of A. Returns NULL if A is NULL.
`A DIV B`	Performs integer division of A by B, discarding the fractional part. Returns NULL if A or B is NULL.

Example

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

Data type rules

Only STRING, BIGINT, DOUBLE, and TIMESTAMP_NTZ operands support arithmetic operations. Date and BOOLEAN values are not supported. MaxCompute TIMESTAMP_NTZ data types.
STRING operands are implicitly converted to DOUBLE before calculation.
When BIGINT and DOUBLE operands are mixed, the BIGINT value is implicitly converted to DOUBLE and the result is DOUBLE.
If both A and B are BIGINT, A / B returns DOUBLE. All other arithmetic operations on two BIGINT operands return BIGINT.

Bitwise operators

Bitwise operators work on the binary representation of integers. Both operands must be BIGINT. No implicit type conversions apply.

Operator	Syntax	Description
`&` / `BITAND`	`A & B` or `BITAND(A, B)`	Returns the bitwise AND of A and B. For example, `1 & 2` returns `0`, and `BITAND(1, 3)` returns `1`. Returns NULL if A or B is NULL.
`\|` / `BITOR`	`A\|B` or `BITOR(A, B)`	Returns the bitwise OR of A and B. For example, `1\|2` returns `3`, and `BITOR(1, 3)` returns `3`. Returns NULL if A or B is NULL.
`~` / `BITNOT`	`~A` or `BITNOT(A)`	Returns the bitwise NOT of A. For example, `~1` returns `-2`, and `BITNOT(7)` returns `-8`. Returns NULL if A is NULL.
`^` / `BITXOR`	`A ^ B` or `BITXOR(A, B)`	Returns the bitwise XOR of A and B. For example, `1 ^ 2` returns `3`, and `BITXOR(4, 5)` returns `1`. Returns NULL if A or B is NULL.

Logical operators

Logical operators combine BOOLEAN expressions. Only BOOLEAN operands are supported; no implicit type conversions apply.

The following truth tables show results for all combinations of TRUE, FALSE, and NULL:

AND

A	B	A AND B
TRUE	TRUE	TRUE
TRUE	FALSE	FALSE
FALSE	TRUE	FALSE
FALSE	FALSE	FALSE
FALSE	NULL	FALSE
NULL	FALSE	FALSE
TRUE	NULL	NULL
NULL	TRUE	NULL
NULL	NULL	NULL

OR

A	B	A OR B
TRUE	TRUE	TRUE
TRUE	FALSE	TRUE
FALSE	TRUE	TRUE
FALSE	FALSE	FALSE
FALSE	NULL	NULL
NULL	FALSE	NULL
TRUE	NULL	TRUE
NULL	TRUE	TRUE
NULL	NULL	NULL

NOT

A	NOT A
TRUE	FALSE
FALSE	TRUE
NULL	NULL

Operator precedence

When an expression contains multiple operators, MaxCompute evaluates them in the following order. Lower numbers have higher precedence. Operators at the same level are evaluated left to right.

Precedence	Operators
1 (highest)	`IS (NOT) NULL`
2	`^`
3	`*`, `/`, `%`, `DIV`
4	`+`, `-`
5	\|\|
6	&
7	\|
8	`(NOT) LIKE`, `(NOT) RLIKE`, `=`, `==`, `IS (NOT) DISTINCT FROM`, `<>`, `!=`, `<=`, `<`, `>=`, `>`
9	`(NOT) IN`, `(NOT) BETWEEN AND`
10	`NOT`
11	`AND`
12 (lowest)	`OR`

Use parentheses to override the default precedence. For example:

a = 1 AND b = 1 OR c = 1       -- Evaluates AND first, then OR.
a = 1 AND (b = 1 OR c = 1)     -- Evaluates OR first (inside parentheses), then AND.