A GoogleSQL statement comprises a series of tokens. Tokens includeidentifiers, quoted identifiers, literals, keywords, operators, andspecial characters. You can separate tokens with comments or whitespace suchas spaces, backspaces, tabs, or newlines.

Identifiers

Identifiers are names that are associated with columns, tables,fields, path expressions, and more. They can beunquoted orquoted and somearecase-sensitive.

Unquoted identifiers

• Must begin with a letter or an underscore (_) character.
• Subsequent characters can be letters, numbers, or underscores (_).

Quoted identifiers

• Must be enclosed by backtick (`) characters.
• Can contain any characters, including spaces and symbols.
• Can't be empty.
• Have the same escape sequences asstring literals.
• If an identifier is the same as areserved keyword, theidentifier must be quoted. For example, the identifierFROM must be quoted.Additional rules apply forpath expressions,table names,column names, andfield names.

Identifier examples

Path expression examples:

-- Valid. _5abc and dataField are valid identifiers._5abc.dataField-- Valid. `5abc` and dataField are valid identifiers.`5abc`.dataField-- Invalid. 5abc is an invalid identifier because it's unquoted and starts-- with a number rather than a letter or underscore.5abc.dataField-- Valid. abc5 and dataField are valid identifiers.abc5.dataField-- Invalid. abc5! is an invalid identifier because it's unquoted and contains-- a character that isn't a letter, number, or underscore.abc5!.dataField-- Valid. `GROUP` and dataField are valid identifiers.`GROUP`.dataField-- Invalid. GROUP is an invalid identifier because it's unquoted and is a-- stand-alone reserved keyword.GROUP.dataField-- Valid. abc5 and GROUP are valid identifiers.abc5.GROUP

Function examples:

-- Valid. dataField is a valid identifier in a function called foo().foo().dataField

Array access operation examples:

-- Valid. dataField is a valid identifier in an array called items.items[OFFSET(3)].dataField

Named query parameter examples:

-- Valid. param and dataField are valid identifiers.@param.dataField

Table name examples:

-- Valid table path.myproject.mydatabase.mytable287

-- Valid table path.myproject287.mydatabase.mytable

-- Invalid table path. The project name starts with a number and is unquoted.287myproject.mydatabase.mytable

-- Invalid table name. The table name is unquoted and isn't a valid-- dashed identifier, as the part after the dash is neither a number nor-- an identifier starting with a letter or an underscore.mytable-287a

-- Valid table path.my-project.mydataset.mytable

-- Valid table name.my-table

-- Invalid table path because the dash isn't in the first part-- of the path.myproject.mydataset.my-table

-- Invalid table path because a dataset name can't contain dashes.my-dataset.mytable

Path expressions

A path expression describes how to navigate to an object in a graph of objectsand generally follows this structure:

path:  [path_expression][. ...]path_expression:  [first_part]/subsequent_part[ { / | : | - } subsequent_part ][...]first_part:  { unquoted_identifier | quoted_identifier }subsequent_part:  { unquoted_identifier | quoted_identifier | number }

• path: A graph of one or more objects.
• path_expression: An object in a graph of objects.
• first_part: A path expression can start with a quoted orunquoted identifier. If the path expressions starts with areserved keyword, it must be a quoted identifier.
• subsequent_part: Subsequent parts of a path expression can includenon-identifiers, such as reserved keywords. If a subsequent part of apath expressions starts with areserved keyword, itmay be quoted or unquoted.

Examples:

foo.barfoo.bar/25foo/bar:25foo/bar/25-31/foo/bar/25/foo/bar

Table names

A table name represents the name of a table.

• Table names can be quoted identifiers or unquoted identifiers.
• A table name that's an unquoted identifier can additionally includesingle dashes if the table name is referenced in aFROM orTABLE clause.Only the first identifier in the table path (the project ID or the table name)can have dashes. Dashes aren't supported in datasets.

• A table name can be afully qualified table name (table path)that includes up to three quoted or unquoted identifiers:

  • An optionalproject ID

  • An optionaldataset name

  • A required table name.

  For example:myproject.mydataset.mytable

• Table names can be path expressions.

• Table names havecase-sensitivity rules.

• Table names haveadditional rules.

Examples:

my-project.mydataset.mytablemydataset.mytablemy-tablemytable`287mytable`

Column names

A column name represents the name of a column in a table.

• Column names can be quoted identifiers or unquoted identifiers.
• If unquoted, identifiers support dashed identifiers when referenced in aFROM orTABLE clause.
• Column names haveadditional rules.

Examples:

columnAcolumn-a`287column`

Field names

A field name represents the name of a field inside a complex data type suchas a struct orJSON object.

• A field name can be a quoted identifier or an unquoted identifier.
• Field names must adhere to all of the rules for column names.

Literals

A literal represents a constant value of a built-in data type. Some, but notall, data types can be expressed as literals.

String and bytes literals

A string literal represents a constant value of thestring data type. A bytes literal represents aconstant value of thebytes data type.

Both string and bytes literals must bequoted, either with single (') ordouble (") quotation marks, ortriple-quoted with groups of three single(''') or three double (""") quotation marks.

Formats for quoted literals

The following table lists all of the ways you can format a quoted literal.

Like in many other languages, such as Python and C++, you can divide aGoogleSQL string or bytes literal into chunks, each with its own quotingor raw specification. The literal value is the concatenation of all these parts.

This is useful for a variety of purposes, including readability, organization,formatting and maintainability, for example:

• You can break a literal into multiple chunks fit into a width of 80characters.
• You can break a literal into chunks of different quotings and rawspecifications to avoid escaping. For example, a string value inside aJSON string.
• You can change only one part of a literal through a macro, while the rest ofthe literal is unchanged.
• You can use string literal concatenation in other literals that includestrings such asDATE,TIMESTAMP,JSON, etc.

The following restrictions apply to these literal concatenations:

• You can't mix string and byte literals.
• You must ensure there is some separation between the concatenated parts,such as whitespace or comments.
• r specifiers apply only to the immediate chunk, not the rest of theliteral parts.
• Quoted identifiers don't concatenate.

Examples:

SELECT r'\n' /*Only the prev is raw!*/ '\n' "b" """c"d"e""" '''f'g'h''' "1" "2", br'\n'/*Only the prev is raw!*/ b'\n' b"b" b"""c"d"e""" b'''f'g'h''' b"1" b"2",  NUMERIC "1" r'2',  DECIMAL /*whole:*/ '1' /*fractional:*/ ".23" /*exponent=*/ "e+6",  BIGNUMERIC '1' r"2",  BIGDECIMAL /*sign*/ '-' /*whole:*/ '1' /*fractional:*/ ".23" /*exponent=*/ "e+6",  RANGE<DATE> '[2014-01-01,' /*comment*/ "2015-01-01)",  DATE '2014' "-01-01",  DATETIME '2016-01-01 ' r"12:00:00",  TIMESTAMP '2018-10-01 ' "12:00:00+08"

SELECT "\\n\nbc\"d\"ef'g'h12", b"\\n\nbc\"d\"ef'g'h12",  NUMERIC "12",  DECIMAL '1.23e+6',  BIGNUMERIC '12',  BIGDECIMAL "-1.23e+6",  RANGE<DATE> '[2014-01-01 2015-01-01)',  DATE '2014-01-01',  DATETIME '2016-01-01 12:00:00',  TIMESTAMP "2018-10-01 12:00:00+08"

Escape sequences for string and bytes literals

The following table lists all valid escape sequences for representingnon-alphanumeric characters in string and bytes literals. Any sequence not inthis table produces an error.

Integer literals

Integer literals are either a sequence of decimal digits (0–9) or a hexadecimalvalue that's prefixed with "0x" or "0X". Integers can be prefixed by "+"or "-" to represent positive and negative values, respectively.Examples:

1230xABC-123

An integer literal is interpreted as anINT64.

A integer literal represents a constant value of theinteger data type.

NUMERIC literals

You can constructNUMERIC literals using theNUMERIC keyword followed by a floating point value in quotes.

Examples:

SELECTNUMERIC'0';SELECTNUMERIC'123456';SELECTNUMERIC'-3.14';SELECTNUMERIC'-0.54321';SELECTNUMERIC'1.23456e05';SELECTNUMERIC'-9.876e-3';

ANUMERIC literal represents a constant value of theNUMERIC data type.

BIGNUMERIC literals

You can constructBIGNUMERIC literals using theBIGNUMERIC keyword followedby a floating point value in quotes.

Examples:

SELECTBIGNUMERIC'0';SELECTBIGNUMERIC'123456';SELECTBIGNUMERIC'-3.14';SELECTBIGNUMERIC'-0.54321';SELECTBIGNUMERIC'1.23456e05';SELECTBIGNUMERIC'-9.876e-3';

ABIGNUMERIC literal represents a constant value of theBIGNUMERIC data type.

Floating point literals

Syntax options:

[+-]DIGITS.[DIGITS][e[+-]DIGITS][+-][DIGITS].DIGITS[e[+-]DIGITS]DIGITSe[+-]DIGITS

DIGITS represents one or more decimal numbers (0 through 9) ande representsthe exponent marker (e or E).

Examples:

123.456e-67.1E458.4e2

Numeric literals that containeither a decimal point or an exponent marker are presumed to be type double.

Implicit coercion of floating point literals to float type is possible if thevalue is within the valid float range.

There is no literalrepresentation of NaN or infinity, but the following case-insensitive stringscan be explicitly cast to float:

• "NaN"
• "inf" or "+inf"
• "-inf"

A floating-point literal represents a constant value of thefloating-point data type.

Array literals

Array literals are comma-separated lists of elementsenclosed in square brackets. TheARRAY keyword is optional, and an explicitelement type T is also optional.

Examples:

[1,2,3]['x','y','xy']ARRAY[1,2,3]ARRAY<string>['x','y','xy']ARRAY<int64>[]

An array literal represents a constant value of thearray data type.

Struct literals

A struct literal is a struct whose fields are all literals. Struct literals canbe written using any of the syntaxes forconstructing astruct (tuple syntax, typeless struct syntax, or typedstruct syntax).

Note that tuple syntax requires at least two fields, in order to distinguish itfrom an ordinary parenthesized expression. To write a struct literal with asingle field, use typeless struct syntax or typed struct syntax.

A struct literal represents a constant value of thestruct data type.

Date literals

Syntax:

DATE'date_canonical_format'

Date literals contain theDATE keyword followed bydate_canonical_format,a string literal that conforms to the canonical date format, enclosed in singlequotation marks. Date literals support a range between theyears 1 and 9999, inclusive. Dates outside of this range are invalid.

For example, the following date literal represents September 27, 2014:

DATE'2014-09-27'

String literals in canonical date format also implicitly coerce to DATE typewhen used where a DATE-type expression is expected. For example, in the query

SELECT*FROMfooWHEREdate_col="2014-09-27"

the string literal"2014-09-27" will be coerced to a date literal.

A date literal represents a constant value of thedate data type.

Time literals

Syntax:

TIME'time_canonical_format'

Time literals contain theTIME keyword andtime_canonical_format, a string literal that conforms tothe canonical time format, enclosed in single quotation marks.

For example, the following time represents 12:30 p.m.:

TIME'12:30:00.45'

A time literal represents a constant value of thetime data type.

Datetime literals

Syntax:

DATETIME'datetime_canonical_format'

Datetime literals contain theDATETIME keyword anddatetime_canonical_format, a string literal thatconforms to the canonical datetime format, enclosed in single quotation marks.

For example, the following datetime represents 12:30 p.m. on September 27,2014:

DATETIME'2014-09-27 12:30:00.45'

Datetime literals support a range between the years 1 and 9999, inclusive.Datetimes outside of this range are invalid.

String literals with the canonical datetime format implicitly coerce to adatetime literal when used where a datetime expression is expected.

For example:

SELECT*FROMfooWHEREdatetime_col="2014-09-27 12:30:00.45"

In the query above, the string literal"2014-09-27 12:30:00.45" is coerced toa datetime literal.

A datetime literal can also include the optional characterT ort. Ifyou use this character, a space can't be included before or after it.These are valid:

DATETIME'2014-09-27T12:30:00.45'DATETIME'2014-09-27t12:30:00.45'

A datetime literal represents a constant value of thedatatime data type.

Timestamp literals

Syntax:

TIMESTAMP'timestamp_canonical_format'

Timestamp literals contain theTIMESTAMP keyword andtimestamp_canonical_format, a string literal thatconforms to the canonical timestamp format, enclosed in single quotation marks.

Timestamp literals support a range between the years 1 and 9999, inclusive.Timestamps outside of this range are invalid.

A timestamp literal can include a numerical suffix to indicate the time zone:

TIMESTAMP'2014-09-27 12:30:00.45-08'

If this suffix is absent, the default time zone,UTC, is used.

For example, the following timestamp represents 12:30 p.m. on September 27,2014 in the default time zone, UTC:

TIMESTAMP'2014-09-27 12:30:00.45'

For more information about time zones, seeTime zone.

String literals with the canonical timestamp format, including those withtime zone names, implicitly coerce to a timestamp literal when used where atimestamp expression is expected. For example, in the following query, thestring literal"2014-09-27 12:30:00.45 America/Los_Angeles" is coercedto a timestamp literal.

SELECT*FROMfooWHEREtimestamp_col="2014-09-27 12:30:00.45 America/Los_Angeles"

A timestamp literal can include these optional characters:

• T ort
• Z orz

If you use one of these characters, a space can't be included before or afterit. These are valid:

TIMESTAMP'2017-01-18T12:34:56.123456Z'TIMESTAMP'2017-01-18t12:34:56.123456'TIMESTAMP'2017-01-18 12:34:56.123456z'TIMESTAMP'2017-01-18 12:34:56.123456Z'

A timestamp literal represents a constant value of thetimestamp data type.

Time zone

Since timestamp literals must be mapped to a specific point in time, a time zoneis necessary to correctly interpret a literal. If a time zone isn't specifiedas part of the literal itself, then GoogleSQL uses the default time zonevalue, which the GoogleSQL implementation sets.

GoogleSQL can represent a time zones using a string, which representstheoffset from Coordinated Universal Time (UTC).

Examples:

'-08:00''-8:15''+3:00''+07:30''-7'

Time zones can also be expressed using stringtime zone names.

Examples:

TIMESTAMP'2014-09-27 12:30:00 America/Los_Angeles'TIMESTAMP'2014-09-27 12:30:00 America/Argentina/Buenos_Aires'

Range literals

Syntax:

RANGE<T>'[lower_bound, upper_bound)'

A range literal contains a contiguous range between twodates,datetimes, ortimestamps. The lower or upper bound can be unbounded,if desired.

Example of a date range literal with a lower and upper bound:

RANGE<DATE>'[2020-01-01, 2020-12-31)'

Example of a datetime range literal with a lower and upper bound:

RANGE<DATETIME>'[2020-01-01 12:00:00, 2020-12-31 12:00:00)'

Example of a timestamp range literal with a lower and upper bound:

RANGE<TIMESTAMP>'[2020-10-01 12:00:00+08, 2020-12-31 12:00:00+08)'

Examples of a range literal without a lower bound:

RANGE<DATE>'[UNBOUNDED, 2020-12-31)'

RANGE<DATE>'[NULL, 2020-12-31)'

Examples of a range literal without an upper bound:

RANGE<DATE>'[2020-01-01, UNBOUNDED)'

RANGE<DATE>'[2020-01-01, NULL)'

Examples of a range literal that includes all possible values:

RANGE<DATE>'[UNBOUNDED, UNBOUNDED)'

RANGE<DATE>'[NULL, NULL)'

There must be a single whitespace after the comma in a range literal, otherwisean error is produced. For example:

-- This range literal is valid:RANGE<DATE>'[2020-01-01, 2020-12-31)'

-- This range literal produces an error:RANGE<DATE>'[2020-01-01,2020-12-31)'

A range literal represents a constant value of therange data type.

Interval literals

An interval literal represents a constant value of theinterval data type. There are two types ofinterval literals:

• Interval literal with a single datetime part
• Interval literal with a datetime part range

An interval literal can be used directly inside of theSELECT statementand as an argument in some functions that support the interval data type.

Interval literal with a single datetime part

Syntax:

INTERVALint64_expressiondatetime_part

The single datetime part syntax includes anINT64 expression and asingleinterval-supported datetime part.For example:

-- 0 years, 0 months, 5 days, 0 hours, 0 minutes, 0 seconds (0-0 5 0:0:0)INTERVAL5DAY-- 0 years, 0 months, -5 days, 0 hours, 0 minutes, 0 seconds (0-0 -5 0:0:0)INTERVAL-5DAY-- 0 years, 0 months, 0 days, 0 hours, 0 minutes, 1 seconds (0-0 0 0:0:1)INTERVAL1SECOND

When a negative sign precedes the year or month part in an interval literal, thenegative sign distributes over the years and months. Or, when a negative signprecedes the time part in an interval literal, the negative sign distributesover the hours, minutes, and seconds. For example:

-- -2 years, -1 months, 0 days, 0 hours, 0 minutes, and 0 seconds (-2-1 0 0:0:0)INTERVAL-25MONTH-- 0 years, 0 months, 0 days, -1 hours, -30 minutes, and 0 seconds (0-0 0 -1:30:0)INTERVAL-90MINUTE

For more information on how to construct interval with a single datetime part,seeConstruct an interval with a single datetime part.

Interval literal with a datetime part range

Syntax:

INTERVALdatetime_parts_stringstarting_datetime_partTOending_datetime_part

The range datetime part syntax includes adatetime parts string,astarting datetime part, and anending datetime part.

For example:

-- 0 years, 0 months, 0 days, 10 hours, 20 minutes, 30 seconds (0-0 0 10:20:30.520)INTERVAL'10:20:30.52'HOURTOSECOND-- 1 year, 2 months, 0 days, 0 hours, 0 minutes, 0 seconds (1-2 0 0:0:0)INTERVAL'1-2'YEARTOMONTH-- 0 years, 1 month, -15 days, 0 hours, 0 minutes, 0 seconds (0-1 -15 0:0:0)INTERVAL'1 -15'MONTHTODAY-- 0 years, 0 months, 1 day, 5 hours, 30 minutes, 0 seconds (0-0 1 5:30:0)INTERVAL'1 5:30'DAYTOMINUTE

When a negative sign precedes the year or month part in an interval literal, thenegative sign distributes over the years and months. Or, when a negative signprecedes the time part in an interval literal, the negative sign distributesover the hours, minutes, and seconds. For example:

-- -23 years, -2 months, 10 days, -12 hours, -30 minutes, and 0 seconds (-23-2 10 -12:30:0)INTERVAL'-23-2 10 -12:30'YEARTOMINUTE-- -23 years, -2 months, 10 days, 0 hours, -30 minutes, and 0 seconds (-23-2 10 -0:30:0)SELECTINTERVAL'-23-2 10 -0:30'YEARTOMINUTE-- Produces an error because the negative sign for minutes must come before the hour.SELECTINTERVAL'-23-2 10 0:-30'YEARTOMINUTE-- Produces an error because the negative sign for months must come before the year.SELECTINTERVAL'23--2 10 0:30'YEARTOMINUTE-- 0 years, -2 months, 10 days, 0 hours, 30 minutes, and 0 seconds (-0-2 10 0:30:0)SELECTINTERVAL'-2 10 0:30'MONTHTOMINUTE-- 0 years, 0 months, 0 days, 0 hours, -30 minutes, and -10 seconds (0-0 0 -0:30:10)SELECTINTERVAL'-30:10'MINUTETOSECOND

For more information on how to construct interval with a datetime part range,seeConstruct an interval with a datetime part range.

JSON literals

Syntax:

JSON'json_formatted_data'

A JSON literal representsJSON-formatted data.

Example:

JSON'{  "id": 10,  "type": "fruit",  "name": "apple",  "on_menu": true,  "recipes":    {      "salads":      [        { "id": 2001, "type": "Walnut Apple Salad" },        { "id": 2002, "type": "Apple Spinach Salad" }      ],      "desserts":      [        { "id": 3001, "type": "Apple Pie" },        { "id": 3002, "type": "Apple Scones" },        { "id": 3003, "type": "Apple Crumble" }      ]    }}'

A JSON literal represents a constant value of theJSON data type.

Case sensitivity

GoogleSQL follows these rules for case sensitivity:

Reserved keywords

Keywords are a group of tokens that have special meaning in the GoogleSQLlanguage, and have the following characteristics:

• Keywords can't be used as identifiers unless enclosed by backtick (`) characters.
• Keywords are case-insensitive.

GoogleSQL has the following reserved keywords.

Terminating semicolons

You can optionally use a terminating semicolon (;) when you submit a querystring statement through an Application Programming Interface (API).

In a request containing multiple statements, you must separate statements withsemicolons, but the semicolon is generally optional after the final statement.Some interactive tools require statements to have a terminating semicolon.

Trailing commas

You can optionally use a trailing comma (,) at the end of a column list in aSELECT statement. You might have a trailing comma as the result ofprogrammatically creating a column list.

Example

SELECTname,release_date,FROMBooks

Query parameters

You can use query parameters to substitute arbitrary expressions.However, query parameters can't be used to substitute identifiers,column names, table names, or other parts of the query itself.Query parameters are defined outside of the query statement.

Client APIs allow the binding of parameter names to values; the query enginesubstitutes a bound value for a parameter at execution time.

Query parameters can't be used in the SQL body of these statements:CREATE FUNCTION,CREATE VIEW,CREATE MATERIALIZED VIEW, andCREATE PROCEDURE.

Named query parameters

Syntax:

@parameter_name

A named query parameter is denoted using anidentifierpreceded by the@ character. Named queryparameters can't be used alongsidepositional queryparameters.

A named query parameter can start with an identifier or a reserved keyword.An identifier can be unquoted or quoted.

Example:

This example returns all rows whereLastName is equal to the value of thenamed query parametermyparam.

SELECT*FROMRosterWHERELastName=@myparam

Positional query parameters

Positional query parameters are denoted using the? character.Positional parameters are evaluated by the order in which they are passed in.Positional query parameters can't be usedalongsidenamed query parameters.

Example:

This query returns all rows whereLastName andFirstName are equal to thevalues passed into this query. The order in which these values are passed inmatters. If the last name is passed in first, followed by the first name, theexpected results will not be returned.

SELECT*FROMRosterWHEREFirstName=?andLastName=?

Comments

Comments are sequences of characters that the parser ignores.GoogleSQL supports the following types of comments.

Single-line comments

Use a single-line comment if you want the comment to appear on a line by itself.

Examples

# this is a single-line commentSELECTbookFROMlibrary;

-- this is a single-line commentSELECTbookFROMlibrary;

/* this is a single-line comment */SELECTbookFROMlibrary;

SELECTbookFROMlibrary/* this is a single-line comment */WHEREbook="Ulysses";

Inline comments

Use an inline comment if you want the comment to appear on the same line asa statement. A comment that's prepended with# or-- must appear to theright of a statement.

Examples

SELECTbookFROMlibrary;# this is an inline comment

SELECTbookFROMlibrary;-- this is an inline comment

SELECTbookFROMlibrary;/* this is an inline comment */

SELECTbookFROMlibrary/* this is an inline comment */WHEREbook="Ulysses";

Multiline comments

Use a multiline comment if you need the comment to span multiple lines.Nested multiline comments aren't supported.

Examples

SELECTbookFROMlibrary/*  This is a multiline comment  on multiple lines*/WHEREbook="Ulysses";

SELECTbookFROMlibrary/* this is a multiline commenton two lines */WHEREbook="Ulysses";