Functions, operators, and conditionals

This topic is a compilation of functions, operators, and conditional expressions.

To learn more about how to call functions, function call rules, the SAFE prefix, and special types of arguments, see Function calls.


OPERATORS AND CONDITIONALS

Operators

GoogleSQL for BigQuery supports 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.
  • All operators will throw an error if the computation result overflows.
  • For all floating point operations, +/-inf and NaN may only be returned if one of the operands is +/-inf or NaN. In other cases, an error is returned.

Operator precedence

The following table lists all GoogleSQL operators from highest to lowest precedence, i.e., the order in which they will be evaluated within a statement.

Order of Precedence Operator Input Data Types Name Operator Arity
1 Field access operator STRUCT
JSON
Field access operator Binary
  Array subscript operator ARRAY Array position. Must be used with OFFSET or ORDINAL—see Array Functions . Binary
  JSON subscript operator JSON Field name or array position in JSON. Binary
2 + All numeric types Unary plus Unary
  - All numeric types Unary minus Unary
  ~ Integer or BYTES Bitwise not Unary
3 * All numeric types Multiplication Binary
  / All numeric types Division Binary
  || STRING, BYTES, or ARRAY<T> Concatenation operator Binary
4 + All numeric types, DATE with INT64 , INTERVAL Addition Binary
  - All numeric types, DATE with INT64 , INTERVAL Subtraction Binary
5 << Integer or BYTES Bitwise left-shift Binary
  >> Integer or BYTES Bitwise right-shift Binary
6 & Integer or BYTES Bitwise and Binary
7 ^ Integer or BYTES Bitwise xor Binary
8 | Integer or BYTES Bitwise or Binary
9 (Comparison Operators) = Any comparable type. See Data Types for a complete list. Equal Binary
  < Any comparable type. See Data Types for a complete list. Less than Binary
  > Any comparable type. See Data Types for a complete list. Greater than Binary
  <= Any comparable type. See Data Types for a complete list. Less than or equal to Binary
  >= Any comparable type. See Data Types for a complete list. Greater than or equal to Binary
  !=, <> Any comparable type. See Data Types for a complete list. Not equal Binary
  [NOT] LIKE STRING and BYTES Value does [not] match the pattern specified Binary
  Quantified LIKE STRING and BYTES Checks a search value for matches against several patterns. Binary
  [NOT] BETWEEN Any comparable types. See Data Types for a complete list. Value is [not] within the range specified Binary
  [NOT] IN Any comparable types. See Data Types for a complete list. Value is [not] in the set of values specified Binary
  IS [NOT] NULL All Value is [not] NULL Unary
  IS [NOT] TRUE BOOL Value is [not] TRUE. Unary
  IS [NOT] FALSE BOOL Value is [not] FALSE. Unary
10 NOT BOOL Logical NOT Unary
11 AND BOOL Logical AND Binary
12 OR BOOL Logical OR Binary

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, but comparison operators are not associative. Therefore, parentheses are required in order to resolve ambiguity. For example:

(x < y) IS FALSE

Field access operator

expression.fieldname[. ...]

Desc