Query syntax in GoogleSQL

Query statements scan one or more tables or expressions and return the computed result rows. This topic describes the syntax for SQL queries in GoogleSQL for Spanner.

SQL syntax notation rules

The following table lists and describes the syntax notation rules that GoogleSQL documentation commonly uses.

SQL syntax

query_statement:
  [ statement_hint_expr ]
  [ table_hint_expr ]
  [ group_hint_expr ]
  [ join_hint_expr ]
  query_expr

query_expr:
  [ WITH cte[, ...] ]
  { select | ( query_expr ) | set_operation }
  [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
  [ LIMIT count [ OFFSET skip_rows ] ]
  [ FOR UPDATE ]

select:
  SELECT
    [ { ALL | DISTINCT } ]
    [ AS { typename | STRUCT | VALUE } ]
    select_list
  [ FROM from_clause[, ...] ]
  [ WHERE bool_expression ]
  [ GROUP [  group_hint_expr ] BY group_by_specification ]
  [ HAVING bool_expression ]

SELECT statement

SELECT
  [ { ALL | DISTINCT } ]
  [ AS { typename | STRUCT | VALUE } ]
  select_list

select_list:
  { select_all | select_expression } [, ...]

select_all:
  [ expression. ]*
  [ EXCEPT ( column_name [, ...] ) ]
  [ REPLACE ( expression AS column_name [, ...] ) ]

select_expression:
  expression [ [ AS ] alias ]

The SELECT list defines the columns that the query will return. Expressions in the SELECT list can refer to columns in any of the from_items in its corresponding FROM clause.

Each item in the SELECT list is one of:

• *
• expression
• expression.*

SELECT *

SELECT *, often referred to as select star, produces one output column for each column that's visible after executing the full query.

SELECT * FROM (SELECT "apple" AS fruit, "carrot" AS vegetable);

/*-------+-----------+
 | fruit | vegetable |
 +-------+-----------+
 | apple | carrot    |
 +-------+-----------*/

SELECT expression

Items in a SELECT list can be expressions. These expressions evaluate to a single value and produce one output column, with an optional explicit alias.

If the expression doesn't have an explicit alias, it receives an implicit alias according to the rules for implicit aliases, if possible. Otherwise, the column is anonymous and you can't refer to it by name elsewhere in the query.

SELECT expression.*

An item in a SELECT list can also take the form of expression.*. This produces one output column for each column or top-level field of expression. The expression must either be a table alias or evaluate to a single value of a data type with fields, such as a STRUCT.

The following query produces one output column for each column in the table groceries, aliased as g.

WITH groceries AS
  (SELECT "milk" AS dairy,
   "eggs" AS protein,
   "bread" AS grain)
SELECT g.*
FROM groceries AS g;

/*-------+---------+-------+
 | dairy | protein | grain |
 +-------+---------+-------+
 | milk  | eggs    | bread |
 +-------+---------+-------*/

More examples:

WITH locations AS
  (SELECT STRUCT("Seattle" AS city, "Washington" AS state) AS location
  UNION ALL
  SELECT STRUCT("Phoenix" AS city, "Arizona" AS state) AS location)
SELECT l.location.*
FROM locations l;

/*---------+------------+
 | city    | state      |
 +---------+------------+
 | Seattle | Washington |
 | Phoenix | Arizona    |
 +---------+------------*/

WITH locations AS
  (SELECT ARRAY<STRUCT<city STRING, state STRING>>[("Seattle", "Washington"),
    ("Phoenix", "Arizona")] AS location)
SELECT l.LOCATION[offset(0)].*
FROM locations l;

/*---------+------------+
 | city    | state      |
 +---------+------------+
 | Seattle | Washington |
 +---------+------------*/

SELECT * EXCEPT

A SELECT * EXCEPT statement specifies the names of one or more columns to exclude from the result. All matching column names are omitted from the output.

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * EXCEPT (order_id)
FROM orders;

/*-----------+----------+
 | item_name | quantity |
 +-----------+----------+
 | sprocket  | 200      |
 +-----------+----------*/

SELECT * REPLACE

A SELECT * REPLACE statement specifies one or more expression AS identifier clauses. Each identifier must match a column name from the SELECT * statement. In the output column list, the column that matches the identifier in a REPLACE clause is replaced by the expression in that REPLACE clause.

A SELECT * REPLACE statement doesn't change the names or order of columns. However, it can change the value and the value type.

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * REPLACE ("widget" AS item_name)
FROM orders;

/*----------+-----------+----------+
 | order_id | item_name | quantity |
 +----------+-----------+----------+
 | 5        | widget    | 200      |
 +----------+-----------+----------*/

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * REPLACE (quantity/2 AS quantity)
FROM orders;

/*----------+-----------+----------+
 | order_id | item_name | quantity |
 +----------+-----------+----------+
 | 5        | sprocket  | 100      |
 +----------+-----------+----------*/

SELECT DISTINCT

A SELECT DISTINCT statement discards duplicate rows and returns only the remaining rows. SELECT DISTINCT can't return columns of the following types:

• PROTO
• STRUCT
• ARRAY
• GRAPH_ELEMENT
• GRAPH_PATH

SELECT ALL

A SELECT ALL statement returns all rows, including duplicate rows. SELECT ALL is the default behavior of SELECT.

Using STRUCTs with SELECT

• Queries that return a STRUCT at the root of the return type aren't supported in Spanner APIs. For example, the following query is supported only as a subquery:

  SELECT STRUCT(1, 2) FROM Users;
  

• Returning an array of structs is supported. For example, the following queries are supported in Spanner APIs:

  SELECT ARRAY(SELECT STRUCT(1 AS A, 2 AS B)) FROM Users;
  

  SELECT ARRAY(SELECT AS STRUCT 1 AS a, 2 AS b) FROM Users;
  

• However, query shapes that can return an ARRAY<STRUCT<...>> typed NULL value or an ARRAY<STRUCT<...>> typed value with an element that's NULL aren't supported in Spanner APIs, so the following query is supported only as a subquery:

  SELECT ARRAY(SELECT IF(STARTS_WITH(Users.username, "a"), NULL, STRUCT(1, 2)))
  FROM Users;
  

See Querying STRUCT elements in an ARRAY for more examples on how to query STRUCTs inside an ARRAY.

Also see notes about using STRUCTs in subqueries.

SELECT AS STRUCT

SELECT AS STRUCT expr [[AS] struct_field_name1] [,...]

This produces a value table with a STRUCT row type, where the STRUCT field names and types match the column names and types produced in the SELECT list.

Example:

SELECT ARRAY(SELECT AS STRUCT 1 a, 2 b)

SELECT AS STRUCT can be used in a scalar or array subquery to produce a single STRUCT type grouping multiple values together. Scalar and array subqueries (see Subqueries) are normally not allowed to return multiple columns, but can return a single column with STRUCT type.

Anonymous columns are allowed.

Example:

SELECT AS STRUCT 1 x, 2, 3

The query above produces STRUCT values of type STRUCT<int64 x, int64, int64>. The first field has the name x while the second and third fields are anonymous.

The example above produces the same result as this SELECT AS VALUE query using a struct constructor:

SELECT AS VALUE STRUCT(1 AS x, 2, 3)

Duplicate columns are allowed.

Example:

SELECT AS STRUCT 1 x, 2 y, 3 x

The query above produces STRUCT values of type STRUCT<int64 x, int64 y, int64 x>. The first and third fields have the same name x while the second field has the name y.

The example above produces the same result as this SELECT AS VALUE query using a struct constructor:

SELECT AS VALUE STRUCT(1 AS x, 2 AS y, 3 AS x)

SELECT AS typename

SELECT AS typename
  expr [[AS] field]
  [, ...]

A SELECT AS typename statement produces a value table where the row type is a specific named type. Currently, protocol buffers are the only supported type that can be used with this syntax.

When selecting as a type that has fields, such as a proto message type, the SELECT list may produce multiple columns. Each produced column must have an explicit or implicit alias that matches a unique field of the named type.

When used with SELECT DISTINCT, or GROUP BY or ORDER BY using column ordinals, these operators are first applied on the columns in the SELECT list. The value construction happens last. This means that DISTINCT can be applied on the input columns to the value construction, including in cases where DISTINCT wouldn't be allowed after value construction because grouping isn't supported on the constructed type.

The following is an example of a SELECT AS typename query.

SELECT AS tests.TestProtocolBuffer mytable.key int64_val, mytable.name string_val
FROM mytable;

The query returns the output as a tests.TestProtocolBuffer protocol buffer. mytable.key int64_val means that values from the key column are stored in the int64_val field in the protocol buffer. Similarly, values from the mytable.name column are stored in the string_val protocol buffer field.

To learn more about protocol buffers, see Work with protocol buffers.

SELECT AS VALUE

SELECT AS VALUE produces a value table from any SELECT list that produces exactly one column. Instead of producing an output table with one column, possibly with a name, the output will be a value table where the row type is just the value type that was produced in the one SELECT column. Any alias the column had will be discarded in the value table.

Example:

SELECT AS VALUE 1

The query above produces a table with row type INT64.

Example:

SELECT AS VALUE STRUCT(1 AS a, 2 AS b) xyz

The query above produces a table with row type STRUCT<a int64, b int64>.

Example:

SELECT AS VALUE v FROM (SELECT AS STRUCT 1 a, true b) v WHERE v.b

Given a value table v as input, the query above filters out certain values in the WHERE clause, and then produces a value table using the exact same value that was in the input table. If the query above didn't use SELECT AS VALUE, then the output table schema would differ from the input table schema because the output table would be a regular table with a column named v containing the input value.

FROM clause

FROM from_clause[, ...]

from_clause:
  from_item
  [ tablesample_operator ]

from_item:
  {
    table_name [ table_hint_expr ] [ as_alias ]
    | { join_operation | ( join_operation ) }
    | ( query_expr ) [ table_hint_expr ] [ as_alias ]
    | field_path
    | unnest_operator
    | cte_name [ table_hint_expr ] [ as_alias ]
    | graph_table_operator [ as_alias ]
  }

as_alias:
  [ AS ] alias

The FROM clause indicates the table or tables from which to retrieve rows, and specifies how to join those rows together to produce a single stream of rows for processing in the rest of the query.

tablesample_operator

See TABLESAMPLE operator.

graph_table_operator

See GRAPH_TABLE operator.

table_name

The name of an existing table.

SELECT * FROM Roster;

join_operation

See Join operation.

query_expr

( query_expr ) [ [ AS ] alias ] is a table subquery.

field_path

In the FROM clause, field_path is any path that resolves to a field within a data type. field_path can go arbitrarily deep into a nested data structure.

Some examples of valid field_path values include:

SELECT * FROM T1 t1, t1.array_column;

SELECT * FROM T1 t1, t1.struct_column.array_field;

SELECT (SELECT ARRAY_AGG(c) FROM t1.array_column c) FROM T1 t1;

SELECT a.struct_field1 FROM T1 t1, t1.array_of_structs a;

SELECT (SELECT STRING_AGG(a.struct_field1) FROM t1.array_of_structs a) FROM T1 t1;

Field paths in the FROM clause must end in an array or a repeated field. In addition, field paths can't contain arrays or repeated fields before the end of the path. For example, the path array_column.some_array.some_array_field is invalid because it contains an array before the end of the path.

unnest_operator

See UNNEST operator.

cte_name

Common table expressions (CTEs) in a WITH Clause act like temporary tables that you can reference anywhere in the FROM clause. In the example below, subQ1 and subQ2 are CTEs.

Example:

WITH
  subQ1 AS (SELECT * FROM Roster WHERE SchoolID = 52),
  subQ2 AS (SELECT SchoolID FROM subQ1)
SELECT DISTINCT * FROM subQ2;

UNNEST operator

unnest_operator:
  {
    UNNEST( array ) [ as_alias ]
    | array_path [ as_alias ]
  }
  [ table_hint_expr ]
  [ WITH OFFSET [ as_alias ] ]

array:
  { array_expression | array_path }

as_alias:
  [AS] alias

The UNNEST operator takes an array and returns a table with one row for each element in the array. The output of UNNEST is one value table column. For these ARRAY element types, SELECT * against the value table column returns multiple columns:

• STRUCT
• PROTO

Input values:

• array_expression: An expression that produces an array and that's not an array path.

• array_path: The path to an ARRAY type.

  • In an implicit UNNEST operation, the path must start with a range variable name.
  • In an explicit UNNEST operation, the path can optionally start with a range variable name.

  The UNNEST operation with any correlated array_path must be on the right side of a CROSS JOIN, LEFT JOIN, or INNER JOIN operation.

• as_alias: If specified, defines the explicit name of the value table column containing the array element values. It can be used to refer to the column elsewhere in the query.

• WITH OFFSET: UNNEST destroys the order of elements in the input array. Use this optional clause to return an additional column with the array element indexes, or offsets. Offset counting starts at zero for each row produced by the UNNEST operation. This column has an optional alias; If the optional alias isn't used, the default column name is offset.

  Example:

  SELECT * FROM UNNEST ([10,20,30]) as numbers WITH OFFSET;
  
  /*---------+--------+
   | numbers | offset |
   +---------+--------+
   | 10      | 0      |
   | 20      | 1      |
   | 30      | 2      |
   +---------+--------*/
  

You can also use UNNEST outside of the FROM clause with the IN operator.

For several ways to use UNNEST, including construction, flattening, and filtering, see Work with arrays.

To learn more about the ways you can use UNNEST explicitly and implicitly, see Explicit and implicit UNNEST.

UNNEST and structs

For an input array of structs, UNNEST returns a row for each struct, with a separate column for each field in the struct. The alias for each column is the name of the corresponding struct field.

Example:

SELECT *
FROM UNNEST(
  ARRAY<
    STRUCT<
      x INT64,
      y STRING,
      z ARRAY<INT64>>>[
        (1, 'foo', [10, 11]),
        (3, 'bar', [20, 21])]);

/*---+-----+----------+
 | x | y   | z        |
 +---+-----+----------+
 | 1 | foo | {10, 11} |
 | 3 | bar | {20, 21} |
 +---+-----+----------*/

UNNEST and protocol buffers

For an input array of protocol buffers, UNNEST returns a row for each protocol buffer, with a separate column for each field in the protocol buffer. The alias for each column is the name of the corresponding protocol buffer field.

Example:

SELECT *
FROM UNNEST(
  ARRAY<googlesql.examples.music.Album>[
    NEW googlesql.examples.music.Album (
      'The Goldberg Variations' AS album_name,
      ['Aria', 'Variation 1', 'Variation 2'] AS song
    )
  ]
);

/*-------------------------+--------+----------------------------------+
 | album_name              | singer | song                             |
 +-------------------------+--------+----------------------------------+
 | The Goldberg Variations | NULL   | [Aria, Variation 1, Variation 2] |
 +-------------------------+--------+----------------------------------*/

As with structs, you can alias UNNEST to define a range variable. You can reference this alias in the SELECT list to return a value table where each row is a protocol buffer element from the array.

SELECT proto_value
FROM UNNEST(
  ARRAY<googlesql.examples.music.Album>[
    NEW googlesql.examples.music.Album (
      'The Goldberg Variations' AS album_name,
      ['Aria', 'Var. 1'