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:
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
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:
All logical operators allow only BOOL input.
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:
!=
and <>
, which return TRUE.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:
The following table demonstrates how STRUCT data types are compared when they have fields that are NULL
valued.
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.