layout: section title: “Beam ZetaSQL operators” section_menu: section-menu/sdks.html permalink: /documentation/dsls/sql/zetasql/operators/

Beam ZetaSQL operators

Operators are represented by special characters or keywords; they do not use function call syntax. An operator manipulates any number of data inputs, also called operands, and returns a result.

Common conventions:

  • Unless otherwise specified, all operators return NULL when one of the operands is NULL.

The following table lists all supported operators from highest to lowest precedence. Precedence determines the order in which operators will be evaluated within a statement.

Operators with the same precedence are left associative. This means that those operators are grouped together starting from the left and moving right. For example, the expression:

x AND y AND z

is interpreted as

( ( x AND y ) AND z )

The expression:

x * y / z

is interpreted as:

( ( x * y ) / z )

All comparison operators have the same priority and are grouped using left associativity. However, comparison operators are not associative. As a result, it is recommended that you use parentheses to improve readability and ensure expressions are resolved as desired. For example:

(x < y) IS FALSE

is recommended over:

x < y IS FALSE

Element access operators

Arithmetic operators

All arithmetic operators accept input of numeric type T, and the result type has type T unless otherwise indicated in the description below:

Result types for Addition and Multiplication:

Result types for Subtraction:

Result types for Division:

Result types for Unary Minus:

Logical operators

All logical operators allow only BOOL input.

Comparison operators

Comparisons always return BOOL. Comparisons generally require both operands to be of the same type. If operands are of different types, and if Cloud Dataflow SQL can convert the values of those types to a common type without loss of precision, Cloud Dataflow SQL will generally coerce them to that common type for the comparison; Cloud Dataflow SQL will generally [coerce literals to the type of non-literals]({{ site.baseurl }}/documentation/dsls/sql/zetasql/conversion-rules/#coercion), where present. Comparable data types are defined in [Data Types]({{ site.baseurl }}/documentation/dsls/sql/zetasql/data-types).

STRUCTs support only 4 comparison operators: equal (=), not equal (!= and <>), and IN.

The following rules apply when comparing these data types:

  • FLOAT64 : All comparisons with NaN return FALSE, except for != and <>, which return TRUE.
  • BOOL: FALSE is less than TRUE.
  • STRING: Strings are compared codepoint-by-codepoint, which means that canonically equivalent strings are only guaranteed to compare as equal if they have been normalized first.
  • NULL: The convention holds here: any operation with a NULL input returns NULL.

When testing values that have a STRUCT data type for equality, it's possible that one or more fields are NULL. In such cases:

  • If all non-NULL field values are equal, the comparison returns NULL.
  • If any non-NULL field values are not equal, the comparison returns false.

The following table demonstrates how STRUCT data types are compared when they have fields that are NULL valued.

IS operators

IS operators return TRUE or FALSE for the condition they are testing. They never return NULL, even for NULL inputs. If NOT is present, the output BOOL value is inverted.