Operators in GoogleSQL

GoogleSQL for Spanner 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.

When Spanner runs an operator, the operator is treated as a function.Because of this, if an operator produces an error, the error message might usethe termfunction when referencing an operator.

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.

Order of Precedence	Operator	Input Data Types	Name	Operator Arity
1	Field access operator	`STRUCT` `PROTO` `JSON`	Field access operator	Binary
	Array subscript operator	`ARRAY`	Array position. Must be used with`OFFSET` or`ORDINAL`—seeArray 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 ,`INTERVAL`	Addition	Binary
	`-`	All numeric types ,`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. SeeData Types for a complete list.	Equal	Binary
	`<`	Any comparable type. SeeData Types for a complete list.	Less than	Binary
	`>`	Any comparable type. SeeData Types for a complete list.	Greater than	Binary
	`<=`	Any comparable type. SeeData Types for a complete list.	Less than or equal to	Binary
	`>=`	Any comparable type. SeeData Types for a complete list.	Greater than or equal to	Binary
	`!=`,`<>`	Any comparable type. SeeData Types for a complete list.	Not equal	Binary
	`[NOT] LIKE`	`STRING` and`BYTES`	Value does [not] match the pattern specified	Binary
	`[NOT] BETWEEN`	Any comparable types. SeeData Types for a complete list.	Value is [not] within the range specified	Binary
	`[NOT] IN`	Any comparable types. SeeData 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

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

Name	Summary
Field access operator	Gets the value of a field.
Array subscript operator	Gets a value from an array at a specific position.
JSON subscript operator	Gets a value of an array element or field in a JSON expression.
Arithmetic operators	Performs arithmetic operations.
Datetime subtraction	Computes the difference between two datetimes as an interval.
Interval arithmetic operators	Adds an interval to a datetime or subtracts an interval from a datetime.
Bitwise operators	Performs bit manipulation.
Logical operators	Tests for the truth of some condition and produces`TRUE`,`FALSE`, or`NULL`.
Graph concatenation operator	Combines multiple graph paths into one and preserves the original order of the nodes and edges.
Graph logical operators	Tests for the truth of a condition in a graph and produces either`TRUE` or`FALSE`.
Graph predicates	Tests for the truth of a condition for a graph element and produces`TRUE`,`FALSE`, or`NULL`.
`ALL_DIFFERENT` predicate	In a graph, checks to see if the elements in a list are mutually distinct.
`IS DESTINATION` predicate	In a graph, checks to see if a node is or isn't the destination of an edge.
`IS LABELED` predicate	In a graph, checks to see if a node or edge label satisfies a label expression.
`IS SOURCE` predicate	In a graph, checks to see if a node is or isn't the source of an edge.
`PROPERTY_EXISTS` predicate	In a graph, checks to see if a property exists for an element.
`SAME` predicate	In a graph, checks if all graph elements in a list bind to the same node or edge.
Comparison operators	Compares operands and produces the results of the comparison as a`BOOL` value.
`EXISTS` operator	Checks if a subquery produces one or more rows.
`IN` operator	Checks for an equal value in a set of values.
`IS` operators	Checks for the truth of a condition and produces either`TRUE` or`FALSE`.
`LIKE` operator	Checks if values are like or not like one another.
`NEW` operator	Creates a protocol buffer.
Concatenation operator	Combines multiple values into one.
`WITH` expression	Creates variables for re-use and produces a result expression.

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
PROTO
JSON
GRAPH_ELEMENT

Return type

ForSTRUCT: SQL data type offieldname. If a field isn't found inthe struct, an error is thrown.
ForPROTO: SQL data type offieldname. If a field isn't found inthe protocol buffer, an error is thrown.
ForJSON:JSON. If a field isn't found in a JSON value, a SQLNULL isreturned.
ForGRAPH_ELEMENT:
- Without dynamic properties: SQL data type offieldname. Ifa field (property) isn't found in the graph element, an error is returned.
- With dynamic properties: SQL data type offieldname if the field(property) is defined;JSON type if the field (property) isstored as a dynamic property and found in the graph element during queryexecution; SQLNULL is returned if the field (property) is not found in thegraph element.Seegraph element type for more details about graphelements with dynamic properties.

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

Note: Syntax characters enclosed in double quotes ("") are literal andrequired.

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

Description

Gets a value from an array at a specific position.

Input values:

array_expression: The input array.
position_keyword(index): Determines where the index for the array shouldstart and how out-of-range indexes are handled. The index is an integer thatrepresents a specific position in the array.
- OFFSET(index): The index starts at zero. Produces an error if the index isout of range. To produceNULL instead of an error, useSAFE_OFFSET(index).
- SAFE_OFFSET(index): The index starts atzero. ReturnsNULL if the index is out of range.
- ORDINAL(index): The index starts at one.Produces an error if the index is out of range.To produceNULL instead of an error, useSAFE_ORDINAL(index).
- SAFE_ORDINAL(index): The index starts atone. ReturnsNULL if the index is out of range.

Return type

T wherearray_expression isARRAY<T>.

Examples

In following query, the array subscript operator is used to return values atspecific position initem_array. This query also shows what happens when youreference an index (6) in an array that's out of range. If theSAFE prefixis included,NULL is returned, otherwise an error is produced.

SELECT["coffee","tea","milk"]ASitem_array,["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_offset | item_ordinal | item_safe_offset | +---------------------+-------------+--------------+------------------+ | [coffee, tea, milk] | coffee      | coffee       | NULL             | +---------------------+-------------+--------------+------------------*/

When you reference an index that's out of range in an array, and a positionalkeyword that begins withSAFE isn't included, an error is produced.For example:

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

JSON subscript operator

Note: Syntax characters enclosed in double quotes ("") are literal andrequired.

json_expression"["array_element_id"]"

json_expression"["field_name"]"

Description

Gets a value of an array element or field in a JSON expression. Can beused to access nested data.

Input values:

JSON expression: TheJSON expression that contains an array element orfield to return.
[array_element_id]: AnINT64 expression that represents a zero-based indexin the array. If a negative value is entered, or the value is greater thanor equal to the size of the array, or the JSON expression doesn't representa JSON array, a SQLNULL is returned.
[field_name]: ASTRING expression that represents the name of a field inJSON. If the field name isn't found, or the JSON expression isn't aJSON object, a SQLNULL is returned.

Return type

JSON

Example

In the following example:

json_value is a JSON expression.
.class is a JSON field access.
.students is a JSON field access.
[0] is a JSON subscript expression with an element offset thataccesses the zeroth element of an array in the JSON value.
['name'] is a JSON subscript expression with a field name thataccesses a field.

SELECTjson_value.class.students[0]['name']ASfirst_studentFROMUNNEST([JSON'{"class" : {"students" : [{"name" : "Jane"}]}}',JSON'{"class" : {"students" : []}}',JSON'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'])ASjson_value;/*-----------------+ | first_student   | +-----------------+ | "Jane"          | | NULL            | | "John"          | +-----------------*/

Arithmetic operators

All arithmetic operators accept input of numeric typeT, and the result typehas typeT unless otherwise indicated in the description below:

Name	Syntax
Addition	`X + Y`
Subtraction	`X - Y`
Multiplication	`X * Y`
Division	`X / Y`
Unary Plus	`+ X`
Unary Minus	`- X`

NOTE: Divide by zero operations return an error. To return a different result,consider theIEEE_DIVIDE orSAFE_DIVIDE functions.

Result types for Addition, Subtraction and Multiplication:

INPUT	`INT64`	`NUMERIC`	`FLOAT32`	`FLOAT64`
`INT64`	`INT64`	`NUMERIC`	`FLOAT64`	`FLOAT64`
`NUMERIC`	`NUMERIC`	`NUMERIC`	`FLOAT64`	`FLOAT64`
`FLOAT32`	`FLOAT64`	`FLOAT64`	`FLOAT64`	`FLOAT64`
`FLOAT64`	`FLOAT64`	`FLOAT64`	`FLOAT64`	`FLOAT64`

Result types for Division:

INPUT	`INT64`	`NUMERIC`	`FLOAT32`	`FLOAT64`
`INT64`	`FLOAT64`	`NUMERIC`	`FLOAT64`	`FLOAT64`
`NUMERIC`	`NUMERIC`	`NUMERIC`	`FLOAT64`	`FLOAT64`
`FLOAT32`	`FLOAT64`	`FLOAT64`	`FLOAT64`	`FLOAT64`
`FLOAT64`	`FLOAT64`	`FLOAT64`	`FLOAT64`	`FLOAT64`

Result types for Unary Plus:

INPUT	`INT64`	`NUMERIC`	`FLOAT32`	`FLOAT64`
OUTPUT	`INT64`	`NUMERIC`	`FLOAT32`	`FLOAT64`

Result types for Unary Minus:

INPUT	`INT64`	`NUMERIC`	`FLOAT32`	`FLOAT64`
OUTPUT	`INT64`	`NUMERIC`	`FLOAT32`	`FLOAT64`

Datetime subtraction

date_expression-date_expressiontimestamp_expression-timestamp_expression

Description

Computes the difference between two datetime values as an interval.

Return Data Type

INTERVAL

Example

SELECTDATE"2021-05-20"-DATE"2020-04-19"ASdate_diff,TIMESTAMP"2021-06-01 12:34:56.789"-TIMESTAMP"2021-05-31 00:00:00"AStime_diff/*-------------------+------------------------+ | date_diff         | time_diff              | +-------------------+------------------------+ | 0-0 396 0:0:0     | 0-0 0 36:34:56.789     | +-------------------+------------------------*/

Interval arithmetic operators

Addition and subtraction

timestamp_expression+interval_expression=TIMESTAMPtimestamp_expression-interval_expression=TIMESTAMP

Description

Adds an interval to a datetime value or subtracts an interval from a datetimevalue.

Example

SELECTTIMESTAMP"2021-05-02 00:01:02.345+00"+INTERVAL25HOURAStime_plus,TIMESTAMP"2021-05-02 00:01:02.345+00"-INTERVAL10SECOND AStime_minus;/*------------------------------+--------------------------------+ | time_plus                    | time_minus                     | +------------------------------+--------------------------------+ | 2021-05-03 08:01:02.345+00   | 2021-05-02 00:00:52.345+00     | +------------------------------+--------------------------------*/

Multiplication and division

interval_expression*integer_expression=INTERVALinterval_expression/integer_expression=INTERVAL

Description

Multiplies or divides an interval value by an integer.

Example

SELECTINTERVAL'1:2:3'HOURTOSECOND*10ASmul1,INTERVAL35SECOND*4ASmul2,INTERVAL10YEAR/3ASdiv1,INTERVAL1MONTH/12ASdiv2/*----------------+--------------+-------------+--------------+ | mul1           | mul2         | div1        | div2         | +----------------+--------------+-------------+--------------+ | 0-0 0 10:20:30 | 0-0 0 0:2:20 | 3-4 0 0:0:0 | 0-0 2 12:0:0 | +----------------+--------------+-------------+--------------*/

Bitwise operators

All bitwise operators return the same type and the same length asthe first operand.

Name	Syntax	Input Data Type	Description
Bitwise not	`~ X`	Integer or`BYTES`	Performs logical negation on each bit, forming the ones' complement of thegiven binary value.
Bitwise or	`X \| Y`	`X`: Integer or`BYTES` `Y`: Same type as`X`	Takes two bit patterns of equal length and performs the logical inclusive`OR` operation on each pair of the corresponding bits.This operator throws an error if`X` and`Y` are bytes ofdifferent lengths.
Bitwise xor	`X ^ Y`	`X`: Integer or`BYTES` `Y`: Same type as`X`	Takes two bit patterns of equal length and performs thelogical exclusive`OR` operation on each pair of the correspondingbits.This operator throws an error if`X` and`Y` are bytes ofdifferent lengths.
Bitwise and	`X & Y`	`X`: Integer or`BYTES` `Y`: Same type as`X`	Takes two bit patterns of equal length and performs thelogical`AND` operation on each pair of the corresponding bits.This operator throws an error if`X` and`Y` are bytes ofdifferent lengths.
Left shift	`X << Y`	`X`: Integer or`BYTES` `Y`:`INT64`	Shifts the first operand`X` to the left.This operator returns`0` or a byte sequence of`b'\x00'`if the second operand`Y` is greater than or equal tothe bit length of the first operand`X` (for example,`64`if`X` has the type`INT64`).This operator throws an error if`Y` is negative.
Right shift	`X >> Y`	`X`: Integer or`BYTES` `Y`:`INT64`	Shifts the first operand`X` to the right. This operator doesn'tperform sign bit extension with a signed type (i.e., it fills vacant bits on the leftwith`0`). This operator returns`0` or a byte sequence of`b'\x00'`if the second operand`Y` is greater than or equal tothe bit length of the first operand`X` (for example,`64`if`X` has the type`INT64`).This operator throws an error if`Y` is negative.

Logical operators

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

`x`	`y`	`x AND y`	`x OR y`
`TRUE`	`TRUE`	`TRUE`	`TRUE`
`TRUE`	`FALSE`	`FALSE`	`TRUE`
`TRUE`	`NULL`	`NULL`	`TRUE`
`FALSE`	`TRUE`	`FALSE`	`TRUE`
`FALSE`	`FALSE`	`FALSE`	`FALSE`
`FALSE`	`NULL`	`FALSE`	`NULL`
`NULL`	`TRUE`	`NULL`	`TRUE`
`NULL`	`FALSE`	`FALSE`	`NULL`
`NULL`	`NULL`	`NULL`	`NULL`

`x`	`NOT x`
`TRUE`	`FALSE`
`FALSE`	`TRUE`
`NULL`	`NULL`

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  | +-------*/

Graph concatenation operator

graph_path||graph_path[||...]

Description

Combines multiple graph paths into one and preserves the original order of thenodes and edges.

Arguments:

graph_path: AGRAPH_PATH value that represents a graph path toconcatenate.

Details

This operator produces an error if the last node in the first path isn't thesame as the first node in the second path.

-- This successfully produces the concatenated path called `full_path`.MATCHp=(src:Account)-[t1:Transfers]->(mid:Account),q=(mid)-[t2:Transfers]->(dst:Account)LETfull_path=p||q

-- This produces an error because the first node of the path to be concatenated-- (mid2) isn't equal to the last node of the previous path (mid1).MATCHp=(src:Account)-[t1:Transfers]->(mid1:Account),q=(mid2:Account)-[t2:Transfers]->(dst:Account)LETfull_path=p||q

The first node in each subsequent path is removed from theconcatenated path.

-- The concatenated path called `full_path` contains these elements:-- src, t1, mid, t2, dst.MATCHp=(src:Account)-[t1:Transfers]->(mid:Account),q=(mid)-[t2:Transfers]->(dst:Account)LETfull_path=p||q

If anygraph_path isNULL, producesNULL.

Example

In the following query, a path calledp andq are concatenated. Notice thatmid is used at the end of the first path and at the beginning of thesecond path. Also notice that the duplicatemid is removed from theconcatenated path calledfull_path:

GRAPHFinGraphMATCHp=(src:Account)-[t1:Transfers]->(mid:Account),q=(mid)-[t2:Transfers]->(dst:Account)LETfull_path=p||qRETURNJSON_QUERY(TO_JSON(full_path)[0],'$.labels')ASelement_a,JSON_QUERY(TO_JSON(full_path)[1],'$.labels')ASelement_b,JSON_QUERY(TO_JSON(full_path)[2],'$.labels')ASelement_c,JSON_QUERY(TO_JSON(full_path)[3],'$.labels')ASelement_d,JSON_QUERY(TO_JSON(full_path)[4],'$.labels')ASelement_e,JSON_QUERY(TO_JSON(full_path)[5],'$.labels')ASelement_f/*-------------------------------------------------------------------------------------+ | element_a   | element_b     | element_c   | element_d     | element_e   | element_f | +-------------------------------------------------------------------------------------+ | ["Account"] | ["Transfers"] | ["Account"] | ["Transfers"] | ["Account"] |           | | ...         | ...           | ...         | ...           | ...         | ...       | +-------------------------------------------------------------------------------------/*

The following query produces an error because the last node forp mustbe the first node forq:

-- Error: `mid1` and `mid2` aren't equal.GRAPHFinGraphMATCHp=(src:Account)-[t1:Transfers]->(mid1:Account),q=(mid2:Account)-[t2:Transfers]->(dst:Account)LETfull_path=p||qRETURNTO_JSON(full_path)ASresults

The following query produces an error because the path calledp isNULL:

-- Error: a graph path is NULL.GRAPHFinGraphMATCHp=NULL,q=(mid:Account)-[t2:Transfers]->(dst:Account)LETfull_path=p||qRETURNTO_JSON(full_path)ASresults

Graph logical operators

GoogleSQL supports the following logical operators inelement pattern label expressions:

Name	Syntax	Description
`NOT`	`!X`	Returns`TRUE` if`X` isn't included, otherwise, returns`FALSE`.
`OR`	`X \| Y`	Returns`TRUE` if either`X` or`Y` is included, otherwise, returns`FALSE`.
`AND`	`X & Y`	Returns`TRUE` if both`X` and`Y` are included, otherwise, returns`FALSE`.

Graph predicates

GoogleSQL supports the following graph-specific predicates ingraph expressions. A predicate can produceTRUE,FALSE, orNULL.

`ALL_DIFFERENT` predicate

ALL_DIFFERENT(element,element[,...])

Description

In a graph, checks to see if the elements in a list are mutually distinct.ReturnsTRUE if the elements are distinct, otherwiseFALSE.

Definitions

element: The graph pattern variable for a node or edge element.

Details

Produces an error ifelement isNULL.

Return type

BOOL

Examples

GRAPHFinGraphMATCH(a1:Account)-[t1:Transfers]->(a2:Account)-[t2:Transfers]->(a3:Account)-[t3:Transfers]->(a4:Account)WHEREa1.id <a4.idRETURNALL_DIFFERENT(t1,t2,t3)ASresults/*---------+ | results | +---------+ | FALSE   | | TRUE    | | TRUE    | +---------*/

`IS DESTINATION` predicate

nodeIS[NOT]DESTINATION[OF]edge

Description

In a graph, checks to see if a node is or isn't the destination of an edge.Can produceTRUE,FALSE, orNULL.

Arguments:

node: The graph pattern variable for the node element.
edge: The graph pattern variable for the edge element.

Examples

GRAPHFinGraphMATCH(a:Account)-[transfer:Transfers]-(b:Account)WHEREaISDESTINATIONoftransferRETURNa.idASa_id,b.idASb_id/*-------------+ | a_id | b_id | +-------------+ | 16   | 7    | | 16   | 7    | | 20   | 16   | | 7    | 20   | | 16   | 20   | +-------------*/

GRAPHFinGraphMATCH(a:Account)-[transfer:Transfers]-(b:Account)WHEREbISDESTINATIONoftransferRETURNa.idASa_id,b.idASb_id/*-------------+ | a_id | b_id | +-------------+ | 7    | 16   | | 7    | 16   | | 16   | 20   | | 20   | 7    | | 20   | 16   | +-------------*/

`IS LABELED` predicate

elementIS[NOT]LABELEDlabel_expression

Description

In a graph, checks to see if a node or edge label satisfies a labelexpression. Can produceTRUE,FALSE, orNULL ifelement isNULL.

Arguments:

element: The graph pattern variable for a graph node or edge element.
label_expression: The label expression to verify. For more information, seeLabel expression definition.

Examples

GRAPHFinGraphMATCH(a)WHEREaISLABELEDAccount|PersonRETURNa.idASa_id,LABELS(a)ASlabels/*----------------+ | a_id | labels  | +----------------+ | 1    | Person  | | 2    | Person  | | 3    | Person  | | 7    | Account | | 16   | Account | | 20   | Account | +----------------*/

GRAPHFinGraphMATCH(a)-[e]-(b:Account)WHEREeISLABELEDTransfers|OwnsRETURNa.Idasa_id,Labels(e)ASlabels,b.Idasb_idORDERBYa_id,b_id/*------+-----------------------+------+ | a_id | labels                | b_id | +------+-----------------------+------+ |    1 | [owns]                |    7 | |    2 | [owns]                |   20 | |    3 | [owns]                |   16 | |    7 | [transfers]           |   16 | |    7 | [transfers]           |   16 | |    7 | [transfers]           |   20 | |   16 | [transfers]           |    7 | |   16 | [transfers]           |    7 | |   16 | [transfers]           |   20 | |   16 | [transfers]           |   20 | |   20 | [transfers]           |    7 | |   20 | [transfers]           |   16 | |   20 | [transfers]           |   16 | +------+-----------------------+------*/

GRAPHFinGraphMATCH(a:Account{Id:7})OPTIONALMATCH(a)-[:OWNS]->(b)RETURNa.IdASa_id,b.IdASb_id,bISLABELEDAccountASb_is_account/*------+-----------------------+ | a_id | b_id   | b_is_account | +------+-----------------------+ | 7    | NULL   | NULL         | +------+-----------------------+*/

`IS SOURCE` predicate

nodeIS[NOT]SOURCE[OF]edge

Description

In a graph, checks to see if a node is or isn't the source of an edge.Can produceTRUE,FALSE, orNULL.

Arguments:

node: The graph pattern variable for the node element.
edge: The graph pattern variable for the edge element.

Examples

GRAPHFinGraphMATCH(a:Account)-[transfer:Transfers]-(b:Account)WHEREaISSOURCEoftransferRETURNa.idASa_id,b.idASb_id/*-------------+ | a_id | b_id | +-------------+ | 20   | 7    | | 7    | 16   | | 7    | 16   | | 20   | 16   | | 16   | 20   | +-------------*/

GRAPHFinGraphMATCH(a:Account)-[transfer:Transfers]-(b:Account)WHEREbISSOURCEoftransferRETURNa.idASa_id,b.idASb_id/*-------------+ | a_id | b_id | +-------------+ | 7    | 20   | | 16   | 7    | | 16   | 7    | | 16   | 20   | | 20   | 16   | +-------------*/

`PROPERTY_EXISTS` predicate

PROPERTY_EXISTS(element,element_property)

Description

In a graph, checks to see if a property exists for an element.Can produceTRUE,FALSE, orNULL.

Arguments:

element: The graph pattern variable for a node or edge element.
element_property: The name of the property to look for inelement.The property name must refer to a property in the graph. If the propertydoesn't exist in the graph, an error is produced. The property name isresolved in a case-insensitive manner.

Example

GRAPHFinGraphMATCH(n:Person|AccountWHEREPROPERTY_EXISTS(n,name))RETURNn.name/*------+ | name | +------+ | Alex | | Dana | | Lee  | +------*/

`SAME` predicate

SAME(element,element[,...])

Description

In a graph, checks if all graph elements in a list bind to the same node oredge. ReturnsTRUE if the elements bind to the same node or edge, otherwiseFALSE.

Arguments:

element: The graph pattern variable for a node or edge element.

Details

Produces an error ifelement isNULL.

Example

The following query checks to see ifa andb aren't the same person.

GRAPHFinGraphMATCH(src:Account)<-[transfer:Transfers]-(dest:Account)WHERENOTSAME(src,dest)RETURNsrc.idASsource_id,dest.idASdestination_id/*----------------------------+ | source_id | destination_id | +----------------------------+ | 7         | 20             | | 16        | 7              | | 16        | 7              | | 16        | 20             | | 20        | 16             | +----------------------------*/

Comparison operators

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

Name	Syntax	Description
Less Than	`X < Y`	Returns`TRUE` if`X` is less than`Y`.
Less Than or Equal To	`X <= Y`	Returns`TRUE` if`X` is less than or equal to`Y`.
Greater Than	`X > Y`	Returns`TRUE` if`X` is greater than`Y`.
Greater Than or Equal To	`X >= Y`	Returns`TRUE` if`X` is greater than or equal to`Y`.
Equal	`X = Y`	Returns`TRUE` if`X` is equal to`Y`.
Not Equal	`X != Y` `X <> Y`	Returns`TRUE` if`X` isn't equal to`Y`.
`BETWEEN`	`X [NOT] BETWEEN Y AND Z`	Returns`TRUE` if`X` is [not] within the range specified. The result of`X BETWEEN Y AND Z` is equivalent to`Y <= X AND X <= Z` but`X` is evaluated only once in the former.
`LIKE`	`X [NOT] LIKE Y`	See the`LIKE` operator for details.
`IN`	Multiple	See the`IN` operator for details.

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.
JSON: You can't compare JSON, but you can comparethe values inside of JSON if you convert the values toSQL values first. For more information, seeJSON functions.
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

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`

`EXISTS` operator

EXISTS(subquery)

Description

ReturnsTRUE if the subquery produces one or more rows. ReturnsFALSE ifthe subquery produces zero rows. Never returnsNULL. To learn more abouthow you can use a subquery withEXISTS,seeEXISTS subqueries.

Examples

In this example, theEXISTS operator returnsFALSE because there are norows inWords where the direction issouth:

WITHWordsAS(SELECT'Intend'asvalue,'east'asdirectionUNIONALLSELECT'Secure','north'UNIONALLSELECT'Clarity','west')SELECTEXISTS(SELECTvalueFROMWordsWHEREdirection='south')asresult;/*--------+ | result | +--------+ | FALSE  | +--------*/

`IN` operator

TheIN operator supports the following syntax:

search_value[NOT]INvalue_setvalue_set:{(expression[,...])|(subquery)|UNNEST(array_expression)}

Description

Checks for an equal value in a set of values.Semantic rules apply, but in general,IN returnsTRUEif an equal value is found,FALSE if an equal value is excluded, otherwiseNULL.NOT IN returnsFALSE if an equal value is found,TRUE if anequal value is excluded, otherwiseNULL.

search_value: The expression that's compared to a set of values.
value_set: One or more values to compare to a search value.
- (expression[, ...]): A list of expressions.
- (subquery): Asubquery that returnsa single column. The values in that column are the set of values.If no rows are produced, the set of values is empty.
- UNNEST(array_expression): AnUNNEST operatorthat returns a column of values from an array expression. This isequivalent to:
```
IN(SELECTelementFROMUNNEST(array_expression)ASelement)
```

This operator generally supportscollation,however,[NOT] IN UNNEST doesn't support collation.

Semantic rules

When using theIN operator, the following semantics apply in this order:

ReturnsFALSE ifvalue_set is empty.
ReturnsNULL ifsearch_value isNULL.
ReturnsTRUE ifvalue_set contains a value equal tosearch_value.
ReturnsNULL ifvalue_set contains aNULL.
ReturnsFALSE.

When using theNOT IN operator, the following semantics apply in this order:

ReturnsTRUE ifvalue_set is empty.
ReturnsNULL ifsearch_value isNULL.
ReturnsFALSE ifvalue_set contains a value equal tosearch_value.
ReturnsNULL ifvalue_set contains aNULL.
ReturnsTRUE.

The semantics of:

xIN(y,z,...)

are defined as equivalent to:

(x=y)OR(x=z)OR...

and the subquery and array forms are defined similarly.

xNOTIN...

is equivalent to:

NOT(xIN...)

TheUNNEST form treats an array scan likeUNNEST in theFROM clause:

x[NOT]INUNNEST(<arrayexpression>)

This form is often used with array parameters. For example:

xINUNNEST(@array_parameter)

See theArrays topic for more informationon how to use this syntax.

IN can be used with multi-part keys by using the struct constructor syntax.For example:

(Key1,Key2)IN((12,34),(56,78))(Key1,Key2)IN(SELECT(table.a,table.b)FROMtable)

See theStruct Type topic for more information.

Return Data Type

BOOL

Examples

You can use theseWITH clauses to emulate temporary tables forWords andItems in the following examples:

WITHWordsAS(SELECT'Intend'asvalueUNIONALLSELECT'Secure'UNIONALLSELECT'Clarity'UNIONALLSELECT'Peace'UNIONALLSELECT'Intend')SELECT*FROMWords;/*----------+ | value    | +----------+ | Intend   | | Secure   | | Clarity  | | Peace    | | Intend   | +----------*/

WITHItemsAS(SELECTSTRUCT('blue'AScolor,'round'ASshape)ASinfoUNIONALLSELECTSTRUCT('blue','square')UNIONALLSELECTSTRUCT('red','round'))SELECT*FROMItems;/*----------------------------+ | info                       | +----------------------------+ | {blue color, round shape}  | | {blue color, square shape} | | {red color, round shape}   | +----------------------------*/

Example withIN and an expression:

SELECT*FROMWordsWHEREvalueIN('Intend','Secure');/*----------+ | value    | +----------+ | Intend   | | Secure   | | Intend   | +----------*/

Example withNOT IN and an expression:

SELECT*FROMWordsWHEREvalueNOTIN('Intend');/*----------+ | value    | +----------+ | Secure   | | Clarity  | | Peace    | +----------*/

Example withIN, a scalar subquery, and an expression:

SELECT*FROMWordsWHEREvalueIN((SELECT'Intend'),'Clarity');/*----------+ | value    | +----------+ | Intend   | | Clarity  | | Intend   | +----------*/

Example withIN and anUNNEST operation:

SELECT*FROMWordsWHEREvalueINUNNEST(['Secure','Clarity']);/*----------+ | value    | +----------+ | Secure   | | Clarity  | +----------*/

Example withIN and a struct:

SELECT(SELECTASSTRUCTItems.info)asitemFROMItemsWHERE(info.shape,info.color)IN(('round','blue'));/*------------------------------------+ | item                               | +------------------------------------+ | { {blue color, round shape} info } | +------------------------------------*/

`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.

Function Syntax	Input Data Type	Result Data Type	Description
`X IS TRUE`	`BOOL`	`BOOL`	Evaluates to`TRUE` if`X` evaluates to`TRUE`. Otherwise, evaluates to`FALSE`.
`X IS NOT TRUE`	`BOOL`	`BOOL`	Evaluates to`FALSE` if`X` evaluates to`TRUE`. Otherwise, evaluates to`TRUE`.
`X IS FALSE`	`BOOL`	`BOOL`	Evaluates to`TRUE` if`X` evaluates to`FALSE`. Otherwise, evaluates to`FALSE`.
`X IS NOT FALSE`	`BOOL`	`BOOL`	Evaluates to`FALSE` if`X` evaluates to`FALSE`. Otherwise, evaluates to`TRUE`.
`X IS NULL`	Any value type	`BOOL`	Evaluates to`TRUE` if`X` evaluates to`NULL`. Otherwise evaluates to`FALSE`.
`X IS NOT NULL`	Any value type	`BOOL`	Evaluates to`FALSE` if`X` evaluates to`NULL`. Otherwise evaluates to`TRUE`.
`X IS UNKNOWN`	`BOOL`	`BOOL`	Evaluates to`TRUE` if`X` evaluates to`NULL`. Otherwise evaluates to`FALSE`.
`X IS NOT UNKNOWN`	`BOOL`	`BOOL`	Evaluates to`FALSE` if`X` evaluates to`NULL`. Otherwise, evaluates to`TRUE`.

`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'\%'.

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:

WITHWordsAS(SELECT'Intend with clarity.'asvalueUNIONALLSELECT'Secure with intention.'UNIONALLSELECT'Clarity and security.')SELECTvalueFROMWordsWHEREARRAY_INCLUDES(['%ity%','%and%'],pattern->(Words.valueLIKEpattern));/*------------------------+ | value                  | +------------------------+ | Intend with clarity.   | | Clarity and security.  | +------------------------*/

`NEW` operator

TheNEW operator only supports protocol buffers and uses the following syntax:

NEW protocol_buffer {...}: Creates aprotocol buffer using a map constructor.

NEWprotocol_buffer{field_name:literal_or_expressionfield_name{...}repeated_field_name:[literal_or_expression,...]}

NEW protocol_buffer (...): Creates a protocol buffer using a parenthesizedlist of arguments.
```
NEWprotocol_buffer(field[ASalias],...field[ASalias])
```

Examples

The following example uses theNEW operator with a map constructor:

NEWUniverse{name:"Sol"closest_planets:["Mercury","Venus","Earth"]star{radius_miles:432,690age:4,603,000,000}constellations:[{name:"Libra"index:0},{name:"Scorpio"index:1}]all_planets:(SELECTplanetsFROMSolTable)}

The following example uses theNEW operator with a parenthesized list ofarguments:

SELECTkey,name,NEWgooglesql.examples.music.Chart(keyASrank,nameASchart_name)FROM(SELECT1ASkey,"2"ASname);

To learn more about protocol buffers in GoogleSQL, seeWork withprotocol buffers.

Concatenation operator

The concatenation operator combines multiple values into one.

Function Syntax	Input Data Type	Result Data Type
`STRING \|\| STRING [ \|\| ... ]`	`STRING`	`STRING`
`BYTES \|\| BYTES [ \|\| ... ]`	`BYTES`	`BYTES`
`ARRAY<T> \|\| ARRAY<T> [ \|\| ... ]`	`ARRAY<T>`	`ARRAY<T>`

Note: The concatenation operator is translated into a nested CONCAT function call. For example,'A' || 'B' || 'C' becomesCONCAT('A', CONCAT('B', 'C')).

`WITH` expression

WITH(variable_assignment[,...],result_expression)variable_assignment:variable_nameASexpression

Description

Creates one or more variables. Each variable can be used in subsequentexpressions within theWITH expression. Returns the value ofresult_expression.

variable_assignment: Introduces a variable. The variable name must beunique within a givenWITH expression. Each expression can reference thevariables that come before it. For example, if you create variablea,then follow it with variableb, then you can referencea inside of theexpression forb.
- variable_name: The name of the variable.
- expression: The value to assign to the variable.
result_expression: An expression that can use all of the variables definedbefore it. The value ofresult_expression is returned by theWITHexpression.

Return Type

The type of theresult_expression.

Requirements and Caveats

A variable can only be assigned once within aWITH expression.
Variables created duringWITH may not be usedin aggregatefunction arguments. For example,WITH(a AS ..., SUM(a)) produces an error.
Each variable's expression is evaluated only once.

Examples

The following example first concatenates variablea withb, then variableb withc:

SELECTWITH(aAS'123',-- a is '123'bASCONCAT(a,'456'),-- b is '123456'cAS'789',-- c is '789'CONCAT(b,c))ASresult;-- b + c is '123456789'/*-------------+ | result      | +-------------+ | '123456789' | +-------------*/

Aggregate functionresults can be stored in variables.

SELECTWITH(sASSUM(input),cASCOUNT(input),s/c)FROMUNNEST([1.0,2.0,3.0])ASinput;/*---------+ | result  | +---------+ | 2.0     | +---------*/

Variables can't be used in aggregate function call arguments.

SELECTWITH(diffASa-b,AVG(diff))FROMUNNEST([STRUCT(1ASa,2ASb),STRUCT(3ASa,4ASb),STRUCT(5ASa,6ASb)]);-- ERROR: WITH variables like 'diff' can't be used in aggregate or analytic-- function arguments.

AWITH expression is different from aWITH clause. The following exampleshows a query that uses both:

WITHmy_tableAS(SELECT1ASx,2ASyUNIONALLSELECT3ASx,4ASyUNIONALLSELECT5ASx,6ASy)SELECTWITH(aASSUM(x),bASCOUNT(x),a/b)ASavg_x,AVG(y)ASavg_yFROMmy_tableWHEREx >1;/*-------+-------+ | avg_x | avg_y | +-------+-------+ | 4     | 5     | +-------+-------*/

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-17 UTC.

Movatterモバイル変換

Operators in GoogleSQL Stay organized with collections Save and categorize content based on your preferences.

Operator precedence

Operator list

Field access operator

Array subscript operator

JSON subscript operator

Arithmetic operators

Datetime subtraction

Interval arithmetic operators

Bitwise operators

Logical operators

Graph concatenation operator

Graph logical operators

Graph predicates

ALL_DIFFERENT predicate

IS DESTINATION predicate

IS LABELED predicate

IS SOURCE predicate

PROPERTY_EXISTS predicate

SAME predicate

Comparison operators

EXISTS operator

IN operator

IS operators

LIKE operator

NEW operator

Concatenation operator

WITH expression

Operators in GoogleSQL

`ALL_DIFFERENT` predicate

`IS DESTINATION` predicate

`IS LABELED` predicate

`IS SOURCE` predicate

`PROPERTY_EXISTS` predicate

`SAME` predicate

`EXISTS` operator

`IN` operator

`IS` operators

`LIKE` operator

`NEW` operator

`WITH` expression