GoogleSQL for Bigtable supports operators.Operators are represented by special characters or keywords; they don't usefunction call syntax. An operator manipulates any number of data inputs, alsocalled operands, and returns a result.

Common conventions:

• Unless otherwise specified, all operators returnNULL when one of theoperands isNULL.
• All operators will throw an error if the computation result overflows.
• For all floating point operations,+/-inf andNaN may only be returnedif one of the operands is+/-inf orNaN. In other cases, an error isreturned.

Operator precedence

The following table lists all GoogleSQL operators from highest tolowest precedence, i.e., the order in which they will be evaluated within astatement.

For example, the logical expression:

x OR y AND z

is interpreted as:

( x OR ( y AND z ) )

Operators with the same precedence are left associative. This means that thoseoperators are grouped together starting from the left and moving right. Forexample, 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 operatorsaren't associative. Therefore, parentheses are required to resolveambiguity. For example:

(x < y) IS FALSE

Operator list

Field access operator

expression.fieldname[....]

Description

Gets the value of a field. Alternatively known as the dot operator. Can beused to access nested fields. For example,expression.fieldname1.fieldname2.

Input values:

• STRUCT

Return type

• ForSTRUCT: SQL data type offieldname. If a field isn't found inthe struct, an error is thrown.

Example

In the following example, the field access operations are.address and.country.

SELECTSTRUCT(STRUCT('Yonge Street'ASstreet,'Canada'AScountry)ASaddress).address.country/*---------+ | country | +---------+ | Canada  | +---------*/

Array subscript operator

array_expression"["array_subscript_specifier"]"array_subscript_specifier:{index|position_keyword(index)}position_keyword:{OFFSET|SAFE_OFFSET|ORDINAL|SAFE_ORDINAL}

SELECT["coffee","tea","milk"]ASitem_array,["coffee","tea","milk"][0]ASitem_index,["coffee","tea","milk"][OFFSET(0)]ASitem_offset,["coffee","tea","milk"][ORDINAL(1)]ASitem_ordinal,["coffee","tea","milk"][SAFE_OFFSET(6)]ASitem_safe_offset/*---------------------+------------+-------------+--------------+------------------+ | item_array          | item_index | item_offset | item_ordinal | item_safe_offset | +---------------------+------------+-------------+--------------+------------------+ | [coffee, tea, milk] | coffee     | coffee      | coffee       | NULL             | +----------------------------------+-------------+--------------+------------------*/

-- Error. Array index 6 is out of bounds.SELECT["coffee","tea","milk"][6]ASitem_offset

-- Error. Array index 6 is out of bounds.SELECT["coffee","tea","milk"][OFFSET(6)]ASitem_offset

SELECTMAP_KEYS(cell_plan)[0]ASresultsFROMtest_tableLIMIT1/*----------------+ | results        | +----------------+ | data_plan_01gb | +----------------*/

SELECTMAP_KEYS(cell_plan)[1]ASresultsFROMtest_tableLIMIT1/*----------------+ | results        | +----------------+ | data_plan_05gb | +----------------*/

SELECTMAP_KEYS(cell_plan)[OFFSET(1)]ASresultsFROMtest_tableLIMIT1/*----------------+ | results        | +----------------+ | data_plan_05gb | +----------------*/

SELECTMAP_KEYS(cell_plan)[ORDINAL(0)]ASresultsFROMtest_tableLIMIT1/*----------------+ | results        | +----------------+ | data_plan_01gb | +----------------*/

SELECTMAP_KEYS(cell_plan)[SAFE_OFFSET(5)]ASresultsFROMtest_tableLIMIT1/*----------------+ | results        | +----------------+ | NULL           | +----------------*/

date_expression+int64_expressionint64_expression+date_expressiondate_expression-int64_expression

SELECTDATE"2020-09-22"+1ASday_later,DATE"2020-09-22"-7ASweek_ago/*------------+------------+ | day_later  | week_ago   | +------------+------------+ | 2020-09-23 | 2020-09-15 | +------------+------------*/

GoogleSQL supports theAND,OR, andNOT logical operators.Logical operators allow onlyBOOL orNULL inputand usethree-valued logicto produce a result. The result can beTRUE,FALSE, orNULL:

The order of evaluation of operands toAND andOR can vary, and evaluationcan be skipped if unnecessary.

Examples

The examples in this section reference a table calledentry_table:

/*-------+ | entry | +-------+ | a     | | b     | | c     | | NULL  | +-------*/

SELECT'a'FROMentry_tableWHEREentry='a'-- a => 'a' = 'a' => TRUE-- b => 'b' = 'a' => FALSE-- NULL => NULL = 'a' => NULL/*-------+ | entry | +-------+ | a     | +-------*/

SELECTentryFROMentry_tableWHERENOT(entry='a')-- a => NOT('a' = 'a') => NOT(TRUE) => FALSE-- b => NOT('b' = 'a') => NOT(FALSE) => TRUE-- NULL => NOT(NULL = 'a') => NOT(NULL) => NULL/*-------+ | entry | +-------+ | b     | | c     | +-------*/

SELECTentryFROMentry_tableWHEREentryISNULL-- a => 'a' IS NULL => FALSE-- b => 'b' IS NULL => FALSE-- NULL => NULL IS NULL => TRUE/*-------+ | entry | +-------+ | NULL  | +-------*/

Comparison operators

Compares operands and produces the results of the comparison as aBOOLvalue. These comparison operators are available:

The following rules apply to operands in a comparison operator:

• The operands must becomparable.
• A comparison operator generally requires both operands to be of thesame type.
• If the operands are of different types, and the values of those types can beconverted to a common type without loss of precision,they are generally coerced to that common type for the comparison.
• A literal operand is generally coerced to the same data type of anon-literal operand that's part of the comparison.
• Struct operands support only these comparison operators: equal(=), not equal (!= and<>), andIN.

The following rules apply when comparing these data types:

• Floating point:All comparisons withNaN returnFALSE,except for!= and<>, which returnTRUE.
• BOOL:FALSE is less thanTRUE.
• STRING: Strings are compared codepoint-by-codepoint, which means thatcanonically equivalent strings are only guaranteed to compare as equal ifthey have been normalized first.
• NULL: Any operation with aNULL input returnsNULL.

• STRUCT: When testing a struct for equality, it's possible that one or morefields areNULL. In such cases:

  • If all non-NULL field values are equal, the comparison returnsNULL.
  • If any non-NULL field values aren't equal, the comparison returnsFALSE.

  The following table demonstrates howSTRUCT data types are compared whenthey have fields that areNULL valued.

  Struct1	Struct2	Struct1 = Struct2
  STRUCT(1, NULL)	STRUCT(1, NULL)	NULL
  STRUCT(1, NULL)	STRUCT(2, NULL)	FALSE
  STRUCT(1,2)	STRUCT(1, NULL)	NULL

IS operators

IS operators return TRUE or FALSE for the condition they are testing. They neverreturnNULL, even forNULL inputs, unlike theIS_INF andIS_NANfunctions defined inMathematical Functions.IfNOT is present, the outputBOOL value isinverted.

LIKE operator

expression_1[NOT]LIKEexpression_2

Description

LIKE returnsTRUE if the string in the first operandexpression_1matches a pattern specified by the second operandexpression_2,otherwise returnsFALSE.

NOT LIKE returnsTRUE if the string in the first operandexpression_1doesn't match a pattern specified by the second operandexpression_2,otherwise returnsFALSE.

Expressions can contain these characters:

• A percent sign (%) matches any number of characters or bytes.
• An underscore (_) matches a single character or byte.
• You can escape\,_, or% using two backslashes. For example,\\%. If you are using raw strings, only a single backslash isrequired. For example,r'\%'.

This operator supportscollation, but caveats apply:

• Each% character inexpression_2 represents anarbitrary string specifier. An arbitrary string specifier can representany sequence of0 or more characters.

• A character in the expression represents itself and is considered asingle character specifier unless:

  • The character is a percent sign (%).

  • The character is an underscore (_) and the collator isn'tund:ci.

• These additional rules apply to the underscore (_) character:

  • If the collator isn'tund:ci, an error is produced when an underscoreisn't escaped inexpression_2.

  • If the collator isn'tund:ci, the underscore isn't allowed when theoperands have collation specified.

  • Somecompatibility composites, such as the fi-ligature (fi) and thetelephone sign (℡), will produce a match if they are compared to anunderscore.

  • A single underscore matches the idea of what a character is, based onan approximation known as agrapheme cluster.

• For a contiguous sequence of single character specifiers, equalitydepends on the collator and its language tags and tailoring.

  • By default, theund:ci collator doesn't fully normalize a string.Some canonically equivalent strings are considered unequal forboth the= andLIKE operators.

  • TheLIKE operator with collation has the same behavior as the=operator when there are no wildcards in the strings.

  • Character sequences with secondary or higher-weighted differences areconsidered unequal. This includes accent differences and somespecial cases.

    For example there are three ways to produce German sharpß:

    • \u1E9E
    • \U00DF
    • ss

    \u1E9E and\U00DF are considered equal but differ in tertiary.They are considered equal withund:ci collation but different fromss, which has secondary differences.

  • Character sequences with tertiary or lower-weighted differences areconsidered equal. This includes case differences andkana subtype differences, which are considered equal.

• There areignorable characters defined in Unicode.Ignorable characters are ignored in the pattern matching.

Return type

BOOL

Examples

The following examples illustrate how you can check to see if the string in thefirst operand matches a pattern specified by the second operand.

-- Returns TRUESELECT'apple'LIKE'a%';

-- Returns FALSESELECT'%a'LIKE'apple';

-- Returns FALSESELECT'apple'NOTLIKE'a%';

-- Returns TRUESELECT'%a'NOTLIKE'apple';

-- Produces an errorSELECTNULLLIKE'a%';

-- Produces an errorSELECT'apple'LIKENULL;

The following example illustrates how to search multiple patterns in an arrayto find a match with theLIKE operator:

Concatenation operator

The concatenation operator combines multiple values into one.