Query syntax

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

SQL syntax notation rules

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

Notation	Example	Description
Square brackets	[ ]	Optional clauses
Parentheses	( )	Literal parentheses
Vertical bar	|	Logical XOR (exclusive OR)
Curly braces	{ }	A set of options, such as { a | b | c }. Select one option.
Ellipsis	...	The preceding item can repeat.
Comma	,	Literal comma
Comma followed by an ellipsis	, ...	The preceding item can repeat in a comma-separated list.
Item list	item [, ...]	One or more items
	[item, ...]	Zero or more items
Double quotes	""	The enclosed syntax characters (for example, "{"..."}") are literal and required.
Angle brackets	<>	Literal angle brackets

SQL syntax

query_statement:
  query_expr

query_expr:
  [ WITH [ RECURSIVE ] { non_recursive_cte | recursive_cte }[, ...] ]
  { select | ( query_expr ) | set_operation }
  [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
  [ LIMIT count [ OFFSET skip_rows ] ]

select:
  SELECT
    [ { ALL | DISTINCT } ]
    select_list
  [ FROM from_clause[, ...] ]
  [ WHERE bool_expression ]
  [ GROUP BY group_by_specification ]
  [ HAVING bool_expression ]
  [ QUALIFY bool_expression ]
  [ WINDOW window_clause ]

SELECT statement

SELECT
  [ { ALL | DISTINCT } ]
  select_list

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

select_all:
  [ expression. ]*

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.

Note: The * or .* wildcard preserves the order of the fields in the data structure on which they're operating.

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 DISTINCT

A SELECT DISTINCT statement discards duplicate rows and returns only the remaining rows.

In the following example, SELECT DISTINCT is used to produce distinct arrays:

WITH PlayerStats AS (
  SELECT ['Coolidge', 'Adams'] as Name, 3 as PointsScored UNION ALL
  SELECT ['Adams', 'Buchanan'], 0 UNION ALL
  SELECT ['Coolidge', 'Adams'], 1 UNION ALL
  SELECT ['Kiran', 'Noam'], 1)
SELECT DISTINCT Name
FROM PlayerStats;

/*------------------+
 | Name             |
 +------------------+
 | [Coolidge,Adams] |
 | [Adams,Buchanan] |
 | [Kiran,Noam]     |
 +------------------*/

In the following example, SELECT DISTINCT is used to produce distinct structs:

WITH
  PlayerStats AS (
    SELECT
      STRUCT<last_name STRING, first_name STRING, age INT64>(
        'Adams', 'Noam', 20) AS Player,
      3 AS PointsScored UNION ALL
    SELECT ('Buchanan', 'Jie', 19), 0 UNION ALL
    SELECT ('Adams', 'Noam', 20), 4 UNION ALL
    SELECT ('Buchanan', 'Jie', 19), 13
  )
SELECT DISTINCT Player
FROM PlayerStats;

/*--------------------------+
 | player                   |
 +--------------------------+
 | {                        |
 |   last_name: "Adams",    |
 |   first_name: "Noam",    |
 |   age: 20                |
 |  }                       |
 +--------------------------+
 | {                        |
 |   last_name: "Buchanan", |
 |   first_name: "Jie",     |
 |   age: 19                |
 |  }                       |
 +---------------------------*/

SELECT ALL

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

FROM clause

FROM from_clause[, ...]

from_clause:
  from_item

from_item:
  {
    table_name [ as_alias ]
    | { join_operation | ( join_operation ) }
    | ( query_expr ) [ as_alias ]
    | field_path
    | unnest_operator
    | cte_name [ 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.

table_name

The name (optionally qualified) of an existing table.

SELECT * FROM Roster;
SELECT * FROM db.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 field. In addition, field paths can't contain arrays 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.

Note: If a path has only one name, it's interpreted as a table. To work around this, wrap the path using UNNEST, or use the fully-qualified path.

Note: If a path has more than one name, and it matches a field name, it's interpreted as a field name. To force the path to be interpreted as a table name, wrap the path using `.

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;

The WITH clause hides any permanent tables with the same name for the duration of the query, unless you qualify the table name, for example:

db.Roster.

UNNEST operator

unnest_operator:
  {
    UNNEST( array ) [ as_alias ]
    | array_path [ as_alias ]
  }
  [ 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][value-tables] column. For these ARRAY element types, SELECT * against the value table column returns multiple columns:

• STRUCT

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 STRUCT<a INT64, b INT64>>>[
        (1, 'foo', (10, 11)),
        (3, 'bar', (20, 21))]);

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

Explicit and implicit UNNEST

Array unnesting can be either explicit or implicit. To learn more, see the following sections.

Explicit unnesting

The UNNEST keyword is required in explicit unnesting. For example:

WITH Coordinates AS (SELECT [1,2] AS position)
SELECT results FROM Coordinates, UNNEST(Coordinates.position) AS results;

This example and the following examples use the array_path called Coordinates.position to illustrate unnesting.

Implicit unnesting

The UNNEST keyword isn't used in implicit unnesting.

For example:

WITH Coordinates AS (SELECT [1,2] AS position)
SELECT results FROM Coordinates, Coordinates.position AS results;

Tables and implicit unnesting

When you use array_path with implicit UNNEST, array_path must be prepended with the table. For example:

WITH Coordinates AS (SELECT [1,2] AS position)
SELECT results FROM Coordinates, Coordinates.position AS results;

UNNEST and NULL values

UNNEST treats NULL values as follows:

• NULL and empty arrays produce zero rows.
• An array containing NULL values produces rows containing NULL values.

Join operation

join_operation:
  { cross_join_operation | condition_join_operation }

cross_join_operation:
  from_item cross_join_operator from_item

condition_join_operation:
  from_item condition_join_operator from_item join_condition

cross_join_operator:
  { CROSS JOIN | , }

condition_join_operator:
  {
    [INNER] JOIN
    | FULL [OUTER] JOIN
    | LEFT [OUTER] JOIN
    | RIGHT [OUTER] JOIN
  }

join_condition:
  { on_clause | using_clause }

on_clause:
  ON bool_expression

using_clause:
  USING ( column_list )

The JOIN operation combines two from_items so that the SELECT clause can query them as one source. The join operation pairs rows from the two from_items, and a join condition filters those pairs to determine which rows appear in the output.

[INNER] JOIN

An INNER JOIN, or simply JOIN, effectively calculates the Cartesian product of the two from_items and discards all rows that don't meet the join condition. Effectively means that it's possible to implement an INNER JOIN without actually calculating the Cartesian product.

FROM A INNER JOIN B ON A.w = B.y

/*
Table A       Table B       Result
+-------+     +-------+     +---------------+
| w | x |  *  | y | z |  =  | w | x | y | z |
+-------+     +-------+     +---------------+
| 1 | a |     | 2 | k |     | 2 | b | 2 | k |
| 2 | b |     | 3 | m |     | 3 | c | 3 | m |
| 3 | c |     | 3 | n |     | 3 | c | 3 | n |
| 3 | d |     | 4 | p |     | 3 | d | 3 | m |
+-------+     +-------+     | 3 | d | 3 | n |
                            +---------------+
*/

FROM A INNER JOIN B USING (x)

/*
Table A       Table B       Result
+-------+     +-------+     +-----------+
| x | y |  *  | x | z |  =  | x | y | z |
+-------+     +-------+     +-----------+
| 1 | a |     | 2 | k |     | 2 | b | k |
| 2 | b |     | 3 | m |     | 3 | c | m |
| 3 | c |     | 3 | n |     | 3 | c | n |
| 3 | d |     | 4 | p |     | 3 | d | m |
+-------+     +-------+     | 3 | d | n |
                            +-----------+
*/

Example

This query performs an INNER JOIN on the Roster and TeamMascot tables.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

/*---------------------------+
 | LastName   | Mascot       |
 +---------------------------+
 | Adams      | Jaguars      |
 | Buchanan   | Lakers       |
 | Coolidge   | Lakers       |
 | Davis      | Knights      |
 +---------------------------*/

You can use a correlated INNER JOIN to flatten an array into a set of rows. To learn more, see Convert elements in an array to rows in a table.

CROSS JOIN

CROSS JOIN returns the Cartesian product of the two from_items. In other words, it combines each row from the first from_item with each row from the second from_item.

If the rows of the two from_items are independent, then the result has M * N rows, given M rows in one from_item and N in the other. Note that this still holds for the case when either from_item has zero rows.

In a FROM clause, a CROSS JOIN can be written like this:

FROM A CROSS JOIN B

/*
Table A       Table B       Result
+-------+     +-------+     +---------------+
| w | x |  *  | y | z |  =  | w | x | y | z |
+-------+     +-------+     +---------------+
| 1 | a |     | 2 | c |     | 1 | a | 2 | c |
| 2 | b |     | 3 | d |     | 1 | a | 3 | d |
+-------+     +-------+     | 2 | b | 2 | c |
                            | 2 | b | 3 | d |
                            +---------------+
*/

You can use a correlated cross join to convert or flatten an array into a set of rows, though the (equivalent) INNER JOIN is preferred over CROSS JOIN for this case. To learn more, see Convert elements in an array to rows in a table.

Examples

This query performs an CROSS JOIN on the Roster and TeamMascot tables.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster CROSS JOIN TeamMascot;

/*---------------------------+
 | LastName   | Mascot       |
 +---------------------------+
 | Adams      | Jaguars      |
 | Adams      | Knights      |
 | Adams      | Lakers       |
 | Adams      | Mustangs     |
 | Buchanan   | Jaguars      |
 | Buchanan   | Knights      |
 | Buchanan   | Lakers       |
 | Buchanan   | Mustangs     |
 | ...                       |
 +---------------------------*/

Comma cross join (,)

CROSS JOINs can be written implicitly with a comma. This is called a comma cross join.

A comma cross join looks like this in a FROM clause:

FROM A, B

/*
Table A       Table B       Result
+-------+     +-------+     +---------------+
| w | x |  *  | y | z |  =  | w | x | y | z |
+-------+     +-------+     +---------------+
| 1 | a |     | 2 | c |     | 1 | a | 2 | c |
| 2 | b |     | 3 | d |     | 1 | a | 3 | d |
+-------+     +-------+     | 2 | b | 2 | c |
                            | 2 | b | 3 | d |
                            +---------------+
*/

You can't write comma cross joins inside parentheses. To learn more, see Join operations in a sequence.

FROM (A, B)  // INVALID

You can use a correlated comma cross join to convert or flatten an array into a set of rows. To learn more, see Convert elements in an array to rows in a table.

Examples

This query performs a comma cross join on the Roster and TeamMascot tables.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster, TeamMascot;

/*---------------------------+
 | LastName   | Mascot       |
 +---------------------------+
 | Adams      | Jaguars      |
 | Adams      | Knights      |
 | Adams      | Lakers       |
 | Adams      | Mustangs     |
 | Buchanan   | Jaguars      |
 | Buchanan   | Knights      |
 | Buchanan   | Lakers       |
 | Buchanan   | Mustangs     |
 | ...                       |
 +---------------------------*/

FULL [OUTER] JOIN

A FULL OUTER JOIN (or simply FULL JOIN) returns all fields for all matching rows in both from_items that meet the join condition. If a given row from one from_item doesn't join to any row in the other from_item, the row returns with NULL values for all columns from the other from_item.

FROM A FULL OUTER JOIN B ON A.w = B.y

/*
Table A       Table B       Result
+-------+     +-------+     +---------------------------+
| w | x |  *  | y | z |  =  | w    | x    | y    | z    |
+-------+     +-------+     +---------------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | 2    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | 3    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | 3    | n    |
+-------+     +-------+     | 3    | d    | 3    | m    |
                            | 3    | d    | 3    | n    |
                            | NULL | NULL | 4    | p    |
                            +---------------------------+
*/

FROM A FULL OUTER JOIN B USING (x)

/*
Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | n    |
+-------+     +-------+     | 3    | d    | m    |
                            | 3    | d    | n    |
                            | 4    | NULL | p    |
                            +--------------------+
*/

Example

This query performs a FULL JOIN on the Roster and TeamMascot tables.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster FULL JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

/*---------------------------+
 | LastName   | Mascot       |
 +---------------------------+
 | Adams      | Jaguars      |
 | Buchanan   | Lakers       |
 | Coolidge   | Lakers       |
 | Davis      | Knights      |
 | Eisenhower | NULL         |
 | NULL       | Mustangs     |
 +---------------------------*/

LEFT [OUTER] JOIN

The result of a LEFT OUTER JOIN (or simply LEFT JOIN) for two from_items always retains all rows of the left from_item in the JOIN operation, even if no rows in the right from_item satisfy the join predicate.

All rows from the left from_item are retained; if a given row from the left from_item doesn't join to any row in the right from_item, the row will return with NULL values for all columns exclusively from the right from_item. Rows from the right from_item that don't join to any row in the left from_item are discarded.

FROM A LEFT OUTER JOIN B ON A.w = B.y

/*
Table A       Table B       Result
+-------+     +-------+     +---------------------------+
| w | x |  *  | y | z |  =  | w    | x    | y    | z    |
+-------+     +-------+     +---------------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | 2    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | 3    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | 3    | n    |
+-------+     +-------+     | 3    | d    | 3    | m    |
                            | 3    | d    | 3    | n    |
                            +---------------------------+
*/

FROM A LEFT OUTER JOIN B USING (x)

/*
Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | n    |
+-------+     +-------+     | 3    | d    | m    |
                            | 3    | d    | n    |
                            +--------------------+
*/

Example

This query performs a LEFT JOIN on the Roster and TeamMascot tables.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster LEFT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

/*---------------------------+
 | LastName   | Mascot       |
 +---------------------------+
 | Adams      | Jaguars      |
 | Buchanan   | Lakers       |
 | Coolidge   | Lakers       |
 | Davis      | Knights      |
 | Eisenhower | NULL         |
 +---------------------------*/

RIGHT [OUTER] JOIN

The result of a RIGHT OUTER JOIN (or simply RIGHT JOIN) for two from_items always retains all rows of the right from_item in the JOIN operation, even if no rows in the left from_item satisfy the join predicate.

All rows from the right from_item are returned; if a given row from the right from_item doesn't join to any row in the left from_item, the row will return with NULL values for all columns exclusively from the left from_item. Rows from the left from_item that don't join to any row in the right from_item are discarded.

FROM A RIGHT OUTER JOIN B ON A.w = B.y

/*
Table A       Table B       Result
+-------+     +-------+     +---------------------------+
| w | x |  *  | y | z |  =  | w    | x    | y    | z    |
+-------+     +-------+     +---------------------------+
| 1 | a |     | 2 | k |     | 2    | b    | 2    | k    |
| 2 | b |     | 3 | m |     | 3    | c    | 3    | m    |
| 3 | c |     | 3 | n |     | 3    | c    | 3    | n    |
| 3 | d |     | 4 | p |     | 3    | d    | 3    | m    |
+-------+     +-------+     | 3    | d    | 3    | n    |
                            | NULL | NULL | 4    | p    |
                            +---------------------------+
*/

FROM A RIGHT OUTER JOIN B USING (x)

/*
Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | k |     | 2    | b    | k    |
| 2 | b |     | 3 | m |     | 3    | c    | m    |
| 3 | c |     | 3 | n |     | 3    | c    | n    |
| 3 | d |     | 4 | p |     | 3    | d    | m    |
+-------+     +-------+     | 3    | d    | n    |
                            | 4    | NULL | p    |
                            +--------------------+
*/

Example

This query performs a RIGHT JOIN on the Roster and TeamMascot tables.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster RIGHT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

/*---------------------------+
 | LastName   | Mascot       |
 +---------------------------+
 | Adams      | Jaguars      |
 | Buchanan   | Lakers       |
 | Coolidge   | Lakers       |
 | Davis      | Knights      |
 | NULL       | Mustangs     |
 +---------------------------*/

Join conditions

A join operation pairs rows from two from_items, and a join condition filters those pairs to determine which rows appear in the output.

The two types of join conditions are the ON clause and USING clause.

When you use the JOIN keyword, you typically need an ON or USING clause to specify the join condition. An exception is the CROSS JOIN operator, which doesn't support an ON or USING clause.

ON clause

ON bool_expression

Description

The ON clause determines which rows from a join operation appear in the output. Given a row from the Cartesian product of two from_items, if bool_expression evaluates to TRUE, the join includes that row in the output.

Definitions:

• bool_expression: The boolean expression that specifies the condition for the join. This is frequently a comparison operation or logical combination of comparison operators.

Details:

Similarly to CROSS JOIN, ON produces a column once for each column in each input table.

A NULL join condition evaluation is equivalent to a FALSE evaluation.

If a column-order sensitive operation such as UNION or SELECT * is used with the ON join condition, the resulting table contains all of the columns from the left input in order, and then all of the columns from the right input in order.

Examples

The following examples show how to use the ON clause:

WITH
  A AS ( SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3),
  B AS ( SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4)
SELECT * FROM A INNER JOIN B ON A.x = B.x;

WITH
  A AS ( SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3),
  B AS ( SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4)
SELECT A.x, B.x FROM A INNER JOIN B ON A.x = B.x;

/*
Table A   Table B   Result (A.x, B.x)
+---+     +---+     +-------+
| x |  *  | x |  =  | x | x |
+---+     +---+     +-------+
| 1 |     | 2 |     | 2 | 2 |
| 2 |     | 3 |     | 3 | 3 |
| 3 |     | 4 |     +-------+
+---+     +---+
*/

WITH
  A AS ( SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT NULL),
  B AS ( SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5)
SELECT * FROM A LEFT OUTER JOIN B ON A.x = B.x;

WITH
  A AS ( SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT NULL),
  B AS ( SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5)
SELECT A.x, B.x FROM A LEFT OUTER JOIN B ON A.x = B.x;

/*
Table A    Table B   Result
+------+   +---+     +-------------+
| x    | * | x |  =  | x    | x    |
+------+   +---+     +-------------+
| 1    |   | 2 |     | 1    | NULL |
| 2    |   | 3 |     | 2    | 2    |
| 3    |   | 4 |     | 3    | 3    |
| NULL |   | 5 |     | NULL | NULL |
+------+   +---+     +-------------+
*/

WITH
  A AS ( SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT NULL),
  B AS ( SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5)
SELECT * FROM A FULL OUTER JOIN B ON A.x = B.x;

WITH
  A AS ( SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT NULL),
  B AS ( SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5)
SELECT A.x, B.x FROM A FULL OUTER JOIN B ON A.x = B.x;

/*
Table A    Table B   Result
+------+   +---+     +-------------+
| x    | * | x |  =  | x    | x    |
+------+   +---+     +-------------+
| 1    |   | 2 |     | 1    | NULL |
| 2    |   | 3 |     | 2    | 2    |
| 3    |   | 4 |     | 3    | 3    |
| NULL |   | 5 |     | NULL | NULL |
+------+   +---+     | NULL | 4    |
                     | NULL | 5    |
                     +-------------+
*/

USING clause

USING ( column_name_list )

column_name_list:
    column_name[, ...]

Description

The USING clause determines which rows from a join operation appear in the output. The USING clause performs an equality comparison operation on the columns named in column_name_list. Each column name in column_name_list must appear in both input tables. Given a row from the Cartesian product of two from_items, if the equality comparisons all evaluate to TRUE, the join includes that row in the output.

Definitions:

• column_name_list: A list of columns to include in the join condition.
• column_name: The column that exists in both of the tables that you are joining.

Details:

A NULL join condition evaluation is equivalent to a FALSE evaluation.

If a column-order sensitive operation such as UNION or SELECT * is used with the USING join condition, the resulting table contains columns in this order:

• The columns from column_name_list in the order they appear in the USING clause.
• All other columns of the left input in the order they appear in the input.
• All other columns of the right input in the order they appear in the input.

A column name in the USING clause must not be qualified by a table name.

If the join is an INNER JOIN or a LEFT OUTER JOIN, the output columns are populated from the values in the first table. If the join is a RIGHT OUTER JOIN, the output columns are populated from the values in the second table. If the join is a FULL OUTER JOIN, the output columns are populated by coalescing the values from the left and right tables in that order.

Examples

The following example shows how to use the USING clause with one column name in the column name list:

WITH
  A AS ( SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 9 UNION ALL SELECT NULL),
  B AS ( SELECT 2 as x UNION ALL SELECT 9 UNION ALL SELECT 9 UNION ALL SELECT 5)
SELECT * FROM A INNER JOIN B USING (x);

/*
Table A    Table B   Result
+------+   +---+     +---+
| x    | * | x |  =  | x |
+------+   +---+     +---+
| 1    |   | 2 |     | 2 |
| 2    |   | 9 |     | 9 |
| 9    |   | 9 |     | 9 |
| NULL |   | 5 |     +---+
+------+   +---+
*/

The following example shows how to use the USING clause with multiple column names in the column name list:

WITH
  A AS (
    SELECT 1 as x, 15 as y UNION ALL
    SELECT 2, 10 UNION ALL
    SELECT 9, 16 UNION ALL
    SELECT NULL, 12),
  B AS (
    SELECT 2 as x, 10 as y UNION ALL
    SELECT 9, 17 UNION ALL
    SELECT 9, 16 UNION ALL
    SELECT 5, 15)
SELECT * FROM A INNER JOIN B USING (x, y);

/*
Table A         Table B        Result
+-----------+   +---------+     +---------+
| x    | y  | * | x  | y  |  =  | x  | y  |
+-----------+   +---------+     +---------+
| 1    | 15 |   | 2  | 10 |     | 2  | 10 |
| 2    | 10 |   | 9  | 17 |     | 9  | 16 |
| 9    | 16 |   | 9  | 16 |     +---------+
| NULL | 12 |   | 5  | 15 |
+-----------+   +---------+
*/

The following examples show additional ways in which to use the USING clause with one column name in the column name list:

WITH
  A AS ( SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 9 UNION ALL SELECT NULL),
  B AS ( SELECT 2 as x UNION ALL SELECT 9 UNION ALL SELECT 9 UNION ALL SELECT 5)
SELECT x, A.x, B.x FROM A INNER JOIN B USING (x)

/*
Table A    Table B   Result
+------+   +---+     +--------------------+
| x    | * | x |  =  | x    | A.x  | B.x  |
+------+   +---+     +--------------------+
| 1    |   | 2 |     | 2    | 2    | 2    |
| 2    |   | 9 |     | 9    | 9    | 9    |
| 9    |   | 9 |     | 9    | 9    | 9    |
| NULL |   | 5 |     +--------------------+
+------+   +---+
*/

WITH
  A AS ( SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 9 UNION ALL SELECT NULL),
  B AS ( SELECT 2 as x UNION ALL SELECT 9 UNION ALL SELECT 9 UNION ALL SELECT 5)
SELECT x, A.x, B.x FROM A LEFT OUTER JOIN B USING (x)

/*
Table A    Table B   Result
+------+   +---+     +--------------------+
| x    | * | x |  =  | x    | A.x  | B.x  |
+------+   +---+     +--------------------+
| 1    |   | 2 |     | 1    | 1    | NULL |
| 2    |   | 9 |     | 2    | 2    | 2    |
| 9    |   | 9 |     | 9    | 9    | 9    |
| NULL |   | 5 |     | 9    | 9    | 9    |
+------+   +---+     | NULL | NULL | NULL |
                     +--------------------+
*/

WITH
  A AS ( SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT NULL),
  B AS ( SELECT 2 as x UNION ALL SELECT 9 UNION ALL SELECT 9 UNION ALL SELECT 5)
SELECT x, A.x, B.x FROM A RIGHT OUTER JOIN B USING (x)

/*
Table A    Table B   Result
+------+   +---+     +--------------------+
| x    | * | x |  =  | x    | A.x  | B.x  |
+------+   +---+     +--------------------+
| 1    |   | 2 |     | 2    | 2    | 2    |
| 2    |   | 9 |     | 2    | 2    | 2    |
| 2    |   | 9 |     | 9    | NULL | 9    |
| NULL |   | 5 |     | 9    | NULL | 9    |
+------+   +---+     | 5    | NULL | 5    |
                     +--------------------+
*/

WITH
  A AS ( SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT NULL),
  B AS ( SELECT 2 as x UNION ALL SELECT 9 UNION ALL SELECT 9 UNION ALL SELECT 5)
SELECT x, A.x, B.x FROM A FULL OUTER JOIN B USING (x);

/*
Table A    Table B   Result
+------+   +---+     +--------------------+
| x    | * | x |  =  | x    | A.x  | B.x  |
+------+   +---+     +--------------------+
| 1    |   | 2 |     | 1    | 1    | NULL |
| 2    |   | 9 |     | 2    | 2    | 2    |
| 2    |   | 9 |     | 2    | 2    | 2    |
| NULL |   | 5 |     | NULL | NULL | NULL |
+------+   +---+     | 9    | NULL | 9    |
                     | 9    | NULL | 9    |
                     | 5    | NULL | 5    |
                     +--------------------+
*/

The following example shows how to use the USING clause with only some column names in the column name list.

WITH
  A AS (
    SELECT 1 as x, 15 as y UNION ALL
    SELECT 2, 10 UNION ALL
    SELECT 9, 16 UNION ALL
    SELECT NULL, 12),
  B AS (
    SELECT 2 as x, 10 as y UNION ALL
    SELECT 9, 17 UNION ALL
    SELECT 9, 16 UNION ALL
    SELECT 5, 15)
SELECT * FROM A INNER JOIN B USING (x);

/*
Table A         Table B         Result
+-----------+   +---------+     +-----------------+
| x    | y  | * | x  | y  |  =  | x   | A.y | B.y |
+-----------+   +---------+     +-----------------+
| 1    | 15 |   | 2  | 10 |     | 2   | 10  | 10  |
| 2    | 10 |   | 9  | 17 |     | 9   | 16  | 17  |
| 9    | 16 |   | 9  | 16 |     | 9   | 16  | 16  |
| NULL | 12 |   | 5  | 15 |     +-----------------+
+-----------+   +---------+
*/

The following query performs an INNER JOIN on the Roster and TeamMascot table. The query returns the rows from Roster and TeamMascot where Roster.SchoolID is the same as TeamMascot.SchoolID. The results include a single SchoolID column.

SELECT * FROM Roster INNER JOIN TeamMascot USING (SchoolID);

/*----------------------------------------+
 | SchoolID   | LastName   | Mascot       |
 +----------------------------------------+
 | 50         | Adams      | Jaguars      |
 | 52         | Buchanan   | Lakers       |
 | 52         | Coolidge   | Lakers       |
 | 51         | Davis      | Knights      |
 +----------------------------------------*/

ON and USING equivalency

The ON and USING join conditions aren't equivalent, but they share some rules and sometimes they can produce similar results.

In the following examples, observe what is returned when all rows are produced for inner and outer joins. Also, look at how each join condition handles NULL values.

WITH
  A AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3),
  B AS (SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4)
SELECT * FROM A INNER JOIN B ON A.x = B.x;

WITH
  A AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3),
  B AS (SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4)
SELECT * FROM A INNER JOIN B USING (x);

/*
Table A   Table B   Result ON     Result USING
+---+     +---+     +-------+     +---+
| x |  *  | x |  =  | x | x |     | x |
+---+     +---+     +-------+     +---+
| 1 |     | 2 |     | 2 | 2 |     | 2 |
| 2 |     | 3 |     | 3 | 3 |     | 3 |
| 3 |     | 4 |     +-------+     +---+
+---+     +---+
*/

WITH
  A AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT NULL),
  B AS (SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5)
SELECT * FROM A LEFT OUTER JOIN B ON A.x = B.x;

WITH
  A AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT NULL),
  B AS (SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5)
SELECT * FROM A LEFT OUTER JOIN B USING (x);

/*
Table A    Table B   Result ON           Result USING
+------+   +---+     +-------------+     +------+
| x    | * | x |  =  | x    | x    |     | x    |
+------+   +---+     +-------------+     +------+
| 1    |   | 2 |     | 1    | NULL |     | 1    |
| 2    |   | 3 |     | 2    | 2    |     | 2    |
| 3    |   | 4 |     | 3    | 3    |     | 3    |
| NULL |   | 5 |     | NULL | NULL |     | NULL |
+------+   +---+     +-------------+     +------+
*/

WITH
  A AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3),
  B AS (SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4)
SELECT * FROM A FULL OUTER JOIN B ON A.x = B.x;

WITH
  A AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3),
  B AS (SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4)
SELECT * FROM A FULL OUTER JOIN B USING (x);

/*
Table A   Table B   Result ON           Result USING
+---+     +---+     +-------------+     +---+
| x |  *  | x |  =  | x    | x    |     | x |
+---+     +---+     +-------------+     +---+
| 1 |     | 2 |     | 1    | NULL |     | 1 |
| 2 |     | 3 |     | 2    | 2    |     | 2 |
| 3 |     | 4 |     | 3    | 3    |     | 3 |
+---+     +---+     | NULL | 4    |     | 4 |
                    +-------------+     +---+
*/

Although ON and USING aren't equivalent, they can return the same results in some situations if you specify the columns you want to return.

In the following examples, observe what is returned when a specific row is produced for inner and outer joins. Also, look at how each join condition handles NULL values.

WITH
  A AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT NULL),
  B AS (SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5)
SELECT A.x FROM A INNER JOIN B ON A.x = B.x;

WITH
  A AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT NULL),
  B AS (SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5)
SELECT x FROM A INNER JOIN B USING (x);

/*
Table A    Table B   Result ON     Result USING
+------+   +---+     +---+         +---+
| x    | * | x |  =  | x |         | x |
+------+   +---+     +---+         +---+
| 1    |   | 2 |     | 2 |         | 2 |
| 2    |   | 3 |     | 3 |         | 3 |
| 3    |   | 4 |     +---+         +---+
| NULL |   | 5 |
+------+   +---+
*/

WITH
  A AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT NULL),
  B AS (SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5)
SELECT A.x FROM A LEFT OUTER JOIN B ON A.x = B.x;

WITH
  A AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT NULL),
  B AS (SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5)
SELECT x FROM A LEFT OUTER JOIN B USING (x);

/*
Table A    Table B   Result ON    Result USING
+------+   +---+     +------+     +------+
| x    | * | x |  =  | x    |     | x    |
+------+   +---+     +------+     +------+
| 1    |   | 2 |     | 1    |     | 1    |
| 2    |   | 3 |     | 2    |     | 2    |
| 3    |   | 4 |     | 3    |     | 3    |
| NULL |   | 5 |     | NULL |     | NULL |
+------+   +---+     +------+     +------+
*/

WITH
  A AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT NULL),
  B AS (SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5)
SELECT A.x FROM A FULL OUTER JOIN B ON A.x = B.x;

WITH
  A AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT NULL),
  B AS (SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5)
SELECT x FROM A FULL OUTER JOIN B USING (x);

/*
Table A    Table B   Result ON    Result USING
+------+   +---+     +------+     +------+
| x    | * | x |  =  | x    |     | x    |
+------+   +---+     +------+     +------+
| 1    |   | 2 |     | 1    |     | 1    |
| 2    |   | 3 |     | 2    |     | 2    |
| 3    |   | 4 |     | 3    |     | 3    |
| NULL |   | 5 |     | NULL |     | NULL |
+------+   +---+     | NULL |     | 4    |
                     | NULL |     | 5    |
                     +------+     +------+
*/

WITH
  A AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT NULL),
  B AS (SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5)
SELECT B.x FROM A FULL OUTER JOIN B ON A.x = B.x;

WITH
  A AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT NULL),
  B AS (SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5)
SELECT x FROM A FULL OUTER JOIN B USING (x);

/*
Table A    Table B   Result ON    Result USING
+------+   +---+     +------+     +------+
| x    | * | x |  =  | x    |     | x    |
+------+   +---+     +------+     +------+
| 1    |   | 2 |     | 2    |     | 1    |
| 2    |   | 3 |     | 3    |     | 2    |
| 3    |   | 4 |     | NULL |     | 3    |
| NULL |   | 5 |     | NULL |     | NULL |
+------+   +---+     | 4    |     | 4    |
                     | 5    |     | 5    |
                     +------+     +------+
*/

In the following example, observe what is returned when COALESCE is used with the ON clause. It provides the same results as a query with the USING clause.

WITH
  A AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT NULL),
  B AS (SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5)
SELECT COALESCE(A.x, B.x) FROM A FULL OUTER JOIN B ON A.x = B.x;

WITH
  A AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT NULL),
  B AS (SELECT 2 as x UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5)
SELECT x FROM A FULL OUTER JOIN B USING (x);

/*
Table A    Table B   Result ON    Result USING
+------+   +---+     +------+     +------+
| x    | * | x |  =  | x    |     | x    |
+------+   +---+     +------+     +------+
| 1    |   | 2 |     | 1    |     | 1    |
| 2    |   | 3 |     | 2    |     | 2    |
| 3    |   | 4 |     | 3    |     | 3    |
| NULL |   | 5 |     | NULL |     | NULL |
+------+   +---+     | 4    |     | 4    |
                     | 5    |     | 5    |
                     +------+     +------+
*/

Join operations in a sequence

The FROM clause can contain multiple JOIN operations in a sequence. JOINs are bound from left to right. For example:

FROM A JOIN B USING (x) JOIN C USING (x)

-- A JOIN B USING (x)        = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2                  = return value

You can also insert parentheses to group JOINs:

FROM ( (A JOIN B USING (x)) JOIN C USING (x) )

-- A JOIN B USING (x)        = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2                  = return value

With parentheses, you can group JOINs so that they are bound in a different order:

FROM ( A JOIN (B JOIN C USING (x)) USING (x) )

-- B JOIN C USING (x)       = result_1
-- A JOIN result_1          = result_2
-- result_2                 = return value

When comma cross joins are present in a query with a sequence of JOINs, they group from left to right like other JOIN types:

FROM A JOIN B USING (x) JOIN C USING (x), D

-- A JOIN B USING (x)        = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2 CROSS JOIN D     = return value

There can't be a RIGHT JOIN or FULL JOIN after a comma cross join unless it's parenthesized:

FROM A, B RIGHT JOIN C ON TRUE // INVALID

FROM A, B FULL JOIN C ON TRUE  // INVALID

FROM A, B JOIN C ON TRUE       // VALID

FROM A, (B RIGHT JOIN C ON TRUE) // VALID

FROM A, (B FULL JOIN C ON TRUE)  // VALID

Correlated join operation

A join operation is correlated when the right from_item contains a reference to at least one range variable or column name introduced by the left from_item.

In a correlated join operation, rows from the right from_item are determined by a row from the left from_item. Consequently, RIGHT OUTER and FULL OUTER joins can't be correlated because right from_item rows can't be determined in the case when there is no row from the left from_item.

All correlated join operations must reference an array in the right from_item.

This is another conceptual example of a correlated join operation. array_of_IDs is part of the left from_item but is referenced in the right from_item.

FROM A JOIN UNNEST(A.array_of_IDs) AS C

The UNNEST operator can be explicit or implicit. These are both allowed:

FROM A JOIN UNNEST(A.array_of_IDs) AS IDs

FROM A JOIN A.array_of_IDs AS IDs

In a correlated join operation, the right from_item is re-evaluated against each distinct row from the left from_item.

Caveats

• In a correlated LEFT JOIN, when the input table on the right side is empty for some row from the left side, it's as if no rows from the right side satisfied the join condition in a regular LEFT JOIN. When there are no joining rows, a row with NULL values for all columns on the right side is generated to join with the row from the left side.
• In a correlated CROSS JOIN, when the input table on the right side is empty for some row from the left side, it's as if no rows from the right side satisfied the join condition in a regular correlated INNER JOIN. This means that the row is dropped from the results.

Examples

A common pattern for a correlated LEFT JOIN is to have an UNNEST operation on the right side that references an array from some column introduced by input on the left side. For rows where that array is empty or NULL, the UNNEST operation produces no rows on the right input. In that case, a row with a NULL entry in each column of the right input is created to join with the row from the left input. For example:

SELECT A.name, item, ARRAY_LENGTH(A.items) item_count_for_name
FROM
  UNNEST(
    [
      STRUCT(
        'first' AS name,
        [1, 2, 3, 4] AS items),
      STRUCT(
        'second' AS name,
        [] AS items)]) AS A
LEFT JOIN
  A.items AS item;

/*--------+------+---------------------+
 | name   | item | item_count_for_name |
 +--------+------+---------------------+
 | first  | 1    | 4                   |
 | first  | 2    | 4                   |
 | first  | 3    | 4                   |
 | first  | 4    | 4                   |
 | second | NULL | 0                   |
 +--------+------+---------------------*/

In the case of a correlated INNER JOIN or CROSS JOIN, when the input on the right side is empty for some row from the left side, the final row is dropped from the results. For example:

SELECT A.name, item
FROM
  UNNEST(
    [
      STRUCT(
        'first' AS name,
        [1, 2, 3, 4] AS items),
      STRUCT(
        'second' AS name,
        [] AS items)]) AS A
INNER JOIN
  A.items AS item;

/*-------+------+
 | name  | item |
 +-------+------+
 | first | 1    |
 | first | 2    |
 | first | 3    |
 | first | 4    |
 +-------+------*/

WHERE clause

WHERE bool_expression

The WHERE clause filters the results of the FROM clause.

Only rows whose bool_expression evaluates to TRUE are included. Rows whose bool_expression evaluates to NULL or FALSE are discarded.

The evaluation of a query with a WHERE clause is typically completed in this order:

• FROM
• WHERE
• GROUP BY and aggregation
• HAVING
• WINDOW
• QUALIFY
• DISTINCT
• ORDER BY
• LIMIT

Evaluation order doesn't always match syntax order.

The WHERE clause only references columns available via the FROM clause; it can't reference SELECT list aliases.

Examples

This query returns returns all rows from the Roster table where the SchoolID column has the value 52:

SELECT * FROM Roster
WHERE SchoolID = 52;

The bool_expression can contain multiple sub-conditions:

SELECT * FROM Roster
WHERE STARTS_WITH(LastName, "Mc") OR STARTS_WITH(LastName, "Mac");

Expressions in an INNER JOIN have an equivalent expression in the WHERE clause. For example, a query using INNER JOIN and ON has an equivalent expression using CROSS JOIN and WHERE. For example, the following two queries are equivalent:

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster INNER JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster CROSS JOIN TeamMascot
WHERE Roster.SchoolID = TeamMascot.SchoolID;

GROUP BY clause

GROUP BY groupable_items

Description

The GROUP BY clause groups together rows in a table that share common values for certain columns. For a group of rows in the source table with non-distinct values, the GROUP BY clause aggregates them into a single combined row. This clause is commonly used when aggregate functions are present in the SELECT list, or to eliminate redundancy in the output.

Definitions

• groupable_items: Group rows in a table that share common values for certain columns. To learn more, see Group rows by groupable items.
• ALL: Automatically group rows. To learn more, see Group rows automatically.

Group rows by groupable items

GROUP BY groupable_item[, ...]

groupable_item:
  {
    value
    | value_alias
    | column_ordinal
  }

Description

The GROUP BY clause can include groupable expressions and their ordinals.

Definitions

• value: An expression that represents a non-distinct, groupable value. To learn more, see Group rows by values.
• value_alias: An alias for value. To learn more, see Group rows by values.
• column_ordinal: An INT64 value that represents the ordinal assigned to a groupable expression in the SELECT list. To learn more, see Group rows by column ordinals.

Group rows by values

The GROUP BY clause can group rows in a table with non-distinct values in the GROUP BY clause. For example:

WITH PlayerStats AS (
  SELECT 'Adams' as LastName, 'Noam' as FirstName, 3 as PointsScored UNION ALL
  SELECT 'Buchanan', 'Jie', 0 UNION ALL
  SELECT 'Coolidge', 'Kiran', 1 UNION ALL
  SELECT 'Adams', 'Noam', 4 UNION ALL
  SELECT 'Buchanan', 'Jie', 13)
SELECT SUM(PointsScored) AS total_points, LastName
FROM PlayerStats
GROUP BY LastName;

/*--------------+----------+
 | total_points | LastName |
 +--------------+----------+
 | 7            | Adams    |
 | 13           | Buchanan |
 | 1            | Coolidge |
 +--------------+----------*/

GROUP BY clauses may also refer to aliases. If a query contains aliases in the SELECT clause, those aliases override names in the corresponding FROM clause. For example:

WITH PlayerStats AS (
  SELECT 'Adams' as LastName, 'Noam' as FirstName, 3 as PointsScored UNION ALL
  SELECT 'Buchanan', 'Jie', 0 UNION ALL
  SELECT 'Coolidge', 'Kiran', 1 UNION ALL
  SELECT 'Adams', 'Noam', 4 UNION ALL
  SELECT 'Buchanan', 'Jie', 13)
SELECT SUM(PointsScored) AS total_points, LastName AS last_name
FROM PlayerStats
GROUP BY last_name;

/*--------------+-----------+
 | total_points | last_name |
 +--------------+-----------+
 | 7            | Adams     |
 | 13           | Buchanan  |
 | 1            | Coolidge  |
 +--------------+-----------*/

You can use the GROUP BY clause with arrays. The following query executes because the array elements being grouped are the same length and group type:

WITH PlayerStats AS (
  SELECT ['Coolidge', 'Adams'] as Name, 3 as PointsScored UNION ALL
  SELECT ['Adams', 'Buchanan'], 0 UNION ALL
  SELECT ['Coolidge', 'Adams'], 1 UNION ALL
  SELECT ['Kiran', 'Noam'], 1)
SELECT SUM(PointsScored) AS total_points, name
FROM PlayerStats
GROUP BY Name;

/*--------------+------------------+
 | total_points | name             |
 +--------------+------------------+
 | 4            | [Coolidge,Adams] |
 | 0            | [Adams,Buchanan] |
 | 1            | [Kiran,Noam]     |
 +--------------+------------------*/

You can use the GROUP BY clause with structs. The following query executes because the struct fields being grouped have the same group types:

WITH
  TeamStats AS (
    SELECT
      ARRAY<STRUCT<last_name STRING, first_name STRING, age INT64>>[
        ('Adams', 'Noam', 20), ('Buchanan', 'Jie', 19)] AS Team,
      3 AS PointsScored
    UNION ALL
    SELECT [('Coolidge', 'Kiran', 21), ('Yang', 'Jason', 22)], 4
    UNION ALL
    SELECT [('Adams', 'Noam', 20), ('Buchanan', 'Jie', 19)], 10
    UNION ALL
    SELECT [('Coolidge', 'Kiran', 21), ('Yang', 'Jason', 22)], 7
  )
SELECT
  SUM(PointsScored) AS total_points,
  Team
FROM TeamStats
GROUP BY Team;

/*--------------+--------------------------+
 | total_points | teams                    |
 +--------------+--------------------------+
 | 13           | [{                       |
 |              |    last_name: "Adams",   |
 |              |    first_name: "Noam",   |
 |              |    age: 20               |
 |              |  },{                     |
 |              |    last_name: "Buchanan",|
 |              |    first_name: "Jie",    |
 |              |    age: 19               |
 |              |  }]                      |
 +-----------------------------------------+
 | 11           | [{                       |
 |              |    last_name: "Coolidge",|
 |              |    first_name: "Kiran",  |
 |              |    age: 21               |
 |              |  },{                     |
 |              |    last_name: "Yang",    |
 |              |    first_name: "Jason",  |
 |              |    age: 22               |
 |              |  }]                      |
 +--------------+--------------------------*/

To learn more about the data types that are supported for values in the GROUP BY clause, see Groupable data types.

Group rows by column ordinals

The GROUP BY clause can refer to expression names in the SELECT list. The GROUP BY clause also allows ordinal references to expressions in the SELECT list, using integer values. 1 refers to the first value in the SELECT list, 2 the second, and so forth. The value list can combine ordinals and value names. The following queries are equivalent:

WITH PlayerStats AS (
  SELECT 'Adams' as LastName, 'Noam' as FirstName, 3 as PointsScored UNION ALL
  SELECT 'Buchanan', 'Jie', 0 UNION ALL
  SELECT 'Coolidge', 'Kiran', 1 UNION ALL
  SELECT 'Adams', 'Noam', 4 UNION ALL
  SELECT 'Buchanan', 'Jie', 13)
SELECT SUM(PointsScored) AS total_points, LastName, FirstName
FROM PlayerStats
GROUP BY LastName, FirstName;

/*--------------+----------+-----------+
 | total_points | LastName | FirstName |
 +--------------+----------+-----------+
 | 7            | Adams    | Noam      |
 | 13           | Buchanan | Jie       |
 | 1            | Coolidge | Kiran     |
 +--------------+----------+-----------*/

WITH PlayerStats AS (
  SELECT 'Adams' as LastName, 'Noam' as FirstName, 3 as PointsScored UNION ALL
  SELECT 'Buchanan', 'Jie', 0 UNION ALL
  SELECT 'Coolidge', 'Kiran', 1 UNION ALL
  SELECT 'Adams', 'Noam', 4 UNION ALL
  SELECT 'Buchanan', 'Jie', 13)
SELECT SUM(PointsScored) AS total_points, LastName, FirstName
FROM PlayerStats
GROUP BY 2, 3;

/*--------------+----------+-----------+
 | total_points | LastName | FirstName |
 +--------------+----------+-----------+
 | 7            | Adams    | Noam      |
 | 13           | Buchanan | Jie       |
 | 1            | Coolidge | Kiran     |
 +--------------+----------+-----------*/

Group rows by ALL

GROUP BY ALL

Description

The GROUP BY ALL clause groups rows by inferring grouping keys from the SELECT items.

The following SELECT items are excluded from the GROUP BY ALL clause:

• Expressions that include aggregate functions.
• Expressions that include window functions.
• Expressions that don't reference a name from the FROM clause. This includes:
  • Constants
  • Query parameters
  • Correlated column references
  • Expressions that only reference GROUP BY keys inferred from other SELECT items.

After exclusions are applied, an error is produced if any remaining SELECT item includes a volatile function or has a non-groupable type.

If the set of inferred grouping keys is empty after exclusions are applied, all input rows are considered a single group for aggregation.

Examples

In the following example, the query groups rows by first_name and last_name. total_points is excluded because it represents an aggregate function.

WITH PlayerStats AS (
  SELECT 'Adams' as LastName, 'Noam' as FirstName, 3 as PointsScored UNION ALL
  SELECT 'Buchanan', 'Jie', 0 UNION ALL
  SELECT 'Coolidge', 'Kiran', 1 UNION ALL
  SELECT 'Adams', 'Noam', 4 UNION ALL
  SELECT 'Buchanan', 'Jie', 13)
SELECT
  SUM(PointsScored) AS total_points,
  FirstName AS first_name,
  LastName AS last_name
FROM PlayerStats
GROUP BY ALL;

/*--------------+------------+-----------+
 | total_points | first_name | last_name |
 +--------------+------------+-----------+
 | 7            | Noam       | Adams     |
 | 13           | Jie        | Buchanan  |
 | 1            | Kiran      | Coolidge  |
 +--------------+------------+-----------*/

If the select list contains an analytic function, the query groups rows by first_name and last_name. total_people is excluded because it contains a window function.

WITH PlayerStats AS (
  SELECT 'Adams' as LastName, 'Noam' as FirstName, 3 as PointsScored UNION ALL
  SELECT 'Buchanan', 'Jie', 0 UNION ALL
  SELECT 'Coolidge', 'Kiran', 1 UNION ALL
  SELECT 'Adams', 'Noam', 4 UNION ALL
  SELECT 'Buchanan', 'Jie', 13)
SELECT
  COUNT(*) OVER () AS total_people,
  FirstName AS first_name,
  LastName AS last_name
FROM PlayerStats
GROUP BY ALL;

/*--------------+------------+-----------+
 | total_people | first_name | last_name |
 +--------------+------------+-----------+
 | 3            | Noam       | Adams     |
 | 3            | Jie        | Buchanan  |
 | 3            | Kiran      | Coolidge  |
 +--------------+------------+-----------*/

If multiple SELECT items reference the same FROM item, and any of them is a path expression prefix of another, only the prefix path is used for grouping. In the following example, coordinates is excluded because x_coordinate and y_coordinate have already referenced Values.x and Values.y in the FROM clause, and they are prefixes of the path expression used in x_coordinate:

WITH Values AS (
  SELECT 1 AS x, 2 AS y
  UNION ALL SELECT 1 AS x, 4 AS y
  UNION ALL SELECT 2 AS x, 5 AS y
)
SELECT
  Values.x AS x_coordinate,
  Values.y AS y_coordinate,
  [Values.x, Values.y] AS coordinates
FROM Values
GROUP BY ALL

/*--------------+--------------+-------------+
 | x_coordinate | y_coordinate | coordinates |
 +--------------+--------------+-------------+
 | 1            | 4            | [1, 4]      |
 | 1            | 2            | [1, 2]      |
 | 2            | 5            | [2, 5]      |
 +--------------+--------------+-------------*/

In the following example, the inferred set of grouping keys is empty. The query returns one row even when the input contains zero rows.

SELECT COUNT(*) AS num_rows
FROM UNNEST([])
GROUP BY ALL

/*----------+
 | num_rows |
 +----------+
 | 0        |
 +----------*/

HAVING clause

HAVING bool_expression

The HAVING clause filters the results produced by GROUP BY or aggregation. GROUP BY or aggregation must be present in the query. If aggregation is present, the HAVING clause is evaluated once for every aggregated row in the result set.

Only rows whose bool_expression evaluates to TRUE are included. Rows whose bool_expression evaluates to NULL or FALSE are discarded.

The evaluation of a query with a HAVING clause is typically completed in this order:

• FROM
• WHERE
• GROUP BY and aggregation
• HAVING
• WINDOW
• QUALIFY
• DISTINCT
• ORDER BY
• LIMIT

Evaluation order doesn't always match syntax order.

The HAVING clause references columns available via the FROM clause, as well as SELECT list aliases. Expressions referenced in the HAVING clause must either appear in the GROUP BY clause or they must be the result of an aggregate function:

SELECT LastName
FROM Roster
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

If a query contains aliases in the SELECT clause, those aliases override names in a FROM clause.

SELECT LastName, SUM(PointsScored) AS ps
FROM Roster
GROUP BY LastName
HAVING ps > 0;

Mandatory aggregation

Aggregation doesn't have to be present in the HAVING clause itself, but aggregation must be present in at least one of the following forms:

Aggregation function in the SELECT list.

SELECT LastName, SUM(PointsScored) AS total
FROM PlayerStats
GROUP BY LastName
HAVING total > 15;

Aggregation function in the HAVING clause.

SELECT LastName
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

Aggregation in both the SELECT list and HAVING clause.

When aggregation functions are present in both the SELECT list and HAVING clause, the aggregation functions and the columns they reference don't need to be the same. In the example below, the two aggregation functions, COUNT() and SUM(), are different and also use different columns.

SELECT LastName, COUNT(*)
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

ORDER BY clause

ORDER BY expression
  [{ ASC | DESC }]
  [{ NULLS FIRST | NULLS LAST }]
  [, ...]

The ORDER BY clause specifies a column or expression as the sort criterion for the result set. If an ORDER BY clause isn't present, the order of the results of a query isn't defined. Column aliases from a FROM clause or SELECT list are allowed. If a query contains aliases in the SELECT clause, those aliases override names in the corresponding FROM clause. The data type of expression must be orderable.

When using the ORDER BY clause with either recursive or non-recursive common table expressions (CTEs), specify the ORDER BY clause within the main query definition, and not in the CTE definition itself. This evenly distributes the execution of your queries as tables grow over time and prevents a single processing unit from having to process all query data.

Optional Clauses

• NULLS FIRST | NULLS LAST:
  • NULLS FIRST: Sort null values before non-null values.
  • NULLS LAST. Sort null values after non-null values.
• ASC | DESC: Sort the results in ascending or descending order of expression values. ASC is the default value. If null ordering isn't specified with NULLS FIRST or NULLS LAST:
  • NULLS FIRST is applied by default if the sort order is ascending.
  • NULLS LAST is applied by default if the sort order is descending.

Examples

Use the default sort order (ascending).

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true UNION ALL
      SELECT NULL, false)
ORDER BY x;

/*------+-------+
 | x    | y     |
 +------+-------+
 | NULL | false |
 | 1    | true  |
 | 9    | true  |
 +------+-------*/

Use the default sort order (ascending), but return null values last.

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true UNION ALL
      SELECT NULL, false)
ORDER BY x NULLS LAST;

/*------+-------+
 | x    | y     |
 +------+-------+
 | 1    | true  |
 | 9    | true  |
 | NULL | false |
 +------+-------*/

Use descending sort order.

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true UNION ALL
      SELECT NULL, false)
ORDER BY x DESC;

/*------+-------+
 | x    | y     |
 +------+-------+
 | 9    | true  |
 | 1    | true  |
 | NULL | false |
 +------+-------*/

Use descending sort order, but return null values first.

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true UNION ALL
      SELECT NULL, false)
ORDER BY x DESC NULLS FIRST;

/*------+-------+
 | x    | y     |
 +------+-------+
 | NULL | false |
 | 9    | true  |
 | 1    | true  |
 +------+-------*/

It's possible to order by multiple columns. In the example below, the result set is ordered first by SchoolID and then by LastName:

SELECT LastName, PointsScored, OpponentID
FROM PlayerStats
ORDER BY SchoolID, LastName;

When used in conjunction with set operators, the ORDER BY clause applies to the result set of the entire query; it doesn't apply only to the closest SELECT statement. For this reason, it can be helpful (though it isn't required) to use parentheses to show the scope of the ORDER BY.

This query without parentheses:

SELECT * FROM Roster
UNION ALL
SELECT * FROM TeamMascot
ORDER BY SchoolID;

is equivalent to this query with parentheses:

( SELECT * FROM Roster
  UNION ALL
  SELECT * FROM TeamMascot )
ORDER BY SchoolID;

but isn't equivalent to this query, where the ORDER BY clause applies only to the second SELECT statement:

SELECT * FROM Roster
UNION ALL
( SELECT * FROM TeamMascot
  ORDER BY SchoolID );

You can also use integer literals as column references in ORDER BY clauses. An integer literal becomes an ordinal (for example, counting starts at 1) into the SELECT list.

Example - the following two queries are equivalent:

SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY LastName
ORDER BY LastName;

SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY 2
ORDER BY 2;

QUALIFY clause

QUALIFY bool_expression

The QUALIFY clause filters the results of window functions. A window function is required to be present in the QUALIFY clause or the SELECT list.

Only rows whose bool_expression evaluates to TRUE are included. Rows whose bool_expression evaluates to NULL or FALSE are discarded.

The evaluation of a query with a QUALIFY clause is typically completed in this order:

• FROM
• WHERE
• GROUP BY and aggregation
• HAVING
• WINDOW
• QUALIFY
• DISTINCT
• ORDER BY
• LIMIT

Evaluation order doesn't always match syntax order.

Examples

The following query returns the most popular vegetables in the Produce table and their rank.

SELECT
  item,
  RANK() OVER (PARTITION BY category ORDER BY purchases DESC) as rank
FROM Produce
WHERE Produce.category = 'vegetable'
QUALIFY rank <= 3

/*---------+------+
 | item    | rank |
 +---------+------+
 | kale    | 1    |
 | lettuce | 2    |
 | cabbage | 3    |
 +---------+------*/

You don't have to include a window function in the SELECT list to use QUALIFY. The following query returns the most popular vegetables in the Produce table.

SELECT item
FROM Produce
WHERE Produce.category = 'vegetable'
QUALIFY RANK() OVER (PARTITION BY category ORDER BY purchases DESC) <= 3

/*---------+
 | item    |
 +---------+
 | kale    |
 | lettuce |
 | cabbage |
 +---------*/

WINDOW clause

WINDOW named_window_expression [, ...]

named_window_expression:
  named_window AS { named_window | ( [ window_specification ] ) }

A WINDOW clause defines a list of named windows. A named window represents a group of rows in a table upon which to use a window function. A named window can be defined with a window specification or reference another named window. If another named window is referenced, the definition of the referenced window must precede the referencing window.

Examples

These examples reference a table called Produce. They all return the same result. Note the different ways you can combine named windows and use them in a window function's OVER clause.

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window) AS most_popular
FROM Produce
WINDOW item_window AS (
  PARTITION BY category
  ORDER BY purchases
  ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (d) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  c AS (b ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING),
  d AS (c)

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (c ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  c AS b

Set operators

  query_expr
  [ { FULL | LEFT } [ OUTER ] ]
  {
    UNION { ALL | DISTINCT } |
    INTERSECT DISTINCT |
    EXCEPT DISTINCT
  }
  [ [ STRICT ] CORRESPONDING [ BY (column_list) ] ]
  query_expr

Set operators combine or filter results from two or more input queries into a single result set.

Definitions

• query_expr: One of two input queries whose results are combined or filtered into a single result set.
• UNION: Returns the combined results of the left and right input queries. Values in columns that are matched by position are concatenated vertically.
• INTERSECT: Returns rows that are found in the results of both the left and right input queries.
• EXCEPT: Returns rows from the left input query that aren't present in the right input query.
• ALL: Executes the set operation on all rows. The ALL modifier is supported by only the UNION set operator.
• DISTINCT: Excludes duplicate rows in the set operation.
• CORRESPONDING: Matches columns by name instead of by position. For details, see CORRESPONDING.
• FULL | LEFT [OUTER], STRICT, BY: Adjust how the CORRESPONDING modifier behaves when the column names don't match exactly. For details, see CORRESPONDING.

Positional column matching

By default, columns are matched positionally and follow these rules. If the CORRESPONDING modifier is used, columns are matched by name, as described in the next section.

• Columns from input queries are matched by their position in the queries. That is, the first column in the first input query is paired with the first column in the second input query and so on.
• The input queries on each side of the operator must return the same number of columns.

Name-based column matching

To make set operations match columns by name instead of by column position, use the CORRESPONDING modifier.

With STRICT CORRESPONDING, the same column names must exist in each input, but they can be in different orders. Additional modifiers can be used to handle cases where the columns don't exactly match.

Example:

SELECT 1 AS one_digit, 10 AS two_digit
UNION ALL STRICT CORRESPONDING
SELECT 20 AS two_digit, 2 AS one_digit;

-- Column values match by name and not position in query.
/*-----------+-----------+
 | one_digit | two_digit |
 +-----------+-----------+
 | 1         | 10        |
 | 2         | 20        |
 +-----------+-----------*/

Other column-related rules

• For set operations other than UNION ALL, all column types must support equality comparison.
• The results of the set operation always use the column names from the first input query.
• The results of the set operation always use the supertypes of input types in corresponding columns, so paired columns must also have either the same data type or a common supertype.

Parenthesized set operators

• Parentheses must be used to separate different set operations. Set operations like UNION ALL and UNION DISTINCT are considered different.
• Parentheses are also used to group set operations and control order of operations. In EXCEPT set operations, for example, query results can vary depending on the operation grouping.

The following examples illustrate the use of parentheses with set operations:

-- Same set operations, no parentheses.
query1
UNION ALL
query2
UNION ALL
query3;

-- Different set operations, parentheses needed.
query1
UNION ALL
(
  query2
  UNION DISTINCT
  query3
);

-- Invalid
query1
UNION ALL
query2
UNION DISTINCT
query3;

-- Same set operations, no parentheses.
query1
EXCEPT DISTINCT
query2
EXCEPT DISTINCT
query3;

-- Equivalent query with optional parentheses, returns same results.
(
  query1
  EXCEPT DISTINCT
  query2
)
EXCEPT DISTINCT
query3;

-- Different execution order with a subquery, parentheses needed.
query1
EXCEPT DISTINCT
(
  query2
  EXCEPT DISTINCT
  query3
);

Set operator behavior with duplicate rows

Consider a given row R that appears exactly m times in the first input query and n times in the second input query, where m >= 0 and n >= 0:

• For UNION ALL, row R appears exactly m + n times in the result.
• For UNION DISTINCT, the DISTINCT is computed after the UNION is computed, so row R appears exactly one time.
• For INTERSECT DISTINCT, row R appears once in the output if m > 0 and n > 0.
• For EXCEPT DISTINCT, row R appears once in the output if m > 0 and n = 0.
• If more than two input queries are used, the above operations generalize and the output is the same as if the input queries were combined incrementally from left to right.

CORRESPONDING

Use the CORRESPONDING modifier with set operations to match columns by name instead of by position. You can use mode prefixes to adjust how the CORRESPONDING modifier behaves when the column names don't match exactly.

• CORRESPONDING: Matches columns by name instead of by position.
  • Columns that appear in both input queries are included in the query results and any other columns are excluded.
  • Input queries can't contain duplicate columns.
  • Input queries that produce [value tables][value-tables] aren't supported.
• FULL [OUTER]: Adjusts the CORRESPONDING modifier behavior so that all columns from both input queries are included in the query results, even if some columns aren't present in both queries.
  • Columns from the left input query are returned first, followed by unique columns from the right input query.
  • For columns in one input query that aren't present in the other query, a NULL value is added as its column value for the other query in the results.
• LEFT [OUTER]: Adjusts the CORRESPONDING modifier so that all columns from the left input query are included in the results, even if some columns in the left query aren't present in the right query.
  • For columns in the left query that aren't in the right query, a NULL value is added as its column value for the right query in the results.
  • At least one column name must be common in both left and right input queries.
• OUTER: If used alone, equivalent to FULL OUTER.
• STRICT: Adjusts the CORRESPONDING modifier behavior so that input queries must have the same set of column names, but column order can be different.
  • If a column in one input query doesn't appear in the other query, an error is raised.
  • STRICT mode can't be used with FULL | LEFT [OUTER] modes.
• BY (column_list): Used after the CORRESPONDING modifier to specify a comma-separated list of column names and the column order to return from the input queries.
  • If CORRESPONDING BY (column_list) is used alone without mode prefixes like FULL | LEFT [OUTER], then both the left and right input queries must contain all the columns in the column_list.
  • If any mode prefixes are used, then any column names not in the column_list are excluded from the results according to the mode used.

The following table shows the behavior of the mode prefixes for the CORRESPONDING modifier when left and right input columns don't match:

Mode prefix and modifier	Behavior when left and right input columns don't match
CORRESPONDING (no prefix)	Drop all unmatched columns in both inputs.
STRICT CORRESPONDING	Error, all columns must match in both inputs.
FULL [OUTER] CORRESPONDING	Include all columns from both inputs. For column values that exist in one input but not in another, add NULL values.
LEFT [OUTER] CORRESPONDING	Include all columns from the left input. For column values that exist in the left input but not in the right input, add NULL values. Drop any columns from the right input that don't exist in the left input.

For example set operations with modifiers, see the sections for each set operator, such as UNION.

UNION

The UNION operator returns the combined results of the left and right input queries. Columns are matched according to the rules described previously and rows are concatenated vertically.

Examples

SELECT * FROM UNNEST(ARRAY<INT64>[1, 2, 3]) AS number
UNION ALL
SELECT 1;

/*--------+
 | number |
 +--------+
 | 1      |
 | 2      |
 | 3      |
 | 1      |
 +--------*/

SELECT * FROM UNNEST(ARRAY<INT64>[1, 2, 3]) AS number
UNION DISTINCT
SELECT 1;

/*--------+
 | number |
 +--------+
 | 1      |
 | 2      |
 | 3      |
 +--------*/

The following example shows multiple chained operators:

SELECT * FROM UNNEST(ARRAY<INT64>[1, 2, 3]) AS number
UNION DISTINCT
SELECT 1
UNION DISTINCT
SELECT 2;

/*--------+
 | number |
 +--------+
 | 1      |
 | 2      |
 | 3      |
 +--------*/

The following example shows input queries with multiple columns. Both queries specify the same column names but in different orders. As a result, the column values are matched by column position in the input query and the column names are ignored.

SELECT 1 AS one_digit, 10 AS two_digit
UNION ALL
SELECT 20 AS two_digit, 2 AS one_digit;

-- Column values are matched by position and not column name.
/*-----------+-----------+
 | one_digit | two_digit |
 +-----------+-----------+
 | 1         | 10        |
 | 20        | 2         |
 +-----------+-----------*/

To resolve this ordering issue, the following example uses the STRICT CORRESPONDING modifier to match the columns by name instead of by position in the query results. The STRICT mode prefix ensures that all column names in both queries match, which is typically best practice.

SELECT 1 AS one_digit, 10 AS two_digit
UNION ALL STRICT CORRESPONDING
SELECT 20 AS two_digit, 2 AS one_digit;

/*-----------+-----------+
 | one_digit | two_digit |
 +-----------+-----------+
 | 1         | 10        |
 | 2         | 20        |
 +-----------+-----------*/

The following example adds a three_digit column to the left input query and a four_digit column to the right input query. Because these columns aren't present in both queries, the STRICT CORRESPONDING modifier would trigger an error. Therefore, the example removes the STRICT mode prefix so that the new columns are excluded from the results, executing the query successfully.

SELECT 1 AS one_digit, 10 AS two_digit, 100 AS three_digit
UNION ALL CORRESPONDING
SELECT 20 AS two_digit, 2 AS one_digit, 1000 AS four_digit;

/*-----------+-----------+
 | one_digit | two_digit |
 +-----------+-----------+
 | 1         | 10        |
 | 2         | 20        |
 +-----------+-----------*/

To include the differing columns in the results, the following example uses the FULL OUTER mode prefix to populate NULL values for the missing column in each query.

SELECT 1 AS one_digit, 10 AS two_digit, 100 AS three_digit
FULL OUTER UNION ALL CORRESPONDING
SELECT 20 AS two_digit, 2 AS one_digit, 1000 AS four_digit;

/*-----------+-----------+-------------+------------+
 | one_digit | two_digit | three_digit | four_digit |
 +-----------+-----------+-------------+------------+
 | 1         | 10        | 100         | NULL       |
 | 2         | 20        | NULL        | 1000       |
 +-----------+-----------+-------------+------------*/

Similarly, the following example uses the LEFT OUTER mode prefix to include the new column from only the left input query and populate a NULL value for the missing column in the right input query.

SELECT 1 AS one_digit, 10 AS two_digit, 100 AS three_digit
LEFT OUTER UNION ALL CORRESPONDING
SELECT 20 AS two_digit, 2 AS one_digit, 1000 AS four_digit;

/*-----------+-----------+-------------+
 | one_digit | two_digit | three_digit |
 +-----------+-----------+-------------+
 | 1         | 10        | 100         |
 | 2         | 20        | NULL        |
 +-----------+-----------+-------------*/

The following example adds the modifier BY (column_list) to return only the specified columns in the specified order.

SELECT 1 AS one_digit, 10 AS two_digit, 100 AS three_digit
FULL OUTER UNION ALL CORRESPONDING BY (three_digit, two_digit)
SELECT 20 AS two_digit, 2 AS one_digit, 1000 AS four_digit;

/*-------------+-----------+
 | three_digit | two_digit |
 +-------------+-----------+
 | 100         | 10        |
 | NULL        | 20        |
 +-----------+-------------*/

INTERSECT

The INTERSECT operator returns rows that are found in the results of both the left and right input queries.

Examples

SELECT * FROM UNNEST(ARRAY<INT64>[1, 2, 3, 3, 4]) AS number
INTERSECT DISTINCT
SELECT * FROM UNNEST(ARRAY<INT64>[2, 3, 3, 5]) AS number;

/*--------+
 | number |
 +--------+
 | 2      |
 | 3      |
 +--------*/

The following example shows multiple chained operations:

SELECT * FROM UNNEST(ARRAY<INT64>[1, 2, 3, 3, 4]) AS number
INTERSECT DISTINCT
SELECT * FROM UNNEST(ARRAY<INT64>[2, 3, 3, 5]) AS number
INTERSECT DISTINCT
SELECT * FROM UNNEST(ARRAY<INT64>[3, 3, 4, 5]) AS number;

/*--------+
 | number |
 +--------+
 | 3      |
 +--------*/

The following example shows input queries that specify multiple columns. Both queries specify the same column names but in different orders. As a result, the same columns in differing order are considered different columns, so the query doesn't detect any intersecting row values. Therefore, no results are returned.

WITH
  NumbersTable AS (
    SELECT 1 AS one_digit, 10 AS two_digit
    UNION ALL
    SELECT 2, 20
    UNION ALL
    SELECT 3, 30
  )
SELECT one_digit, two_digit FROM NumbersTable
INTERSECT DISTINCT
SELECT 10 AS two_digit, 1 AS one_digit;

-- No intersecting values detected because columns aren't recognized as the same.
/*-----------+-----------+

 +-----------+-----------*/

To resolve this ordering issue, the following example uses the STRICT CORRESPONDING modifier to match the columns by name instead of by position in the query results. The STRICT mode prefix ensures that all column names in both queries match, which is typically best practice.

WITH
  NumbersTable AS (
    SELECT 1 AS one_digit, 10 AS two_digit
    UNION ALL
    SELECT 2, 20
    UNION ALL
    SELECT 3, 30
  )
SELECT one_digit, two_digit FROM NumbersTable
INTERSECT DISTINCT STRICT CORRESPONDING
SELECT 10 AS two_digit, 1 AS one_digit;

/*-----------+-----------+
 | one_digit | two_digit |
 +-----------+-----------+
 | 1         | 10        |
 +-----------+-----------*/

For more syntax examples with the CORRESPONDING modifier, see the UNION set operator.

EXCEPT

The EXCEPT operator returns rows from the left input query that aren't present in the right input query.

Examples

SELECT * FROM UNNEST(ARRAY<INT64>[1, 2, 3, 3, 4]) AS number
EXCEPT DISTINCT
SELECT * FROM UNNEST(ARRAY<INT64>[1, 2]) AS number;

/*--------+
 | number |
 +--------+
 | 3      |
 | 4      |
 +--------*/

The following example shows multiple chained operations:

SELECT * FROM UNNEST(ARRAY<INT64>[1, 2, 3, 3, 4]) AS number
EXCEPT DISTINCT
SELECT * FROM UNNEST(ARRAY<INT64>[1, 2]) AS number
EXCEPT DISTINCT
SELECT * FROM UNNEST(ARRAY<INT64>[1, 4]) AS number;

/*--------+
 | number |
 +--------+
 | 3      |
 +--------*/

The following example modifies the execution behavior of the set operations. The first input query is used against the result of the last two input queries instead of the values of the last two queries individually. In this example, the EXCEPT result of the last two input queries is 2. Therefore, the EXCEPT results of the entire query are any values other than 2 in the first input query.

SELECT * FROM UNNEST(ARRAY<INT64>[1, 2, 3, 3, 4]) AS number
EXCEPT DISTINCT
(
  SELECT * FROM UNNEST(ARRAY<INT64>[1, 2]) AS number
  EXCEPT DISTINCT
  SELECT * FROM UNNEST(ARRAY<INT64>[1, 4]) AS number
);

/*--------+
 | number |
 +--------+
 | 1      |
 | 3      |
 | 4      |
 +--------*/

The following example shows input queries that specify multiple columns. Both queries specify the same column names but in different orders. As a result, the same columns in differing order are considered different columns, so the query doesn't detect any common rows that should be excluded. Therefore, all column values from the left input query are returned with no exclusions.

WITH
  NumbersTable AS (
    SELECT 1 AS one_digit, 10 AS two_digit
    UNION ALL
    SELECT 2, 20
    UNION ALL
    SELECT 3, 30
  )
SELECT one_digit, two_digit FROM NumbersTable
EXCEPT DISTINCT
SELECT 10 AS two_digit, 1 AS one_digit;

-- No values excluded because columns aren't recognized as the same.
/*-----------+-----------+
 | one_digit | two_digit |
 +-----------+-----------+
 | 1         | 10        |
 | 2         | 20        |
 | 3         | 30        |
 +-----------+-----------*/

To resolve this ordering issue, the following example uses the STRICT CORRESPONDING modifier to match the columns by name instead of by position in the query results. The STRICT mode prefix ensures that all column names in both queries match, which is typically best practice.

WITH
  NumbersTable AS (
    SELECT 1 AS one_digit, 10 AS two_digit
    UNION ALL
    SELECT 2, 20
    UNION ALL
    SELECT 3, 30
  )
SELECT one_digit, two_digit FROM NumbersTable
EXCEPT DISTINCT STRICT CORRESPONDING
SELECT 10 AS two_digit, 1 AS one_digit;

/*-----------+-----------+
 | one_digit | two_digit |
 +-----------+-----------+
 | 2         | 20        |
 | 3         | 30        |
 +-----------+-----------*/

For more syntax examples with the CORRESPONDING modifier, see the UNION set operator.

LIMIT and OFFSET clause

LIMIT count [ OFFSET skip_rows ]

Limits the number of rows to return in a query. Optionally includes the ability to skip over rows.

Definitions

• LIMIT: Limits the number of rows to produce.

  count is an INT64 constant expression that represents the non-negative, non-NULL limit. No more than count rows are produced. LIMIT 0 returns 0 rows.

  If there is a set operation, LIMIT is applied after the set operation is evaluated.

• OFFSET: Skips a specific number of rows before applying LIMIT.

  skip_rows is an INT64 constant expression that represents the non-negative, non-NULL number of rows to skip.

Details

The rows that are returned by LIMIT and OFFSET have undefined order unless these clauses are used after ORDER BY.

A constant expression can be represented by a general expression, literal, or parameter value.

Note: Although the LIMIT clause limits the rows that a query produces, it doesn't limit the amount of data processed by that query.

Examples

SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 2;

/*---------+
 | letter  |
 +---------+
 | a       |
 | b       |
 +---------*/

SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 3 OFFSET 1;

/*---------+
 | letter  |
 +---------+
 | b       |
 | c       |
 | d       |
 +---------*/

WITH clause

WITH [ RECURSIVE ] { non_recursive_cte | recursive_cte }[, ...]

A WITH clause contains one or more common table expressions (CTEs). A CTE acts like a temporary table that you can reference within a single query expression. Each CTE binds the results of a subquery to a table name, which can be used elsewhere in the same query expression, but rules apply.

CTEs can be non-recursive or recursive and you can include both of these in your WITH clause. A recursive CTE references itself, where a non-recursive CTE doesn't. If a recursive CTE is included in the WITH clause, the RECURSIVE keyword must also be included.

You can include the RECURSIVE keyword in a WITH clause even if no recursive CTEs are present. You can learn more about the RECURSIVE keyword here.

RECURSIVE keyword

A WITH clause can optionally include the RECURSIVE keyword, which does two things:

• Enables recursion in the WITH clause. If this keyword isn't present, you can only include non-recursive common table expressions (CTEs). If this keyword is present, you can use both recursive and non-recursive CTEs.
• Changes the visibility of CTEs in the WITH clause. If this keyword isn't present, a CTE is only visible to CTEs defined after it in the WITH clause. If this keyword is present, a CTE is visible to all CTEs in the WITH clause where it was defined.

Non-recursive CTEs

non_recursive_cte:
  cte_name AS ( query_expr )

A non-recursive common table expression (CTE) contains a non-recursive subquery and a name associated with the CTE.

• A non-recursive CTE can't reference itself.
• A non-recursive CTE can be referenced by the query expression that contains the WITH clause, but rules apply.

Examples

In this example, a WITH clause defines two non-recursive CTEs that are referenced in the related set operation, where one CTE is referenced by each of the set operation's input query expressions:

WITH subQ1 AS (SELECT SchoolID FROM Roster),
     subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2

You can break up more complex queries into a WITH clause and WITH SELECT statement instead of writing nested table subqueries. For example:

WITH q1 AS (my_query)
SELECT *
FROM
  (WITH q2 AS (SELECT * FROM q1) SELECT * FROM q2)

WITH q1 AS (my_query)
SELECT *
FROM
  (WITH q2 AS (SELECT * FROM q1),  # q1 resolves to my_query
        q3 AS (SELECT * FROM q1),  # q1 resolves to my_query
        q1 AS (SELECT * FROM q1),  # q1 (in the query) resolves to my_query
        q4 AS (SELECT * FROM q1)   # q1 resolves to the WITH subquery on the previous line.
    SELECT * FROM q1)              # q1 resolves to the third inner WITH subquery.

Recursive CTEs

recursive_cte:
  cte_name AS ( recursive_union_operation )

recursive_union_operation:
  base_term union_operator recursive_term

base_term:
  query_expr

recursive_term:
  query_expr

union_operator:
  UNION ALL

A recursive common table expression (CTE) contains a recursive subquery and a name associated with the CTE.

• A recursive CTE references itself.
• A recursive CTE can be referenced in the query expression that contains the WITH clause, but rules apply.
• When a recursive CTE is defined in a WITH clause, the RECURSIVE keyword must be present.

A recursive CTE is defined by a recursive union operation. The recursive union operation defines how input is recursively processed to produce the final CTE result. The recursive union operation has the following parts:

• base_term: Runs the first iteration of the recursive union operation. This term must follow the base term rules.
• union_operator: The UNION operator returns the rows that are from the union of the base term and recursive term. With UNION ALL, each row produced in iteration N becomes part of the final CTE result and input for iteration N+1. Iteration stops when an iteration produces no rows to move into the next iteration.
• recursive_term: Runs the remaining iterations. It must include one self-reference (recursive reference) to the recursive CTE. Only this term can include a self-reference. This term must follow the recursive term rules.

A recursive CTE looks like this:

WITH RECURSIVE
  T1 AS ( (SELECT 1 AS n) UNION ALL (SELECT n + 1 AS n FROM T1 WHERE n < 3) )
SELECT n FROM T1

/*---+
 | n |
 +---+
 | 2 |
 | 1 |
 | 3 |
 +---*/

The first iteration of a recursive union operation runs the base term. Then, each subsequent iteration runs the recursive term and produces new rows which are unioned with the previous iteration. The recursive union operation terminates when a recursive term iteration produces no new rows.

If recursion doesn't terminate, the query fails after reaching 500 iterations.

To avoid a non-terminating iteration in a recursive union operation, you can use the LIMIT clause in a query.

A recursive CTE can include nested WITH clauses, however, you can't reference recursive_term inside of an inner WITH clause. An inner WITH clause can't be recursive unless it includes its own RECURSIVE keyword. The RECURSIVE keyword affects only the particular WITH clause to which it belongs.

To learn more about recursive CTEs and troubleshooting iteration limit errors, see Work with recursive CTEs.

Examples of allowed recursive CTEs

This is a simple recursive CTE:

WITH RECURSIVE
  T1 AS (
    (SELECT 1 AS n) UNION ALL
    (SELECT n + 2 FROM T1 WHERE n < 4))
SELECT * FROM T1 ORDER BY n

/*---+
 | n |
 +---+
 | 1 |
 | 3 |
 | 5 |
 +---*/

Multiple subqueries in the same recursive CTE are okay, as long as each recursion has a cycle length of 1. It's also okay for recursive entries to depend on non-recursive entries and vice-versa:

WITH RECURSIVE
  T0 AS (SELECT 1 AS n),
  T1 AS ((SELECT * FROM T0) UNION ALL (SELECT n + 1 FROM T1 WHERE n < 4)),
  T2 AS ((SELECT 1 AS n) UNION ALL (SELECT n + 1 FROM T2 WHERE n < 4)),
  T3 AS (SELECT * FROM T1 INNER JOIN T2 USING (n))
SELECT * FROM T3 ORDER BY n

/*---+
 | n |
 +---+
 | 1 |
 | 2 |
 | 3 |
 | 4 |
 +---*/

Aggregate functions can be invoked in subqueries, as long as they aren't aggregating on the table being defined:

WITH RECURSIVE
  T0 AS (SELECT * FROM UNNEST ([60, 20, 30])),
  T1 AS ((SELECT 1 AS n) UNION ALL (SELECT n + (SELECT COUNT(*) FROM T0) FROM T1 WHERE n < 4))
SELECT * FROM T1 ORDER BY n

/*---+
 | n |
 +---+
 | 1 |
 | 4 |
 +---*/

INNER JOIN can be used inside subqueries:

WITH RECURSIVE
  T0 AS (SELECT 1 AS n),
  T1 AS ((SELECT 1 AS n) UNION ALL (SELECT n + 1 FROM T1 INNER JOIN T0 USING (n)))
SELECT * FROM T1 ORDER BY n

/*---+
 | n |
 +---+
 | 1 |
 | 2 |
 +---*/

CROSS JOIN can be used inside subqueries:

WITH RECURSIVE
  T0 AS (SELECT 2 AS p),
  T1 AS ((SELECT 1 AS n) UNION ALL (SELECT T1.n + T0.p FROM T1 CROSS JOIN T0 WHERE T1.n < 4))
SELECT * FROM T1 CROSS JOIN T0 ORDER BY n

/*---+---+
 | n | p |
 +---+---+
 | 1 | 2 |
 | 3 | 2 |
 | 5 | 2 |
 +---+---*/

Examples of disallowed recursive CTEs

The following recursive CTE is disallowed because the self-reference doesn't include a set operator, base term, and recursive term.

WITH RECURSIVE
  T1 AS (SELECT * FROM T1)
SELECT * FROM T1

-- Error

The following recursive CTE is disallowed because the self-reference to T1 is in the base term. The self reference is only allowed in the recursive term.

WITH RECURSIVE
  T1 AS ((SELECT * FROM T1) UNION ALL (SELECT 1))
SELECT * FROM T1

-- Error

The following recursive CTE is disallowed because there are multiple self-references in the recursive term when there must only be one.

WITH RECURSIVE
  T1 AS ((SELECT 1 AS n) UNION ALL ((SELECT * FROM T1) UNION ALL (SELECT * FROM T1)))
SELECT * FROM T1

-- Error

The following recursive CTE is disallowed because the self-reference is inside an expression subquery

WITH RECURSIVE
  T1 AS ((SELECT 1 AS n) UNION ALL (SELECT (SELECT n FROM T1)))
SELECT * FROM T1

-- Error

The following recursive CTE is disallowed because there is a self-reference as an argument to a table-valued function (TVF).

WITH RECURSIVE
  T1 AS (
    (SELECT 1 AS n) UNION ALL
    (SELECT * FROM MY_TVF(T1)))
SELECT * FROM T1;

-- Error

The following recursive CTE is disallowed because there is a self-reference as input to an outer join.

WITH RECURSIVE
  T0 AS (SELECT 1 AS n),
  T1 AS ((SELECT 1 AS n) UNION ALL (SELECT * FROM T1 FULL OUTER JOIN T0 USING (n)))
SELECT * FROM T1;

-- Error

The following recursive CTE is disallowed because you can't use aggregation with a self-reference.

WITH RECURSIVE
  T1 AS (
    (SELECT 1 AS n) UNION ALL
    (SELECT COUNT(*) FROM T1))
SELECT * FROM T1;

-- Error

The following recursive CTE is disallowed because you can't use the window function OVER clause with a self-reference.

WITH RECURSIVE
  T1 AS (
    (SELECT 1.0 AS n) UNION ALL
    SELECT 1 + AVG(n) OVER(ROWS between 2 PRECEDING and 0 FOLLOWING) FROM T1 WHERE n < 10)
SELECT n FROM T1;

-- Error

The following recursive CTE is disallowed because you can't use a LIMIT clause with a self-reference.

WITH RECURSIVE
  T1 AS ((SELECT 1 AS n) UNION ALL (SELECT n FROM T1 LIMIT 3))
SELECT * FROM T1;

-- Error

The following recursive CTEs are disallowed because you can't use an ORDER BY clause with a self-reference.

WITH RECURSIVE
  T1 AS ((SELECT 1 AS n) UNION ALL (SELECT n + 1 FROM T1 ORDER BY n))
SELECT * FROM T1;

-- Error

The following recursive CTE is disallowed because table T1 can't be recursively referenced from inside an inner WITH clause

WITH RECURSIVE
  T1 AS ((SELECT 1 AS n) UNION ALL (WITH t AS (SELECT n FROM T1) SELECT * FROM t))
SELECT * FROM T1

-- Error

CTE rules and constraints

Common table expressions (CTEs) can be referenced inside the query expression that contains the WITH clause.

General rules

Here are some general rules and constraints to consider when working with CTEs:

• Each CTE in the same WITH clause must have a unique name.
• You must include the RECURSIVE keyword keyword if the WITH clause contains a recursive CTE.
• The RECURSIVE keyword in the WITH clause changes the visibility of CTEs to other CTEs in the same WITH clause. You can learn more here.
• A local CTE overrides an outer CTE or table with the same name.
• A CTE on a subquery may not reference correlated columns from the outer query.
• UNION DISTINCT isn't allowed inside a WITH RECURSIVE clause.

Base term rules

The following rules apply to the base term in a recursive CTE:

• The base term is required to be non-recursive.
• The base term determines the names and types of all of the table columns.

Recursive term rules

The following rules apply to the recursive term in a recursive CTE:

• The recursive term must include exactly one reference to the recursively-defined table in the base term.
• The recursive term must contain the same number of columns as the base term, and the type of each column must be implicitly coercible to the type of the corresponding column in the base term.
• A recursive table reference can't be used as an operand to a FULL JOIN, a right operand to a LEFT JOIN, or a left operand to a RIGHT JOIN.
• A recursive table reference can't be used as an operand to a table-valued function (TVF).

The following rules apply to a subquery inside a recursive term:

• A subquery with a recursive table reference must be a SELECT expression, not a set operation, such as UNION ALL.
• A subquery can't contain, directly or indirectly, a recursive table reference anywhere outside of its FROM clause.
• A subquery with a recursive table reference can't contain an ORDER BY or LIMIT clause.
• A subquery with a recursive table reference can't invoke aggregate functions.
• A subquery with a recursive table reference can't invoke window functions.
• A subquery with a recursive table reference can't contain the DISTINCT keyword or GROUP BY clause.

CTE visibility

The visibility of a common table expression (CTE) within a query expression is determined by whether or not you add the RECURSIVE keyword to the WITH clause where the CTE was defined. You can learn more about these differences in the following sections.

Visibility of CTEs in a WITH clause with the RECURSIVE keyword

When you include the RECURSIVE keyword, references between CTEs in the WITH clause can go backwards and forwards. Cycles aren't allowed.

This is what happens when you have two CTEs that reference themselves or each other in a WITH clause with the RECURSIVE keyword. Assume that A is the first CTE and B is the second CTE in the clause:

• A references A = Valid
• A references B = Valid
• B references A = Valid
• A references B references A = Invalid (cycles aren't allowed)

A can reference itself because self-references are supported:

WITH RECURSIVE
  A AS (SELECT 1 AS n UNION ALL (SELECT n + 1 FROM A WHERE n < 3))
SELECT * FROM A

/*---+
 | n |
 +---+
 | 1 |
 | 2 |
 | 3 |
 +---*/

A can reference B because references between CTEs can go forwards:

WITH RECURSIVE
  A AS (SELECT * FROM B),
  B AS (SELECT 1 AS n)
SELECT * FROM B

/*---+
 | n |
 +---+
 | 1 |
 +---*/

B can reference A because references between CTEs can go backwards:

WITH RECURSIVE
  A AS (SELECT 1 AS n),
  B AS (SELECT * FROM A)
SELECT * FROM B

/*---+
 | n |
 +---+
 | 1 |
 +---*/

This produces an error. A and B reference each other, which creates a cycle:

WITH RECURSIVE
  A AS (SELECT * FROM B),
  B AS (SELECT * FROM A)
SELECT * FROM B

-- Error

Visibility of CTEs in a WITH clause without the RECURSIVE keyword

When you don't include the RECURSIVE keyword in the WITH clause, references between CTEs in the clause can go backward but not forward.

This is what happens when you have two CTEs that reference themselves or each other in a WITH clause without the RECURSIVE keyword. Assume that A is the first CTE and B is the second CTE in the clause:

• A references A = Invalid
• A references B = Invalid
• B references A = Valid
• A references B references A = Invalid (cycles aren't allowed)

This produces an error. A can't reference itself because self-references aren't supported:

WITH
  A AS (SELECT 1 AS n UNION ALL (SELECT n + 1 FROM A WHERE n < 3))
SELECT * FROM A

-- Error

This produces an error. A can't reference B because references between CTEs can go backwards but not forwards:

WITH
  A AS (SELECT * FROM B),
  B AS (SELECT 1 AS n)
SELECT * FROM B

-- Error

B can reference A because references between CTEs can go backwards:

WITH
  A AS (SELECT 1 AS n),
  B AS (SELECT * FROM A)
SELECT * FROM B

/*---+
 | n |
 +---+
 | 1 |
 +---*/

This produces an error. A and B reference each other, which creates a cycle:

WITH
  A AS (SELECT * FROM B),
  B AS (SELECT * FROM A)
SELECT * FROM B

-- Error

Using aliases

An alias is a temporary name given to a table, column, or expression present in a query. You can introduce explicit aliases in the SELECT list or FROM clause, or GoogleSQL infers an implicit alias for some expressions. Expressions with neither an explicit nor implicit alias are anonymous and the query can't reference them by name.

Explicit aliases

You can introduce explicit aliases in either the FROM clause or the SELECT list.

In a FROM clause, you can introduce explicit aliases for any item, including tables, arrays, subqueries, and UNNEST clauses, using [AS] alias. The AS keyword is optional.

Example:

SELECT s.FirstName, s2.SongName
FROM Singers AS s, (SELECT * FROM Songs) AS s2;

You can introduce explicit aliases for any expression in the SELECT list using [AS] alias. The AS keyword is optional.

Example:

SELECT s.FirstName AS name, LOWER(s.FirstName) AS lname
FROM Singers s;

Implicit aliases

In the SELECT list, if there is an expression that doesn't have an explicit alias, GoogleSQL assigns an implicit alias according to the following rules. There can be multiple columns with the same alias in the SELECT list.

• For identifiers, the alias is the identifier. For example, SELECT abc implies AS abc.
• For path expressions, the alias is the last identifier in the path. For example, SELECT abc.def.ghi implies AS ghi.
• For field access using the "dot" member field access operator, the alias is the field name. For example, SELECT (struct_function()).fname implies AS fname.

Note: If you omit whitespace between a literal and an alias, GoogleSQL returns a "Missing whitespace" error. For example, SELECT 123 abc implies SELECT 123 AS abc, butSELECT 123abc` produces the error.

In all other cases, there is no implicit alias, so the column is anonymous and can't be referenced by name. The data from that column will still be returned and the displayed query results may have a generated label for that column, but the label can't be used like an alias.

In a FROM clause, from_items aren't required to have an alias. The following rules apply:

• If there is an expression that doesn't have an explicit alias, GoogleSQL assigns an implicit alias in these cases:
  • For identifiers, the alias is the identifier. For example, FROM abc implies AS abc.
  • For path expressions, the alias is the last identifier in the path. For example, FROM abc.def.ghi implies AS ghi
  • The column produced using WITH OFFSET has the implicit alias offset.
• Table subqueries don't have implicit aliases.
• FROM UNNEST(x) doesn't have an implicit alias.

Alias visibility

After you introduce an explicit alias in a query, there are restrictions on where else in the query you can reference that alias. These restrictions on alias visibility are the result of GoogleSQL name scoping rules.

Visibility in the FROM clause

GoogleSQL processes aliases in a FROM clause from left to right, and aliases are visible only to subsequent path expressions in a FROM clause.

Example:

Assume the Singers table had a Concerts column of ARRAY type.

SELECT FirstName
FROM Singers AS s, s.Concerts;

Invalid:

SELECT FirstName
FROM s.Concerts, Singers AS s;  // INVALID.

FROM clause aliases are not visible to subqueries in the same FROM clause. Subqueries in a FROM clause can't contain correlated references to other tables in the same FROM clause.

Invalid:

SELECT FirstName
FROM Singers AS s, (SELECT (2020 - ReleaseDate) FROM s)  // INVALID.

You can use any column name from a table in the FROM as an alias anywhere in the query, with or without qualification with the table name.

Example:

SELECT FirstName, s.ReleaseDate
FROM Singers s WHERE ReleaseDate = 1975;

If the FROM clause contains an explicit alias, you must use the explicit alias instead of the implicit alias for the remainder of the query (see Implicit Aliases). A table alias is useful for brevity or to eliminate ambiguity in cases such as self-joins, where the same table is scanned multiple times during query processing.

Example:

SELECT * FROM Singers as s, Songs as s2
ORDER BY s.LastName

Invalid — ORDER BY doesn't use the table alias:

SELECT * FROM Singers as s, Songs as s2
ORDER BY Singers.LastName;  // INVALID.

Visibility in the SELECT list

Aliases in the SELECT list are visible only to the following clauses:

• GROUP BY clause
• ORDER BY clause
• HAVING clause

Example:

SELECT LastName AS last, SingerID
FROM Singers
ORDER BY last;

Invalid:

SELECT LastName AS last, SingerID
FROM Singers
WHERE last = "Smith";  // INVALID.

Visibility in the GROUP BY, ORDER BY, and HAVING clauses

These three clauses, GROUP BY, ORDER BY, and HAVING, can refer to only the following values:

• Tables in the FROM clause and any of their columns.
• Aliases from the SELECT list.

GROUP BY and ORDER BY can also refer to a third group:

• Integer literals, which refer to items in the SELECT list. The integer 1 refers to the first item in the SELECT list, 2 refers to the second item, etc.

Example:

SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY 1
ORDER BY 2 DESC;

The previous query is equivalent to:

SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY sid
ORDER BY s2id DESC;

Duplicate aliases

A SELECT list or subquery containing multiple explicit or implicit aliases of the same name is allowed, as long as the alias name isn't referenced elsewhere in the query, since the reference would be ambiguous.

Example:

SELECT 1 AS a, 2 AS a;

/*---+---+
 | a | a |
 +---+---+
 | 1 | 2 |
 +---+---*/

Duplicate column names in a table or view definition aren't supported. These statements with queries that contain duplicate column names will fail:

-- This query fails.
CREATE TABLE my_dataset.my_table AS (SELECT 1 AS a, 2 AS a);

-- This query fails.
CREATE VIEW my_dataset.my_view AS (SELECT 1 AS a, 2 AS a);

Ambiguous aliases

GoogleSQL provides an error if accessing a name is ambiguous, meaning it can resolve to more than one unique object in the query or in a table schema, including the schema of a destination table.

The following query contains column names that conflict between tables, since both Singers and Songs have a column named SingerID:

SELECT SingerID
FROM Singers, Songs;

The following query contains aliases that are ambiguous in the GROUP BY clause because they are duplicated in the SELECT list:

SELECT FirstName AS name, LastName AS name,
FROM Singers
GROUP BY name;

The following query contains aliases that are ambiguous in the SELECT list and FROM clause because they share a column and field with same name.

• Assume the Person table has three columns: FirstName, LastName, and PrimaryContact.
• Assume the PrimaryContact column represents a struct with these fields: FirstName and LastName.

The alias P is ambiguous and will produce an error because P.FirstName in the GROUP BY clause could refer to either Person.FirstName or Person.PrimaryContact.FirstName.

SELECT FirstName, LastName, PrimaryContact AS P
FROM Person AS P
GROUP BY P.FirstName;

A name is not ambiguous in GROUP BY, ORDER BY or HAVING if it's both a column name and a SELECT list alias, as long as the name resolves to the same underlying object. In the following example, the alias BirthYear isn't ambiguous because it resolves to the same underlying column, Singers.BirthYear.

SELECT LastName, BirthYear AS BirthYear
FROM Singers
GROUP BY BirthYear;

Range variables

In GoogleSQL, a range variable is a table expression alias in the FROM clause. Sometimes a range variable is known as a table alias. A range variable lets you reference rows being scanned from a table expression. A table expression represents an item in the FROM clause that returns a table. Common items that this expression can represent include tables, subqueries, table-valued functions (TVFs), joins, and parenthesized joins.

In general, a range variable provides a reference to the rows of a table expression. A range variable can be used to qualify a column reference and unambiguously identify the related table, for example range_variable.column_1.

When referencing a range variable on its own without a specified column suffix, the result of a table expression is the row type of the related table. Value tables have explicit row types, so for range variables related to value tables, the result type is the value table's row type. Other tables don't have explicit row types, and for those tables, the range variable type is a dynamically defined struct that includes all of the columns in the table.

Examples

In these examples, the WITH clause is used to emulate a temporary table called Grid. This table has columns x and y. A range variable called Coordinate refers to the current row as the table is scanned. Coordinate can be used to access the entire row or columns in the row.

The following example selects column x from range variable Coordinate, which in effect selects column x from table Grid.

WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate.x FROM Grid AS Coordinate;

/*---+
 | x |
 +---+
 | 1 |
 +---*/

The following example selects all columns from range variable Coordinate, which in effect selects all columns from table Grid.

WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate.* FROM Grid AS Coordinate;

/*---+---+
 | x | y |
 +---+---+
 | 1 | 2 |
 +---+---*/

The following example selects the range variable Coordinate, which is a reference to rows in table Grid. Since Grid isn't a value table, the result type of Coordinate is a struct that contains all the columns from Grid.

WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate FROM Grid AS Coordinate;

/*--------------+
 | Coordinate   |
 +--------------+
 | {x: 1, y: 2} |
 +--------------*/

Table function calls

To call a TVF, use the function call in place of the table name in a FROM clause.

Appendix A: examples with sample data

These examples include statements which perform queries on the Roster and TeamMascot, and PlayerStats tables.

Sample tables

The following tables are used to illustrate the behavior of different query clauses in this reference.

Roster table

The Roster table includes a list of player names (LastName) and the unique ID assigned to their school (SchoolID). It looks like this:

/*-----------------------+
 | LastName   | SchoolID |
 +-----------------------+
 | Adams      | 50       |
 | Buchanan   | 52       |
 | Coolidge   | 52       |
 | Davis      | 51       |
 | Eisenhower | 77       |
 +-----------------------*/

You can use this WITH clause to emulate a temporary table name for the examples in this reference:

WITH Roster AS
 (SELECT 'Adams' as LastName, 50 as SchoolID UNION ALL
  SELECT 'Buchanan', 52 UNION ALL
  SELECT 'Coolidge', 52 UNION ALL
  SELECT 'Davis', 51 UNION ALL
  SELECT 'Eisenhower', 77)
SELECT * FROM Roster

PlayerStats table

The PlayerStats table includes a list of player names (LastName) and the unique ID assigned to the opponent they played in a given game (OpponentID) and the number of points scored by the athlete in that game (PointsScored).

/*----------------------------------------+
 | LastName   | OpponentID | PointsScored |
 +----------------------------------------+
 | Adams      | 51         | 3            |
 | Buchanan   | 77         | 0            |
 | Coolidge   | 77         | 1            |
 | Adams      | 52         | 4            |
 | Buchanan   | 50         | 13           |
 +----------------------------------------*/

You can use this WITH clause to emulate a temporary table name for the examples in this reference:

WITH PlayerStats AS
 (SELECT 'Adams' as LastName, 51 as OpponentID, 3 as PointsScored UNION ALL
  SELECT 'Buchanan', 77, 0 UNION ALL
  SELECT 'Coolidge', 77, 1 UNION ALL
  SELECT 'Adams', 52, 4 UNION ALL
  SELECT 'Buchanan', 50, 13)
SELECT * FROM PlayerStats

TeamMascot table

The TeamMascot table includes a list of unique school IDs (SchoolID) and the mascot for that school (Mascot).

/*---------------------+
 | SchoolID | Mascot   |
 +---------------------+
 | 50       | Jaguars  |
 | 51       | Knights  |
 | 52       | Lakers   |
 | 53       | Mustangs |
 +---------------------*/

You can use this WITH clause to emulate a temporary table name for the examples in this reference:

WITH TeamMascot AS
 (SELECT 50 as SchoolID, 'Jaguars' as Mascot UNION ALL
  SELECT 51, 'Knights' UNION ALL
  SELECT 52, 'Lakers' UNION ALL
  SELECT 53, 'Mustangs')
SELECT * FROM TeamMascot

GROUP BY clause

Example:

SELECT LastName, SUM(PointsScored)
FROM PlayerStats
GROUP BY LastName;

LastName	SUM
Adams	7
Buchanan	13
Coolidge	1

UNION

The UNION operator combines the result sets of two or more SELECT statements by pairing columns from the result set of each SELECT statement and vertically concatenating them.

Example:

SELECT Mascot AS X, SchoolID AS Y
FROM TeamMascot
UNION ALL
SELECT LastName, PointsScored
FROM PlayerStats;

Results:

X	Y
Jaguars	50
Knights	51
Lakers	52
Mustangs	53
Adams	3
Buchanan	0
Coolidge	1
Adams	4
Buchanan	13

INTERSECT

This query returns the last names that are present in both Roster and PlayerStats.

SELECT LastName
FROM Roster
INTERSECT DISTINCT
SELECT LastName
FROM PlayerStats;

Results:

LastName
Adams
Coolidge
Buchanan

EXCEPT

The query below returns last names in Roster that are not present in PlayerStats.

SELECT LastName
FROM Roster
EXCEPT DISTINCT
SELECT LastName
FROM PlayerStats;

Results:

LastName
Eisenhower
Davis

Reversing the order of the SELECT statements will return last names in PlayerStats that are not present in Roster:

SELECT LastName
FROM PlayerStats
EXCEPT DISTINCT
SELECT LastName
FROM Roster;

Results:

(empty)