All Products
Search

This Product

    MaxCompute:Operators

    Document Center

    MaxCompute:Operators

    Last Updated:Feb 28, 2026

    Operators are fundamental building blocks of SQL statements. They let you compare values, perform calculations, manipulate strings, work with binary data, and combine logical conditions. This topic describes the operators that MaxCompute supports, including their syntax, behavior, and usage examples.

    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

    Before you perform relational operations, you may need to convert data types. Otherwise, NULL may be returned. For more information, see Type conversions.

    Comparing DOUBLE values

    Do not use the equal sign (=) to compare two DOUBLE values directly, because DOUBLE values have limited precision. Instead, subtract one value from the other and compare the absolute difference against a small 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.

    • In most cases, a DOUBLE value in MaxCompute provides a precision of 14 decimal digits.

    Comparing STRING and BIGINT values

    When you compare a STRING value with a BIGINT value, both values are automatically converted to DOUBLE, which may cause precision loss. To avoid this, use CAST(string_value AS BIGINT) to explicitly convert the STRING value to BIGINT before comparing.

    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 operands of the STRING, BIGINT, DOUBLE, and TIMESTAMP_NTZ types can be used in arithmetic operations. Date values and BOOLEAN values cannot be used. For more information about the TIMESTAMP_NTZ data type, see MaxCompute TIMESTAMP_NTZ data types.

    • STRING operands are implicitly converted to DOUBLE before arithmetic operations.

    • When a BIGINT operand and a DOUBLE operand are used together, the BIGINT value is implicitly converted to DOUBLE, and the result is of the DOUBLE type.

    • If both A and B are of the BIGINT type, the A / B operation returns a DOUBLE result. For all other arithmetic operations on two BIGINT operands, the result is of the BIGINT type.

    Bitwise operators

    Bitwise operators perform operations on the binary representation of integer values. Both operands must be of the BIGINT type. Bitwise operators do not support implicit type conversions.

    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 can be used. Logical operators do not support implicit type conversions.

    The following truth tables show the 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 according to the following precedence rules. Operators with a lower precedence number are evaluated first. Operators at the same precedence level are evaluated from 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.