Query syntax in GoogleSQL

Query statements scan one or more tables or expressions and return the computedresult rows. This topic describes the syntax for SQL queries inGoogleSQL for Spanner.

SQL syntax notation rules

The GoogleSQL documentation commonly uses the followingsyntax notation rules:

SQL syntax

query_statement:  [statement_hint_expr ]  [table_hint_expr ]  [join_hint_expr ]query_exprquery_expr:  [WITHcte[, ...] ]  {select | (query_expr ) |set_operation }  [ORDERBYexpression [{ ASC | DESC }] [, ...] ]  [LIMITcount [ OFFSETskip_rows ] ]  [FOR UPDATE ]select:SELECT    [ { ALL | DISTINCT } ]    [ AS {typename |STRUCT |VALUE } ]select_list  [FROMfrom_clause[, ...] ]  [WHEREbool_expression ]  [GROUP BYgroup_by_specification ]  [HAVINGbool_expression ]  [WINDOWwindow_clause ]

SELECT statement

SELECT  [ { ALL | DISTINCT } ]  [ AS {typename |STRUCT |VALUE } ]select_listselect_list:  {select_all |select_expression } [, ...]select_all:  [expression. ]*  [ EXCEPT (column_name [, ...] ) ]  [ REPLACE (expression AScolumn_name [, ...] ) ]select_expression:expression [ [ AS ]alias ]

TheSELECT list defines the columns that the query will return. Expressions intheSELECT list can refer to columns in any of thefrom_items in itscorrespondingFROM clause.

Each item in theSELECT list is one of:

  • *
  • expression
  • expression.*

SELECT *

SELECT *, often referred to asselect star, produces one output column foreach column that's visible after executing the full query.

SELECT*FROM(SELECT"apple"ASfruit,"carrot"ASvegetable);/*-------+-----------* | fruit | vegetable | +-------+-----------+ | apple | carrot    | *-------+-----------*/

SELECT expression

Items in aSELECT list can be expressions. These expressions evaluate to asingle value and produce one output column, with an optional explicitalias.

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

SELECT expression.*

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

The following query produces one output column for each column in the tablegroceries, aliased asg.

WITHgroceriesAS(SELECT"milk"ASdairy,"eggs"ASprotein,"bread"ASgrain)SELECTg.*FROMgroceriesASg;/*-------+---------+-------* | dairy | protein | grain | +-------+---------+-------+ | milk  | eggs    | bread | *-------+---------+-------*/

More examples:

WITHlocationsAS(SELECTSTRUCT("Seattle"AScity,"Washington"ASstate)ASlocationUNIONALLSELECTSTRUCT("Phoenix"AScity,"Arizona"ASstate)ASlocation)SELECTl.location.*FROMlocationsl;/*---------+------------* | city    | state      | +---------+------------+ | Seattle | Washington | | Phoenix | Arizona    | *---------+------------*/
WITHlocationsAS(SELECTARRAY<STRUCT<citySTRING,stateSTRING>>[("Seattle","Washington"),("Phoenix","Arizona")]ASlocation)SELECTl.LOCATION[offset(0)].*FROMlocationsl;/*---------+------------* | city    | state      | +---------+------------+ | Seattle | Washington | *---------+------------*/

SELECT * EXCEPT

ASELECT * EXCEPT statement specifies the names of one or more columns toexclude from the result. All matching column names are omitted from the output.

WITHordersAS(SELECT5asorder_id,"sprocket"asitem_name,200asquantity)SELECT*EXCEPT(order_id)FROMorders;/*-----------+----------* | item_name | quantity | +-----------+----------+ | sprocket  | 200      | *-----------+----------*/
Note:SELECT * EXCEPT doesn't exclude columns that don't have names.

SELECT * REPLACE

ASELECT * REPLACE statement specifies one or moreexpression AS identifier clauses. Each identifier must match a column namefrom theSELECT * statement. In the output column list, the column thatmatches the identifier in aREPLACE clause is replaced by the expression inthatREPLACE clause.

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

WITHordersAS(SELECT5asorder_id,"sprocket"asitem_name,200asquantity)SELECT*REPLACE("widget"ASitem_name)FROMorders;/*----------+-----------+----------* | order_id | item_name | quantity | +----------+-----------+----------+ | 5        | widget    | 200      | *----------+-----------+----------*/WITHordersAS(SELECT5asorder_id,"sprocket"asitem_name,200asquantity)SELECT*REPLACE(quantity/2ASquantity)FROMorders;/*----------+-----------+----------* | order_id | item_name | quantity | +----------+-----------+----------+ | 5        | sprocket  | 100      | *----------+-----------+----------*/
Note:SELECT * REPLACE doesn't replace columns that don't have names.

SELECT DISTINCT

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

  • PROTO
  • STRUCT
  • ARRAY
  • GRAPH_ELEMENT
  • GRAPH_PATH

SELECT ALL

ASELECT ALL statement returns all rows, including duplicate rows.SELECT ALL is the default behavior ofSELECT.

Using STRUCTs with SELECT

  • Queries that return aSTRUCT at the root of the return type aren'tsupported in Spanner APIs. For example, the followingquery is supportedonly as a subquery:

    SELECTSTRUCT(1,2)FROMUsers;

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

    SELECTARRAY(SELECTSTRUCT(1ASA,2ASB))FROMUsers;
    SELECTARRAY(SELECTASSTRUCT1ASa,2ASb)FROMUsers;

  • However, query shapes that can return anARRAY<STRUCT<...>> typedNULLvalue or anARRAY<STRUCT<...>> typed value with an element that'sNULLaren't supported in Spanner APIs, so the following queryis supportedonly as a subquery:

    SELECTARRAY(SELECTIF(STARTS_WITH(Users.username,"a"),NULL,STRUCT(1,2)))FROMUsers;
Note: The logic inside Spanner that decides whether or nota query can return aNULL array of structs orNULL array of structelements isn'tcomplete (in the logic sense ofcomplete). That meanssome queries that clearly can't returnNULLs are still rejected andfine-tuning is sometimes necessary to get a query shape that's supported. Theleast troublesome query shapes use theARRAY(SELECT AS STRUCT ... ) subqueryto construct the array of struct values.

SeeQuerying STRUCT elements in an ARRAY for more examples onhow to querySTRUCTs inside anARRAY.

Also see notes about usingSTRUCTs insubqueries.

SELECT AS STRUCT

SELECTASSTRUCTexpr[[AS]struct_field_name1][,...]

This produces avalue table with aSTRUCT row type, where theSTRUCT field names and types match the column namesand types produced in theSELECT list.

Example:

SELECTARRAY(SELECTASSTRUCT1a,2b)

SELECT AS STRUCT can be used in a scalar or array subquery to produce a singleSTRUCT type grouping multiple values together. Scalarand array subqueries (seeSubqueries) are normally notallowed to return multiple columns, but can return a single column withSTRUCT type.

Anonymous columns are allowed.

Example:

SELECTASSTRUCT1x,2,3

The query above produces STRUCT values of typeSTRUCT<int64 x, int64, int64>. The first field has the namex while thesecond and third fields are anonymous.

The example above produces the same result as thisSELECT AS VALUE query usinga struct constructor:

SELECTASVALUESTRUCT(1ASx,2,3)

Duplicate columns are allowed.

Example:

SELECTASSTRUCT1x,2y,3x

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

The example above produces the same result as thisSELECT AS VALUE queryusing a struct constructor:

SELECTASVALUESTRUCT(1ASx,2ASy,3ASx)

SELECT AS typename

SELECTAStypenameexpr[[AS]field][,...]

ASELECT AS typename statement produces a value table where the row typeis a specific named type. Currently,protocol buffers are theonly supported type that can be used with this syntax.

When selecting as a type that has fields, such as a proto message type,theSELECT list may produce multiple columns. Each produced column must havean explicit orimplicit alias that matches a unique field ofthe named type.

When used withSELECT DISTINCT, orGROUP BY orORDER BY using columnordinals, these operators are first applied on the columns in theSELECT list.The value construction happens last. This means thatDISTINCT can be appliedon the input columns to the value construction, including incases whereDISTINCT wouldn't be allowed after value construction becausegrouping isn't supported on the constructed type.

The following is an example of aSELECT AS typename query.

SELECTAStests.TestProtocolBuffermytable.keyint64_val,mytable.namestring_valFROMmytable;

The query returns the output as atests.TestProtocolBuffer protocolbuffer.mytable.key int64_val means that values from thekey column arestored in theint64_val field in the protocol buffer. Similarly, values fromthemytable.name column are stored in thestring_val protocol buffer field.

To learn more about protocol buffers, seeWork with protocol buffers.

SELECT AS VALUE

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

Example:

SELECTASVALUE1

The query above produces a table with row type INT64.

Example:

SELECTASVALUESTRUCT(1ASa,2ASb)xyz

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

Example:

SELECTASVALUEvFROM(SELECTASSTRUCT1a,trueb)vWHEREv.b

Given a value tablev as input, the query above filters out certain values intheWHERE clause, and then produces a value table using the exact same valuethat was in the input table. If the query above didn't useSELECT AS VALUE,then the output table schema would differ from the input table schema becausethe output table would be a regular table with a column namedv containing theinput value.

FROM clause

FROMfrom_clause[, ...]from_clause:from_item  [tablesample_operator ]from_item:  {table_name [table_hint_expr ] [as_alias ]    | {join_operation | (join_operation ) }    | (query_expr ) [table_hint_expr ] [as_alias ]    |field_path    |unnest_operator    |cte_name [table_hint_expr ] [as_alias ]    |graph_table_operator [as_alias ]  }as_alias:  [ AS ]alias

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

tablesample_operator

SeeTABLESAMPLE operator.

graph_table_operator

SeeGRAPH_TABLE operator.

table_name

The name of an existing table.

SELECT * FROM Roster;

join_operation

SeeJoin operation.

query_expr

( query_expr ) [ [ AS ] alias ] is atable subquery.

field_path

In theFROM clause,field_path is any path thatresolves to a field within a data type.field_path can goarbitrarily deep into a nested data structure.

Some examples of validfield_path values include:

SELECT*FROMT1t1,t1.array_column;SELECT*FROMT1t1,t1.struct_column.array_field;SELECT(SELECTARRAY_AGG(c)FROMt1.array_columnc)FROMT1t1;SELECTa.struct_field1FROMT1t1,t1.array_of_structsa;SELECT(SELECTSTRING_AGG(a.struct_field1)FROMt1.array_of_structsa)FROMT1t1;

Field paths in theFROM clause must end in anarray or a repeated field. Inaddition, field paths can't contain arraysor repeated fields before the end of the path. For example, the patharray_column.some_array.some_array_field is invalid because itcontains 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 usingUNNEST, or use thefully-qualified path.Note: If a path has more than one name, and it matches a fieldname, it's interpreted as a field name. To force the path to be interpreted asa table name, wrap the path using`.

unnest_operator

SeeUNNEST operator.

cte_name

Common table expressions (CTEs) in aWITH Clause act liketemporary tables that you can reference anywhere in theFROM clause.In the example below,subQ1 andsubQ2 are CTEs.

Example:

WITHsubQ1AS(SELECT*FROMRosterWHERESchoolID=52),subQ2AS(SELECTSchoolIDFROMsubQ1)SELECTDISTINCT*FROMsubQ2;

UNNEST operator

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

TheUNNEST operator takes an array and returns a table with one row for eachelement in the array. The output ofUNNEST is onevalue table column.For theseARRAY element types,SELECT * against the value table columnreturns multiple columns:

  • STRUCT
  • PROTO

Input values:

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

  • array_path: Thepath to anARRAY type.

    • In an implicitUNNEST operation, the pathmuststart witharange variable name.
    • In an explicitUNNEST operation, the path can optionally start with arange variable name.

    TheUNNEST operation with anycorrelatedarray_path mustbe on the right side of aCROSS JOIN,LEFT JOIN, orINNER JOIN operation.

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

  • WITH OFFSET:UNNEST destroys the order of elements in the inputarray. Use this optional clause to return an additional column withthe array element indexes, oroffsets. Offset counting starts at zero foreach row produced by theUNNEST operation. This column has anoptional alias; If the optional alias isn't used, the default column name isoffset.

    Example:

    SELECT*FROMUNNEST([10,20,30])asnumbersWITHOFFSET;/*---------+--------* | numbers | offset | +---------+--------+ | 10      | 0      | | 20      | 1      | | 30      | 2      | *---------+--------*/

You can also useUNNEST outside of theFROM clause with theIN operator.

For several ways to useUNNEST, including construction, flattening, andfiltering, seeWork with arrays.

To learn more about the ways you can useUNNEST explicitly and implicitly,seeExplicit and implicitUNNEST.

UNNEST and structs

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

Example:

SELECT*FROMUNNEST(ARRAY<STRUCT<xINT64,ySTRING,zSTRUCT<aINT64,bINT64>>>[(1,'foo',(10,11)),(3,'bar',(20,21))]);/*---+-----+----------* | x | y   | z        | +---+-----+----------+ | 1 | foo | {10, 11} | | 3 | bar | {20, 21} | *---+-----+----------*/

Because theUNNEST operator returns avalue table,you can aliasUNNEST to define a range variable that you can referenceelsewhere in the query. If you reference the range variable in theSELECTlist, the query returns a struct containing all of the fields of the originalstruct in the input table.

Example:

SELECT*,struct_valueFROMUNNEST(ARRAY<STRUCT<xINT64,ySTRING>>[(1,'foo'),(3,'bar')])ASstruct_value;/*---+-----+--------------* | x | y   | struct_value | +---+-----+--------------+ | 3 | bar | {3, bar}     | | 1 | foo | {1, foo}     | *---+-----+--------------*/

UNNEST and protocol buffers

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

Example:

SELECT*FROMUNNEST(ARRAY<googlesql.examples.music.Album>[NEWgooglesql.examples.music.Album('The Goldberg Variations'ASalbum_name,['Aria','Variation 1','Variation 2']ASsong)]);/*-------------------------+--------+----------------------------------* | album_name              | singer | song                             | +-------------------------+--------+----------------------------------+ | The Goldberg Variations | NULL   | [Aria, Variation 1, Variation 2] | *-------------------------+--------+----------------------------------*/

As with structs, you can aliasUNNEST to define a range variable. Youcan reference this alias in theSELECT list to return a value table where eachrow is a protocol buffer element from the array.

SELECTproto_valueFROMUNNEST(ARRAY<googlesql.examples.music.Album>[NEWgooglesql.examples.music.Album('The Goldberg Variations'ASalbum_name,['Aria','Var. 1']ASsong)])ASproto_value;/*---------------------------------------------------------------------* | proto_value                                                         | +---------------------------------------------------------------------+ | {album_name: "The Goldberg Variations" song: "Aria" song: "Var. 1"} | *---------------------------------------------------------------------*/

Explicit and implicitUNNEST

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

Explicit unnesting

TheUNNEST keyword is required in explicit unnesting. For example:

WITHCoordinatesAS(SELECT[1,2]ASposition)SELECTresultsFROMCoordinates,UNNEST(Coordinates.position)ASresults;

This example and the following examples use thearray_path calledCoordinates.position to illustrate unnesting.

Implicit unnesting

TheUNNEST keyword isn't used in implicit unnesting.

For example:

WITHCoordinatesAS(SELECT[1,2]ASposition)SELECTresultsFROMCoordinates,Coordinates.positionASresults;
Tables and implicit unnesting

When you usearray_path with implicitUNNEST,array_path must be prependedwith the table. For example:

WITHCoordinatesAS(SELECT[1,2]ASposition)SELECTresultsFROMCoordinates,Coordinates.positionASresults;

UNNEST andNULL values

UNNEST treatsNULL values as follows:

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

TABLESAMPLE operator

tablesample_clause:  TABLESAMPLE sample_method (sample_size percent_or_rows )sample_method:  { BERNOULLI | RESERVOIR }sample_size:  numeric_value_expressionpercent_or_rows:  { PERCENT | ROWS }

Description

You can use theTABLESAMPLE operator to select a random sample of a dataset.This operator is useful when you're working with tables that have largeamounts of data and you don't need precise answers.

  • sample_method: When using theTABLESAMPLE operator, you must specify thesampling algorithm to use:
    • BERNOULLI: Each row is independently selected with the probabilitygiven in thepercent clause. As a result, you get approximatelyN * percent/100 rows.
    • RESERVOIR: Takes as parameter an actual sample sizeK (expressed as a number of rows). If the input is smaller than K, itoutputs the entire input relation. If the input is larger than K,reservoir sampling outputs a sample of size exactly K, where any sample ofsize K is equally likely.
  • sample_size: The size of the sample.
  • percent_or_rows: TheTABLESAMPLE operator requires that you choose eitherROWS orPERCENT. If you choosePERCENT, the value must be between0 and 100. If you chooseROWS, the value must be greater than or equalto 0.

Examples

The following examples illustrate the use of theTABLESAMPLE operator.

Select from a table using theRESERVOIR sampling method:

SELECTMessageIdFROMMessagesTABLESAMPLERESERVOIR(100ROWS);

Select from a table using theBERNOULLI sampling method:

SELECTMessageIdFROMMessagesTABLESAMPLEBERNOULLI(0.1PERCENT);

UseTABLESAMPLE with a subquery:

SELECTSubjectFROM(SELECTMessageId,SubjectFROMMessagesWHEREServerId="test")TABLESAMPLEBERNOULLI(50PERCENT)WHEREMessageId >3;

Use aTABLESAMPLE operation with a join to another table.

SELECTS.SubjectFROM(SELECTMessageId,ThreadIdFROMMessagesWHEREServerId="test")ASRTABLESAMPLERESERVOIR(5ROWS),ThreadsASSWHERES.ServerId="test"ANDR.ThreadId=S.ThreadId;

GRAPH_TABLE operator

To learn more about this operator, seeGRAPH_TABLE operator in theGraph Query Language (GQL) reference guide.

Join operation

join_operation:  {cross_join_operation |condition_join_operation }cross_join_operation:from_itemcross_join_operator [join_hint_expr ]from_itemcondition_join_operation:from_itemcondition_join_operator [join_hint_expr ]from_itemjoin_conditioncross_join_operator:  {CROSS JOIN |, }condition_join_operator:  {[INNER] [join_method ] JOIN    |FULL [OUTER] [join_method ] JOIN    |LEFT [OUTER] [join_method ] JOIN    |RIGHT [OUTER] [join_method ] JOIN  }join_method:  { HASH }join_condition:  {on_clause |using_clause }on_clause:  ONbool_expressionusing_clause:  USING (column_list )

TheJOIN operation merges twofrom_items so that theSELECT clause canquery them as one source. The join operator and join condition specify how tocombine and discard rows from the twofrom_items to form a single source.

[INNER] JOIN

AnINNER JOIN, or simplyJOIN, effectively calculates the Cartesian productof the twofrom_items and discards all rows that don't meet the joincondition.Effectively means that it's possible to implement anINNER JOINwithout actually calculating the Cartesian product.

FROMAINNERJOINBONA.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 |                            +---------------+*/
FROMAINNERJOINBUSING(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 anINNER JOIN on theRosterandTeamMascot tables.

SELECTRoster.LastName,TeamMascot.MascotFROMRosterJOINTeamMascotONRoster.SchoolID=TeamMascot.SchoolID;/*---------------------------* | LastName   | Mascot       | +---------------------------+ | Adams      | Jaguars      | | Buchanan   | Lakers       | | Coolidge   | Lakers       | | Davis      | Knights      | *---------------------------*/

You can use acorrelatedINNER JOIN to flatten an arrayinto a set of rows. To learn more, seeConvert elements in an array to rows in a table.

CROSS JOIN

CROSS JOIN returns the Cartesian product of the twofrom_items. In otherwords, it combines each row from the firstfrom_item with each row from thesecondfrom_item.

If the rows of the twofrom_items are independent, then the result hasM * N rows, givenM rows in onefrom_item andN in the other. Note thatthis still holds for the case when eitherfrom_item has zero rows.

In aFROM clause, aCROSS JOIN can be written like this:

FROMACROSSJOINB/*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 acorrelated cross join to convert orflatten an array into a set of rows, though the (equivalent)INNER JOIN ispreferred overCROSS JOIN for this case. To learn more, seeConvert elements in an array to rows in a table.

Examples

This query performs anCROSS JOIN on theRosterandTeamMascot tables.

SELECTRoster.LastName,TeamMascot.MascotFROMRosterCROSSJOINTeamMascot;/*---------------------------* | 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 iscalled a comma cross join.

A comma cross join looks like this in aFROM clause:

FROMA,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, seeJoin operations in a sequence.

FROM(A,B)//INVALID

You can use acorrelated comma cross join to convert orflatten an array into a set of rows. To learn more, seeConvert elements in an array to rows in a table.

Examples

This query performs a comma cross join on theRosterandTeamMascot tables.

SELECTRoster.LastName,TeamMascot.MascotFROMRoster,TeamMascot;/*---------------------------* | LastName   | Mascot       | +---------------------------+ | Adams      | Jaguars      | | Adams      | Knights      | | Adams      | Lakers       | | Adams      | Mustangs     | | Buchanan   | Jaguars      | | Buchanan   | Knights      | | Buchanan   | Lakers       | | Buchanan   | Mustangs     | | ...                       | *---------------------------*/

FULL [OUTER] JOIN

AFULL OUTER JOIN (or simplyFULL JOIN) returns all fields for all matchingrows in bothfrom_items that meet the join condition. If a given row from onefrom_item doesn't join to any row in the otherfrom_item, the row returnswithNULL values for all columns from the otherfrom_item.

FROMAFULLOUTERJOINBONA.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    |                            +---------------------------+*/
FROMAFULLOUTERJOINBUSING(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 aFULL JOIN on theRosterandTeamMascot tables.

SELECTRoster.LastName,TeamMascot.MascotFROMRosterFULLJOINTeamMascotONRoster.SchoolID=TeamMascot.SchoolID;/*---------------------------* | LastName   | Mascot       | +---------------------------+ | Adams      | Jaguars      | | Buchanan   | Lakers       | | Coolidge   | Lakers       | | Davis      | Knights      | | Eisenhower | NULL         | | NULL       | Mustangs     | *---------------------------*/

LEFT [OUTER] JOIN

The result of aLEFT OUTER JOIN (or simplyLEFT JOIN) for twofrom_items always retains all rows of the leftfrom_item in theJOIN operation, even if no rows in the rightfrom_item satisfy the joinpredicate.

All rows from theleftfrom_item are retained;if a given row from the leftfrom_item doesn't join to any rowin therightfrom_item, the row will return withNULL values for allcolumns exclusively from the rightfrom_item. Rows from the rightfrom_item that don't join to any row in the leftfrom_item are discarded.

FROMALEFTOUTERJOINBONA.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    |                            +---------------------------+*/
FROMALEFTOUTERJOINBUSING(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 aLEFT JOIN on theRosterandTeamMascot tables.

SELECTRoster.LastName,TeamMascot.MascotFROMRosterLEFTJOINTeamMascotONRoster.SchoolID=TeamMascot.SchoolID;/*---------------------------* | LastName   | Mascot       | +---------------------------+ | Adams      | Jaguars      | | Buchanan   | Lakers       | | Coolidge   | Lakers       | | Davis      | Knights      | | Eisenhower | NULL         | *---------------------------*/

RIGHT [OUTER] JOIN

The result of aRIGHT OUTER JOIN (or simplyRIGHT JOIN) for twofrom_items always retains all rows of the rightfrom_item in theJOIN operation, even if no rows in the leftfrom_item satisfy the joinpredicate.

All rows from therightfrom_item are returned;if a given row from the rightfrom_item doesn't join to any rowin theleftfrom_item, the row will return withNULL values for allcolumns exclusively from the leftfrom_item. Rows from the leftfrom_itemthat don't join to any row in the rightfrom_item are discarded.

FROMARIGHTOUTERJOINBONA.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    |                            +---------------------------+*/
FROMARIGHTOUTERJOINBUSING(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 aRIGHT JOIN on theRosterandTeamMascot tables.

SELECTRoster.LastName,TeamMascot.MascotFROMRosterRIGHTJOINTeamMascotONRoster.SchoolID=TeamMascot.SchoolID;/*---------------------------* | LastName   | Mascot       | +---------------------------+ | Adams      | Jaguars      | | Buchanan   | Lakers       | | Coolidge   | Lakers       | | Davis      | Knights      | | NULL       | Mustangs     | *---------------------------*/

Join conditions

In ajoin operation, a join condition helps specify how tocombine rows in twofrom_items to form a single source.

The two types of join conditions are theON clause andUSING clause. You must use a join condition when you perform aconditional join operation. You can't use a join condition when you perform across join operation.

ON clause

ONbool_expression

Description

Given a row from each table, if theON clause evaluates toTRUE, the querygenerates a consolidated row with the result of combining the given rows.

Definitions:

  • bool_expression: The boolean expression that specifies the condition forthe join. This is frequently acomparison operation orlogical combination of comparison operators.

Details:

Similarly toCROSS JOIN,ON produces a column once for each column in eachinput table.

ANULL join condition evaluation is equivalent to aFALSE evaluation.

If a column-order sensitive operation such asUNION orSELECT * is used withtheON join condition, the resulting table contains all of the columns fromthe left input in order, and then all of the columns from the right input inorder.

Examples

The following examples show how to use theON clause:

WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4)SELECT*FROMAINNERJOINBONA.x=B.x;WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4)SELECTA.x,B.xFROMAINNERJOINBONA.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 |     +-------++---+     +---+*/
WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECTNULL),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5)SELECT*FROMALEFTOUTERJOINBONA.x=B.x;WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECTNULL),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5)SELECTA.x,B.xFROMALEFTOUTERJOINBONA.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 |+------+   +---+     +-------------+*/
WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECTNULL),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5)SELECT*FROMAFULLOUTERJOINBONA.x=B.x;WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECTNULL),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5)SELECTA.x,B.xFROMAFULLOUTERJOINBONA.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

When you are joining two tables,USING performs anequality comparison operation on the columns named incolumn_name_list. Each column name incolumn_name_list must appear in bothinput tables. For each pair of rows from the input tables, if theequality comparisons all evaluate toTRUE, one row is added to the resultingcolumn.

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

Details:

ANULL join condition evaluation is equivalent to aFALSE evaluation.

If a column-order sensitive operation such asUNION orSELECT * is usedwith theUSING join condition, the resulting table contains columns in thisorder:

  • The columns fromcolumn_name_list in the order they appear in theUSINGclause.
  • 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 theUSING clause must not be qualified by atable name.

If the join is anINNER JOIN or aLEFT OUTER JOIN, the outputcolumns are populated from the values in the first table. If thejoin is aRIGHT OUTER JOIN, the output columns are populated from the valuesin the second table. If the join is aFULL OUTER JOIN, the output columnsare populated bycoalescing the values from the left and righttables in that order.

Examples

The following example shows how to use theUSING clause with onecolumn name in the column name list:

WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT9UNIONALLSELECTNULL),BAS(SELECT2asxUNIONALLSELECT9UNIONALLSELECT9UNIONALLSELECT5)SELECT*FROMAINNERJOINBUSING(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 theUSING clause withmultiple column names in the column name list:

WITHAAS(SELECT1asx,15asyUNIONALLSELECT2,10UNIONALLSELECT9,16UNIONALLSELECTNULL,12),BAS(SELECT2asx,10asyUNIONALLSELECT9,17UNIONALLSELECT9,16UNIONALLSELECT5,15)SELECT*FROMAINNERJOINBUSING(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 theUSING clausewith one column name in the column name list:

WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT9UNIONALLSELECTNULL),BAS(SELECT2asxUNIONALLSELECT9UNIONALLSELECT9UNIONALLSELECT5)SELECTx,A.x,B.xFROMAINNERJOINBUSING(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 |     +--------------------++------+   +---+*/
WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT9UNIONALLSELECTNULL),BAS(SELECT2asxUNIONALLSELECT9UNIONALLSELECT9UNIONALLSELECT5)SELECTx,A.x,B.xFROMALEFTOUTERJOINBUSING(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 |                     +--------------------+*/
WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT2UNIONALLSELECTNULL),BAS(SELECT2asxUNIONALLSELECT9UNIONALLSELECT9UNIONALLSELECT5)SELECTx,A.x,B.xFROMARIGHTOUTERJOINBUSING(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    |                     +--------------------+*/
WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT2UNIONALLSELECTNULL),BAS(SELECT2asxUNIONALLSELECT9UNIONALLSELECT9UNIONALLSELECT5)SELECTx,A.x,B.xFROMAFULLOUTERJOINBUSING(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 theUSING clause withonly some column names in the column name list.

WITHAAS(SELECT1asx,15asyUNIONALLSELECT2,10UNIONALLSELECT9,16UNIONALLSELECTNULL,12),BAS(SELECT2asx,10asyUNIONALLSELECT9,17UNIONALLSELECT9,16UNIONALLSELECT5,15)SELECT*FROMAINNERJOINBUSING(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 anINNER JOIN on theRoster andTeamMascot table.The query returns the rows fromRoster andTeamMascot whereRoster.SchoolID is the same asTeamMascot.SchoolID. The results include asingleSchoolID column.

SELECT*FROMRosterINNERJOINTeamMascotUSING(SchoolID);/*----------------------------------------* | SchoolID   | LastName   | Mascot       | +----------------------------------------+ | 50         | Adams      | Jaguars      | | 52         | Buchanan   | Lakers       | | 52         | Coolidge   | Lakers       | | 51         | Davis      | Knights      | *----------------------------------------*/

ON andUSING equivalency

TheON andUSING join conditions aren'tequivalent, but they share some rules and sometimes they can produce similarresults.

In the following examples, observe what is returned when all rowsare produced for inner and outer joins. Also, look at howeach join condition handlesNULL values.

WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4)SELECT*FROMAINNERJOINBONA.x=B.x;WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4)SELECT*FROMAINNERJOINBUSING(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 |     +-------+     +---++---+     +---+*/
WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECTNULL),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5)SELECT*FROMALEFTOUTERJOINBONA.x=B.x;WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECTNULL),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5)SELECT*FROMALEFTOUTERJOINBUSING(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 |+------+   +---+     +-------------+     +------+*/
WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4)SELECT*FROMAFULLOUTERJOINBONA.x=B.x;WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4)SELECT*FROMAFULLOUTERJOINBUSING(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 |                    +-------------+     +---+*/

AlthoughON andUSING aren't equivalent, they can return the sameresults in some situations if you specify the columns you want to return.

In the following examples, observe what is returned when a specific rowis produced for inner and outer joins. Also, look at how eachjoin condition handlesNULL values.

WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECTNULL),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5)SELECTA.xFROMAINNERJOINBONA.x=B.x;WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECTNULL),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5)SELECTxFROMAINNERJOINBUSING(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 |+------+   +---+*/
WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECTNULL),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5)SELECTA.xFROMALEFTOUTERJOINBONA.x=B.x;WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECTNULL),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5)SELECTxFROMALEFTOUTERJOINBUSING(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 |+------+   +---+     +------+     +------+*/
WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECTNULL),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5)SELECTA.xFROMAFULLOUTERJOINBONA.x=B.x;WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECTNULL),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5)SELECTxFROMAFULLOUTERJOINBUSING(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    |                     +------+     +------+*/
WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECTNULL),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5)SELECTB.xFROMAFULLOUTERJOINBONA.x=B.x;WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECTNULL),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5)SELECTxFROMAFULLOUTERJOINBUSING(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 whenCOALESCE is usedwith theON clause. It provides the same results as a querywith theUSING clause.

WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECTNULL),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5)SELECTCOALESCE(A.x,B.x)FROMAFULLOUTERJOINBONA.x=B.x;WITHAAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECTNULL),BAS(SELECT2asxUNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5)SELECTxFROMAFULLOUTERJOINBUSING(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

TheFROM clause can contain multipleJOIN operations in a sequence.JOINs are bound from left to right. For example:

FROMAJOINBUSING(x)JOINCUSING(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 groupJOINs:

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

With parentheses, you can groupJOINs so that they are bound in a differentorder:

FROM(AJOIN(BJOINCUSING(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, theygroup from left to right like otherJOIN types:

FROMAJOINBUSING(x)JOINCUSING(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 aRIGHT JOIN orFULL JOIN after a comma cross join unlessit's parenthesized:

FROMA,BRIGHTJOINCONTRUE//INVALID
FROMA,BFULLJOINCONTRUE//INVALID
FROMA,BJOINCONTRUE//VALID
FROMA,(BRIGHTJOINCONTRUE)//VALID
FROMA,(BFULLJOINCONTRUE)//VALID

Correlated join operation

A join operation iscorrelated when the rightfrom_item contains areference to at least one range variable orcolumn name introduced by the leftfrom_item.

In a correlated join operation, rows from the rightfrom_item are determinedby a row from the leftfrom_item. Consequently,RIGHT OUTER andFULL OUTERjoins can't be correlated because rightfrom_item rows can't be determinedin the case when there is no row from the leftfrom_item.

All correlated join operations must reference an array in the rightfrom_item.

This is a conceptual example of a correlated join operation that includesacorrelated subquery:

FROMAJOINUNNEST(ARRAY(SELECTASSTRUCT*FROMBWHEREA.ID=B.ID))ASC
  • Leftfrom_item:A
  • Rightfrom_item:UNNEST(...) AS C
  • A correlated subquery:(SELECT AS STRUCT * FROM B WHERE A.ID = B.ID)

This is another conceptual example of a correlated join operation.array_of_IDs is part of the leftfrom_item but is referenced in therightfrom_item.

FROMAJOINUNNEST(A.array_of_IDs)ASC

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

FROMAJOINUNNEST(A.array_of_IDs)ASIDs
FROMAJOINA.array_of_IDsASIDs

In a correlated join operation, the rightfrom_item is re-evaluatedagainst each distinct row from the leftfrom_item. In the followingconceptual example, the correlated join operation firstevaluatesA andB, thenA andC:

FROMAJOINUNNEST(ARRAY(SELECTASSTRUCT*FROMBWHEREA.ID=B.ID))ASCONA.Name=C.Name

Caveats

  • In a correlatedLEFT JOIN, when the input table on the right side is emptyfor some row from the left side, it's as if no rows from the right sidesatisfied the join condition in a regularLEFT JOIN. When there are nojoining rows, a row withNULL values for all columns on the right side isgenerated to join with the row from the left side.
  • In a correlatedCROSS JOIN, when the input table on the right side isempty for some row from the left side, it's as if no rows from the rightside satisfied the join condition in a regular correlatedINNER JOIN. Thismeans that the row is dropped from the results.

Examples

This is an example of a correlated join, using theRoster andPlayerStats tables:

SELECT*FROMRosterJOINUNNEST(ARRAY(SELECTASSTRUCT*FROMPlayerStatsWHEREPlayerStats.OpponentID=Roster.SchoolID))ASPlayerMatchesONPlayerMatches.LastName='Buchanan'/*------------+----------+----------+------------+--------------* | LastName   | SchoolID | LastName | OpponentID | PointsScored | +------------+----------+----------+------------+--------------+ | Adams      | 50       | Buchanan | 50         | 13           | | Eisenhower | 77       | Buchanan | 77         | 0            | *------------+----------+----------+------------+--------------*/

A common pattern for a correlatedLEFT JOIN is to have anUNNEST operationon the right side that references an array from some column introduced byinput on the left side. For rows where that array is empty orNULL,theUNNEST operation produces no rows on the right input. In that case, a rowwith aNULL entry in each column of the right input is created to join withthe row from the left input. For example:

SELECTA.name,item,ARRAY_LENGTH(A.items)item_count_for_nameFROMUNNEST([STRUCT('first'ASname,[1,2,3,4]ASitems),STRUCT('second'ASname,[]ASitems)])ASALEFTJOINA.itemsASitem;/*--------+------+---------------------* | 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 correlatedINNER JOIN orCROSS JOIN, when the input on theright side is empty for some row from the left side, the final row is droppedfrom the results. For example:

SELECTA.name,itemFROMUNNEST([STRUCT('first'ASname,[1,2,3,4]ASitems),STRUCT('second'ASname,[]ASitems)])ASAINNERJOINA.itemsASitem;/*-------+------* | name  | item | +-------+------+ | first | 1    | | first | 2    | | first | 3    | | first | 4    | *-------+------*/

WHERE clause

WHERE bool_expression

TheWHERE clause filters the results of theFROM clause.

Only rows whosebool_expression evaluates toTRUE are included. Rowswhosebool_expression evaluates toNULL orFALSE arediscarded.

The evaluation of a query with aWHERE clause is typically completed in thisorder:

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

Evaluation order doesn't always match syntax order.

TheWHERE clause only references columns available via theFROM clause;it can't referenceSELECT list aliases.

Examples

This query returns returns all rows from theRoster tablewhere theSchoolID column has the value52:

SELECT*FROMRosterWHERESchoolID=52;

Thebool_expression can contain multiple sub-conditions:

SELECT*FROMRosterWHERESTARTS_WITH(LastName,"Mc")ORSTARTS_WITH(LastName,"Mac");

Expressions in anINNER JOIN have an equivalent expression in theWHERE clause. For example, a query usingINNERJOIN andON has anequivalent expression usingCROSS JOIN andWHERE. For example,the following two queries are equivalent:

SELECTRoster.LastName,TeamMascot.MascotFROMRosterINNERJOINTeamMascotONRoster.SchoolID=TeamMascot.SchoolID;
SELECTRoster.LastName,TeamMascot.MascotFROMRosterCROSSJOINTeamMascotWHERERoster.SchoolID=TeamMascot.SchoolID;

GROUP BY clause

GROUP BYgroupable_items

Description

TheGROUP BY clause groups together rows in a table that share common valuesfor certain columns. For a group of rows in the source table withnon-distinct values, theGROUP BY clause aggregates them into a singlecombined row. This clause is commonly used when aggregate functions arepresent in theSELECT list, or to eliminate redundancy in the output.

Definitions

Group rows by groupable items

GROUP BYgroupable_item[, ...]groupable_item:  {value    |value_alias    |column_ordinal  }

Description

TheGROUP BY clause can includegroupable expressionsand their ordinals.

Definitions

Group rows by values

TheGROUP BY clause can group rows in a table with non-distinctvalues in theGROUP BY clause. For example:

WITHPlayerStatsAS(SELECT'Adams'asLastName,'Noam'asFirstName,3asPointsScoredUNIONALLSELECT'Buchanan','Jie',0UNIONALLSELECT'Coolidge','Kiran',1UNIONALLSELECT'Adams','Noam',4UNIONALLSELECT'Buchanan','Jie',13)SELECTSUM(PointsScored)AStotal_points,LastNameFROMPlayerStatsGROUPBYLastName;/*--------------+----------+ | total_points | LastName | +--------------+----------+ | 7            | Adams    | | 13           | Buchanan | | 1            | Coolidge | +--------------+----------*/

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

WITHPlayerStatsAS(SELECT'Adams'asLastName,'Noam'asFirstName,3asPointsScoredUNIONALLSELECT'Buchanan','Jie',0UNIONALLSELECT'Coolidge','Kiran',1UNIONALLSELECT'Adams','Noam',4UNIONALLSELECT'Buchanan','Jie',13)SELECTSUM(PointsScored)AStotal_points,LastNameASlast_nameFROMPlayerStatsGROUPBYlast_name;/*--------------+-----------+ | total_points | last_name | +--------------+-----------+ | 7            | Adams     | | 13           | Buchanan  | | 1            | Coolidge  | +--------------+-----------*/

To learn more about the data types that are supported for values in theGROUP BY clause, seeGroupable data types.

Group rows by column ordinals

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

WITHPlayerStatsAS(SELECT'Adams'asLastName,'Noam'asFirstName,3asPointsScoredUNIONALLSELECT'Buchanan','Jie',0UNIONALLSELECT'Coolidge','Kiran',1UNIONALLSELECT'Adams','Noam',4UNIONALLSELECT'Buchanan','Jie',13)SELECTSUM(PointsScored)AStotal_points,LastName,FirstNameFROMPlayerStatsGROUPBYLastName,FirstName;/*--------------+----------+-----------+ | total_points | LastName | FirstName | +--------------+----------+-----------+ | 7            | Adams    | Noam      | | 13           | Buchanan | Jie       | | 1            | Coolidge | Kiran     | +--------------+----------+-----------*/
WITHPlayerStatsAS(SELECT'Adams'asLastName,'Noam'asFirstName,3asPointsScoredUNIONALLSELECT'Buchanan','Jie',0UNIONALLSELECT'Coolidge','Kiran',1UNIONALLSELECT'Adams','Noam',4UNIONALLSELECT'Buchanan','Jie',13)SELECTSUM(PointsScored)AStotal_points,LastName,FirstNameFROMPlayerStatsGROUPBY2,3;/*--------------+----------+-----------+ | total_points | LastName | FirstName | +--------------+----------+-----------+ | 7            | Adams    | Noam      | | 13           | Buchanan | Jie       | | 1            | Coolidge | Kiran     | +--------------+----------+-----------*/

HAVING clause

HAVING bool_expression

TheHAVING clause filters the results produced byGROUP BY oraggregation.GROUP BY or aggregation must be present in the query. Ifaggregation is present, theHAVING clause is evaluated once for everyaggregated row in the result set.

Only rows whosebool_expression evaluates toTRUE are included. Rowswhosebool_expression evaluates toNULL orFALSE arediscarded.

The evaluation of a query with aHAVING clause is typically completed in thisorder:

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

Evaluation order doesn't always match syntax order.

TheHAVING clause references columns available via theFROM clause, aswell asSELECT list aliases. Expressions referenced in theHAVING clausemust either appear in theGROUP BY clause or they must be the result of anaggregate function:

SELECTLastNameFROMRosterGROUPBYLastNameHAVINGSUM(PointsScored) >15;

If a query contains aliases in theSELECT clause, those aliases override namesin aFROM clause.

SELECTLastName,SUM(PointsScored)ASpsFROMRosterGROUPBYLastNameHAVINGps >0;

Mandatory aggregation

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

Aggregation function in theSELECT list.

SELECTLastName,SUM(PointsScored)AStotalFROMPlayerStatsGROUPBYLastNameHAVINGtotal >15;

Aggregation function in theHAVING clause.

SELECTLastNameFROMPlayerStatsGROUPBYLastNameHAVINGSUM(PointsScored) >15;

Aggregation in both theSELECT list andHAVING clause.

When aggregation functions are present in both theSELECT list andHAVINGclause, the aggregation functions and the columns they reference don't needto be the same. In the example below, the two aggregation functions,COUNT() andSUM(), are different and also use different columns.

SELECTLastName,COUNT(*)FROMPlayerStatsGROUPBYLastNameHAVINGSUM(PointsScored) >15;

ORDER BY clause

ORDER BY expression  [COLLATE collation_specification]  [{ ASC | DESC }]  [, ...]collation_specification:  language_tag[:collation_attribute]

TheORDER BY clause specifies a column or expression as the sort criterion forthe result set. If anORDER BY clause isn't present, the order of the resultsof a query isn't defined. Column aliases from aFROM clause orSELECT listare allowed. If a query contains aliases in theSELECT clause, those aliasesoverride names in the correspondingFROM clause. The data type ofexpression must beorderable.

Optional Clauses

  • COLLATE: You can use theCOLLATE clause to refine how data is orderedby anORDER BY clause.Collation refers to a set of rules that determinehow strings are compared according to the conventions andstandards of a particular written language, region, or country. These rulesmight define the correct character sequence, with options for specifyingcase-insensitivity. You can useCOLLATE only on columns of typeSTRING.

    collation_specification represents the collation specification for theCOLLATE clause. The collation specification can be a string literal ora query parameter. To learn more seecollation specification details.

  • ASC | DESC: Sort the results in ascending or descendingorder ofexpression values.ASC is thedefault value.

Examples

Use the default sort order (ascending).

SELECTx,yFROM(SELECT1ASx,trueASyUNIONALLSELECT9,true)ORDERBYx;/*------+-------* | x    | y     | +------+-------+ | 1    | true  | | 9    | true  | *------+-------*/

Use descending sort order.

SELECTx,yFROM(SELECT1ASx,trueASyUNIONALLSELECT9,true)ORDERBYxDESC;/*------+-------* | x    | y     | +------+-------+ | 9    | true  | | 1    | true  | *------+-------*/

It's possible to order by multiple columns. In the example below, the resultset is ordered first bySchoolID and then byLastName:

SELECTLastName,PointsScored,OpponentIDFROMPlayerStatsORDERBYSchoolID,LastName;

When used in conjunction withset operators,theORDER BY clause applies to the result set of the entire query; it doesn'tapply only to the closestSELECT statement. For this reason, it can be helpful(though it isn't required) to use parentheses to show the scope of theORDERBY.

This query without parentheses:

SELECT*FROMRosterUNIONALLSELECT*FROMTeamMascotORDERBYSchoolID;

is equivalent to this query with parentheses:

(SELECT*FROMRosterUNIONALLSELECT*FROMTeamMascot)ORDERBYSchoolID;

but isn't equivalent to this query, where theORDER BY clause applies only tothe secondSELECT statement:

SELECT*FROMRosterUNIONALL(SELECT*FROMTeamMascotORDERBYSchoolID);

You can also use integer literals as column references inORDER BY clauses. Aninteger literal becomes an ordinal (for example, counting starts at 1) intotheSELECT list.

Example - the following two queries are equivalent:

SELECTSUM(PointsScored),LastNameFROMPlayerStatsGROUPBYLastNameORDERBYLastName;
SELECTSUM(PointsScored),LastNameFROMPlayerStatsGROUPBY2ORDERBY2;

Collate results using English - Canada:

SELECTPlaceFROMLocationsORDERBYPlaceCOLLATE"en_CA"

Collate results using a parameter:

#@collate_param = "arg_EG"SELECTPlaceFROMLocationsORDERBYPlaceCOLLATE@collate_param

Using multipleCOLLATE clauses in a statement:

SELECTAPlace,BPlace,CPlaceFROMLocationsORDERBYAPlaceCOLLATE"en_US"ASC,BPlaceCOLLATE"ar_EG"DESC,CPlaceCOLLATE"en"DESC

Case insensitive collation:

SELECTPlaceFROMLocationsORDERBYPlaceCOLLATE"en_US:ci"

Default Unicode case-insensitive collation:

SELECTPlaceFROMLocationsORDERBYPlaceCOLLATE"und:ci"

Set operators

query_expr    {UNION { ALL | DISTINCT } |INTERSECT { ALL | DISTINCT } |EXCEPT { ALL | DISTINCT }  }query_expr

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

Definitions

  • query_expr: One of two input queries whose results are combined or filteredinto 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 andright input queries.
  • EXCEPT: Returns rows from the left input query that aren't present in theright input query.
  • ALL: Executes the set operation on all rows.
  • DISTINCT: Excludes duplicate rows in the set operation.

Positional column matching

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

Other column-related rules

  • For set operations other thanUNIONALL, all column types must supportequality comparison.
  • The results of the set operation always use the column names from thefirst input query.
  • The results of the set operation always use the supertypes of input typesin corresponding columns, so paired columns must also have either the samedata type or a common supertype.

Parenthesized set operators

  • Parentheses must be used to separate different set operations.Set operations likeUNION ALL andUNION DISTINCT are considered different.
  • Parentheses are also used to group set operations and control order ofoperations. InEXCEPT set operations, for example,query results can vary depending on the operation grouping.

The following examples illustrate the use of parentheses with setoperations:

-- Same set operations, no parentheses.query1UNIONALLquery2UNIONALLquery3;
-- Different set operations, parentheses needed.query1UNIONALL(query2UNIONDISTINCTquery3);
-- Invalidquery1UNIONALLquery2UNIONDISTINCTquery3;
-- Same set operations, no parentheses.query1EXCEPTALLquery2EXCEPTALLquery3;-- Equivalent query with optional parentheses, returns same results.(query1EXCEPTALLquery2)EXCEPTALLquery3;
-- Different execution order with a subquery, parentheses needed.query1EXCEPTALL(query2EXCEPTALLquery3);

Set operator behavior with duplicate rows

Consider a given rowR that appears exactlym times in the first input queryandn times in the second input query, wherem >= 0 andn >= 0:

  • ForUNION ALL, rowR appears exactlym + n times in theresult.
  • ForINTERSECT ALL, rowR appears exactlyMIN(m, n) times in theresult.
  • ForEXCEPT ALL, rowR appears exactlyMAX(m - n, 0) times in theresult.
  • ForUNION DISTINCT, theDISTINCTis computed after theUNION is computed, so rowR appears exactlyone time.
  • ForINTERSECT DISTINCT, rowR appears once in the output ifm > 0 andn > 0.
  • ForEXCEPT DISTINCT, rowR appears once in the output ifm > 0 andn = 0.
  • If more than two input queries are used, the above operations generalizeand the output is the same as if the input queries were combinedincrementally from left to right.

UNION

TheUNION operator returns the combined results of the left and right inputqueries. Columns are matched according to the rules described previously androws are concatenated vertically.

Examples

SELECT*FROMUNNEST(ARRAY<INT64>[1,2,3])ASnumberUNIONALLSELECT1;/*--------+ | number | +--------+ | 1      | | 2      | | 3      | | 1      | +--------*/
SELECT*FROMUNNEST(ARRAY<INT64>[1,2,3])ASnumberUNIONDISTINCTSELECT1;/*--------+ | number | +--------+ | 1      | | 2      | | 3      | +--------*/

The following example shows multiple chained operators:

SELECT*FROMUNNEST(ARRAY<INT64>[1,2,3])ASnumberUNIONDISTINCTSELECT1UNIONDISTINCTSELECT2;/*--------+ | number | +--------+ | 1      | | 2      | | 3      | +--------*/

INTERSECT

TheINTERSECT operator returns rows that are found in the results of both theleft and right input queries.

Examples

SELECT*FROMUNNEST(ARRAY<INT64>[1,2,3,3,4])ASnumberINTERSECTALLSELECT*FROMUNNEST(ARRAY<INT64>[2,3,3,5])ASnumber;/*--------+ | number | +--------+ | 2      | | 3      | | 3      | +--------*/
SELECT*FROMUNNEST(ARRAY<INT64>[1,2,3,3,4])ASnumberINTERSECTDISTINCTSELECT*FROMUNNEST(ARRAY<INT64>[2,3,3,5])ASnumber;/*--------+ | number | +--------+ | 2      | | 3      | +--------*/

The following example shows multiple chained operations:

SELECT*FROMUNNEST(ARRAY<INT64>[1,2,3,3,4])ASnumberINTERSECTDISTINCTSELECT*FROMUNNEST(ARRAY<INT64>[2,3,3,5])ASnumberINTERSECTDISTINCTSELECT*FROMUNNEST(ARRAY<INT64>[3,3,4,5])ASnumber;/*--------+ | number | +--------+ | 3      | +--------*/

EXCEPT

TheEXCEPT operator returns rows from the left input query that aren't presentin the right input query.

Examples

SELECT*FROMUNNEST(ARRAY<INT64>[1,2,3,3,4])ASnumberEXCEPTALLSELECT*FROMUNNEST(ARRAY<INT64>[1,2])ASnumber;/*--------+ | number | +--------+ | 3      | | 3      | | 4      | +--------*/
SELECT*FROMUNNEST(ARRAY<INT64>[1,2,3,3,4])ASnumberEXCEPTDISTINCTSELECT*FROMUNNEST(ARRAY<INT64>[1,2])ASnumber;/*--------+ | number | +--------+ | 3      | | 4      | +--------*/

The following example shows multiple chained operations:

SELECT*FROMUNNEST(ARRAY<INT64>[1,2,3,3,4])ASnumberEXCEPTDISTINCTSELECT*FROMUNNEST(ARRAY<INT64>[1,2])ASnumberEXCEPTDISTINCTSELECT*FROMUNNEST(ARRAY<INT64>[1,4])ASnumber;/*--------+ | number | +--------+ | 3      | +--------*/

The following example modifies the execution behavior of the set operations. Thefirst input query is used against the result of the last two input queriesinstead of the values of the last two queries individually. In this example,theEXCEPT result of the last two input queries is2. Therefore, theEXCEPT results of the entire query are any values other than2 in the firstinput query.

SELECT*FROMUNNEST(ARRAY<INT64>[1,2,3,3,4])ASnumberEXCEPTDISTINCT(SELECT*FROMUNNEST(ARRAY<INT64>[1,2])ASnumberEXCEPTDISTINCTSELECT*FROMUNNEST(ARRAY<INT64>[1,4])ASnumber);/*--------+ | number | +--------+ | 1      | | 3      | | 4      | +--------*/

LIMIT andOFFSET clause

LIMITcount[OFFSETskip_rows]

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

Definitions

  • LIMIT: Limits the number of rows to produce.

    count is anINT64 constant expression that represents thenon-negative, non-NULL limit. No more thancount rows are produced.LIMIT 0 returns 0 rows.

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

  • OFFSET: Skips a specific number of rows before applyingLIMIT.

    skip_rows is anINT64 constant expression that representsthe non-negative, non-NULL number of rows to skip.

Details

The rows that are returned byLIMIT andOFFSET have undefined order unlessthese clauses are used afterORDER BY.

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

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

Examples

SELECT*FROMUNNEST(ARRAY<STRING>['a','b','c','d','e'])ASletterORDERBYletterASCLIMIT2;/*---------* | letter  | +---------+ | a       | | b       | *---------*/
SELECT*FROMUNNEST(ARRAY<STRING>['a','b','c','d','e'])ASletterORDERBYletterASCLIMIT3OFFSET1;/*---------* | letter  | +---------+ | b       | | c       | | d       | *---------*/

FOR UPDATE clause

SELECTexpressionFORUPDATE;UPDATEexpression;

When you use theSELECT query to scan a table, add aFOR UPDATE clause toenable exclusive locks at the row-and-column granularitylevel, otherwise known as cell-level. The lockremains in place for the lifetime of the read-write transaction. During thistime, theFOR UPDATE clause prevents other transactions from modifying thelocked cells until the current transaction completes.

Example:

SELECTMarketingBudgetFROMAlbumsWHERESingerId=1andAlbumId=1FORUPDATE;UPDATEAlbumsSETMarketingBudget=100000WHERESingerId=1andAlbumId=1;

You can't use theFOR UPDATE clause in the following ways:

For more information, seeUse SELECT FOR UPDATE.

WITH clause

WITHcte[, ...]

AWITH clause contains one or more common table expressions (CTEs).A CTE acts like a temporary table that you can reference within a singlequery expression. Each CTE binds the results of asubqueryto a table name, which can be used elsewhere in the same query expression,butrules apply.

CTEs

cte:cte_name AS (query_expr )

A common table expression (CTE) containsasubqueryand a name associated with the CTE.

  • A CTE can't reference itself.
  • A CTE can be referenced by the query expression thatcontains theWITH clause, butrules apply.
Examples

In this example, aWITH clause defines two CTEs thatare referenced in the related set operation, where one CTE is referenced byeach of the set operation's input query expressions:

WITHsubQ1AS(SELECTSchoolIDFROMRoster),subQ2AS(SELECTOpponentIDFROMPlayerStats)SELECT*FROMsubQ1UNIONALLSELECT*FROMsubQ2

WITH isn't supportedin a subquery. This returns an error:

SELECTaccountFROM(WITHresultAS(SELECT*FROMNPCs)SELECT*FROMresult)

You can use aFOR UPDATE clause in a CTE subquery to lock the scannedrange of the subquery.

The following query exclusively lockscol1 andcol2 in tablet.

WITHt1AS(SELECTcol1,col2FROMtFORUPDATE)SELECT*FROMt1;

However, aFOR UPDATE clause in the outer query won't propagate into the CTE.In the following example, an exclusive lock won't apply to any cells in tablet.

WITHt2AS(SELECTcol1,col2FROMt)SELECT*FROMt2FORUPDATE;

WITH clause isn't supported in DML statements.

Temporary tables defined by theWITH clause are stored in memory.Spanner dynamically allocates memory for all temporary tablescreated by a query. If the available resources aren't sufficient then the querywill fail.

CTE rules and constraints

Common table expressions (CTEs) can be referenced inside the query expressionthat contains theWITH clause.

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

  • Each CTE in the sameWITH clause must have a unique name.
  • A CTE defined in aWITH clause is only visible to other CTEs in the sameWITH clause that were defined after it.
  • 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.

CTE visibility

References between common table expressions (CTEs) in theWITH clause can gobackward but not forward.

This is what happens when you have two CTEs that referencethemselves or each other in aWITH clause. Assume thatA is the first CTE andBis 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-referencesaren't supported:

WITHAAS(SELECT1ASnUNIONALL(SELECTn+1FROMAWHEREn <3))SELECT*FROMA-- Error

This produces an error.A can't referenceB because references betweenCTEs can go backwards but not forwards:

WITHAAS(SELECT*FROMB),BAS(SELECT1ASn)SELECT*FROMB-- Error

B can referenceA because references between CTEs can go backwards:

WITHAAS(SELECT1ASn),BAS(SELECT*FROMA)SELECT*FROMB/*---* | n | +---+ | 1 | *---*/

This produces an error.A andB reference each other, which creates acycle:

WITHAAS(SELECT*FROMB),BAS(SELECT*FROMA)SELECT*FROMB-- Error

Using aliases

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

Explicit aliases

You can introduce explicit aliases in either theFROM clause or theSELECTlist.

In aFROM clause, you can introduce explicit aliases for any item, includingtables, arrays, subqueries, andUNNEST clauses, using[AS] alias. TheASkeyword is optional.

Example:

SELECTs.FirstName,s2.SongNameFROMSingersASs,(SELECT*FROMSongs)ASs2;

You can introduce explicit aliases for any expression in theSELECT list using[AS] alias. TheAS keyword is optional.

Example:

SELECTs.FirstNameASname,LOWER(s.FirstName)ASlnameFROMSingerss;

Implicit aliases

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

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

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

In aFROM clause,from_items aren't required to have an alias. Thefollowing 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 impliesAS abc.
    • For path expressions, the alias is the last identifier in the path. For example,FROM abc.def.ghi impliesAS ghi
    • The column produced usingWITH OFFSET has the implicit aliasoffset.
  • 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 onwhere else in the query you can reference that alias. These restrictions onalias visibility are the result of GoogleSQL name scoping rules.

Visibility in theFROM clause

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

Example:

Assume theSingers table had aConcerts column ofARRAY type.

SELECTFirstNameFROMSingersASs,s.Concerts;

Invalid:

SELECTFirstNameFROMs.Concerts,SingersASs;//INVALID.

FROM clause aliases arenot visible to subqueries in the sameFROMclause. Subqueries in aFROM clause can't contain correlated references toother tables in the sameFROM clause.

Invalid:

SELECTFirstNameFROMSingersASs,(SELECT(2020-ReleaseDate)FROMs)//INVALID.

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

Example:

SELECTFirstName,s.ReleaseDateFROMSingerssWHEREReleaseDate=1975;

If theFROM clause contains an explicit alias, you must use the explicit aliasinstead of the implicit alias for the remainder of the query (seeImplicit Aliases). A table alias is useful for brevity orto eliminate ambiguity in cases such as self-joins, where the same table isscanned multiple times during query processing.

Example:

SELECT*FROMSingersass,Songsass2ORDERBYs.LastName

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

SELECT*FROMSingersass,Songsass2ORDERBYSingers.LastName;//INVALID.

Visibility in theSELECT list

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

  • GROUP BY clause
  • ORDER BY clause
  • HAVING clause

Example:

SELECTLastNameASlast,SingerIDFROMSingersORDERBYlast;

Visibility in theGROUP BY,ORDER BY, andHAVING clauses

These three clauses,GROUP BY,ORDER BY, andHAVING, can refer to only thefollowing values:

  • Tables in theFROM clause and any of their columns.
  • Aliases from theSELECT list.

GROUP BY andORDER BY can also refer to a third group:

  • Integer literals, which refer to items in theSELECT list. The integer1refers to the first item in theSELECT list,2 refers to the second item,etc.

Example:

SELECTSingerIDASsid,COUNT(Songid)ASs2idFROMSongsGROUPBY1ORDERBY2DESC;

The previous query is equivalent to:

SELECTSingerIDASsid,COUNT(Songid)ASs2idFROMSongsGROUPBYsidORDERBYs2idDESC;

Duplicate aliases

ASELECT list or subquery containing multiple explicit or implicit aliasesof the same name is allowed, as long as the alias name isn't referencedelsewhere in the query, since the reference would beambiguous.

Example:

SELECT1ASa,2ASa;/*---+---* | a | a | +---+---+ | 1 | 2 | *---+---*/

Ambiguous aliases

GoogleSQL provides an error if accessing a name is ambiguous, meaningit 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, sincebothSingers andSongs have a column namedSingerID:

SELECTSingerIDFROMSingers,Songs;

The following query contains aliases that are ambiguous in theGROUP BY clausebecause they are duplicated in theSELECT list:

SELECTFirstNameASname,LastNameASname,FROMSingersGROUPBYname;

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

  • Assume thePerson table has three columns:FirstName,LastName, andPrimaryContact.
  • Assume thePrimaryContact column represents a struct with these fields:FirstName andLastName.

The aliasP is ambiguous and will produce an error becauseP.FirstName intheGROUP BY clause could refer to eitherPerson.FirstName orPerson.PrimaryContact.FirstName.

SELECTFirstName,LastName,PrimaryContactASPFROMPersonASPGROUPBYP.FirstName;

A name isnot ambiguous inGROUP BY,ORDER BY orHAVING if it's botha column name and aSELECT list alias, as long as the name resolves to thesame underlying object. In the following example, the aliasBirthYear isn'tambiguous because it resolves to the same underlying column,Singers.BirthYear.

SELECTLastName,BirthYearASBirthYearFROMSingersGROUPBYBirthYear;

Range variables

In GoogleSQL, a range variable is a table expression alias in theFROM clause. Sometimes a range variable is known as atable alias. Arange variable lets you reference rows being scanned from a table expression.A table expression represents an item in theFROM clause that returns a table.Common items that this expression can represent includetables,value tables,subqueries,joins, andparenthesized joins.

In general, a range variable provides a reference to the rows of a tableexpression. A range variable can be used to qualify a column reference andunambiguously identify the related table, for examplerange_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 relatedto value tables, the result type is the value table's row type. Other tablesdon't have explicit row types, and for those tables, the range variabletype is a dynamically defined struct that includes all of thecolumns in the table.

Examples

In these examples, theWITH clause is used to emulate a temporary tablecalledGrid. This table has columnsx andy. A range variable calledCoordinate refers to the current row as the table is scanned.Coordinatecan be used to access the entire row or columns in the row.

The following example selects columnx from range variableCoordinate,which in effect selects columnx from tableGrid.

WITHGridAS(SELECT1x,2y)SELECTCoordinate.xFROMGridASCoordinate;/*---* | x | +---+ | 1 | *---*/

The following example selects all columns from range variableCoordinate,which in effect selects all columns from tableGrid.

WITHGridAS(SELECT1x,2y)SELECTCoordinate.*FROMGridASCoordinate;/*---+---* | x | y | +---+---+ | 1 | 2 | *---+---*/

The following example selects the range variableCoordinate, which is areference to rows in tableGrid. SinceGrid isn't a value table,the result type ofCoordinate is a struct that contains all the columnsfromGrid.

WITHGridAS(SELECT1x,2y)SELECTCoordinateFROMGridASCoordinate;/*--------------* | Coordinate   | +--------------+ | {x: 1, y: 2} | *--------------*/

Hints

@{hint_key=hint_value[, ...]}

GoogleSQL supports hints, which make the query optimizer use aspecific operator in the execution plan. If performance is an issue for you,a hint might be able to help by suggesting a differentquery execution plan shape.

Definitions

  • hint_key: The name of the hint key.
  • hint_value: The value forhint_key.

Examples

@{KEY_ONE=TRUE}
@{KEY_TWO=10,KEY_THREE=FALSE}

Statement hints

The following query statement hints are supported:

Hint keyPossible valuesDescription
USE_ADDITIONAL_PARALLELISMTRUE
|FALSE (default)		 IfTRUE, the execution engine favors using more parallelism when possible. Because this can reduce resources available to other operations, you may want to avoid this hint if you run latency-sensitive operations on the same instance.
OPTIMIZER_VERSION1 toN
|latest_version
|default_version

Executes the query using the specified optimizer version. Possible values are1 toN (the latest optimizer version),default_version, orlatest_version. If the hint isn't set, the optimizer executes against the package that's set in database options or specified through the client API. If neither of those are set, the optimizer uses thedefault version.

In terms of version setting precedence, the value set by the client API takes precedence over the value in the database options and the value set by this hint takes precedence over everything else.

For more information, seeQuery optimizer.

OPTIMIZER_STATISTICS_PACKAGEpackage_name
|latest

Executes the query using the specified optimizer statistics package. Possible values forpackage_name can be found by running the following query:

SELECT * FROM INFORMATION_SCHEMA.SPANNER_STATISTICS

If the hint isn't set, the optimizer executes against the package that's set in the database option or specified through the client API. If neither of those are set, the optimizer defaults to the latest package.

The value set by the client API takes precedence over the value in the database options and the value set by this hint takes precedence over everything else.

The specified package needs to be pinned by the database option or haveallow_gc=false to prevent garbage collection.

For more information, seeQuery optimizer statistics packages.

ALLOW_DISTRIBUTED_MERGETRUE (default)
|FALSE

IfTRUE (default), the engine favors using a distributed merge sort algorithm for certain ORDER BY queries. When applicable, global sorts are changed to local sorts. This gives the advantage of parallel sorting close to where the data is stored. The locally sorted data is then merged to provide globally sorted data. This allows for removal of full global sorts and potentially improved latency.

This feature can increase parallelism of certain ORDER BY queries. This hint has been provided so that users can experiment with turning off the distributed merge algorithm if desired.

LOCK_SCANNED_RANGESexclusive
|shared (default)

Use this hint to request an exclusive lock on a set of ranges scanned by a transaction. Acquiring an exclusive lock helps in scenarios when you observe high write contention, that is, you notice that multiple transactions are concurrently trying to read and write to the same data, resulting in a large number of aborts.

Without the hint, it's possible that multiple simultaneous transactions will acquire shared locks, and then try to upgrade to exclusive locks. This will cause a deadlock, because each transaction's shared lock is preventing the other transaction(s) from upgrading to exclusive. Spanner aborts all but one of the transactions.

When requesting an exclusive lock using this hint, one transaction acquires the lock and proceeds to execute, while other transactions wait their turn for the lock. Throughput is still limited because the conflicting transactions can only be performed one at a time, but in this case Spanner is always making progress on one transaction, saving time that would otherwise be spent aborting and retrying transactions.

This hint is supported on all statement types, both query and DML.

Spanner always enforcesserializability Lock mode hints can affect which transactions wait or abort in contended workloads, but don't change the isolation level.

Because this is just a hint, it shouldn't be considered equivalent to a mutex. In other words, you shouldn't use Spanner exclusive locks as a mutual exclusion mechanism for the execution of code outside of Spanner. For more information, seeLocking.

You can't use both theFOR UPDATE clause and theLOCK_SCANNED_RANGES hint in the same query. An error is returned. For more information, see.

SCAN_METHODAUTO (default)
|BATCH
|ROW		 Use this hint to enforce the query scan method.

The default Spanner scan method isAUTO (automatic). TheAUTO setting specifies that batch-oriented query processing might be used to improve query performance. If you want to change the default scanning method, you can use a statement hint to enforce theBATCH-oriented orROW-oriented processing method. You can't manually set the scan method toAUTO; to do so, remove the statement hint, and Spanner will set it to the default method. For more information, seeOptimize scans.

EXECUTION_METHODDEFAULT
|BATCH
|ROW		 Use this hint to enforce the query execution method.

The default Spanner query execution method isDEFAULT. TheDEFAULT setting specifies that batch-oriented execution might be used to improve query performance, depending on the heuristics of the query. If you want to change the default execution method, you can use a statement hint to enforce theBATCH-oriented orROW-oriented execution method. You can't manually set the query execution method toDEFAULT; to do so, remove the statement hint, and Spanner will set it to the default method. For more information, seeOptimize query execution.

USE_UNENFORCED_FOREIGN_KEYTRUE (default)
|FALSE		 Use this hint to enforce the query scan method.

IfTRUE (default), the query optimizer relies oninformational (NOT ENFORCED) foreign key relationships to improve query performance. For example, if set toTRUE, the optimizer can remove redundant scans, and push someLIMIT operators through the join operators.USE_UNENFORCED_FOREIGN_KEY overrides the value of theuse_unenforced_foreign_key_for_query_optimization database option for the applied statement. This hint might introduce incorrect results if the data is inconsistent with the foreign key relationships.

For more information, seeinformational foreign keys.

ALLOW_TIMESTAMP_PREDICATE_PUSHDOWNTRUE
|FALSE (default)		 If set toTRUE, the query execution engine uses the timestamp predicate pushdown optimization technique. This technique improves the efficiency of queries that use timestamps and data with an age-based tiered storage policy. For more information, seeOptimize queries with timestamp predicate pushdown.

Table hints

The following table hints are supported:

Hint keyPossible valuesDescription
FORCE_INDEXSTRING
  • If set to the name of an index, use that index instead of the base table. If the index can't provide all needed columns, perform a back join with the base table.
  • If set to the string_BASE_TABLE, use the base table for the index strategy instead of an index. Note that this is the only valid value whenFORCE_INDEX is used in a statement hint expression.

Note:FORCE_INDEX is actually a directive, not a hint, which means an error is raised if the index doesn't exist.

GROUPBY_SCAN_OPTIMIZATIONTRUE
|FALSE

The group by scan optimization can make queries faster if they useGROUP BY orSELECT DISTINCT. It can be applied if the grouping keys can form a prefix of the underlying table or index key, and if the query requires only the first row from each group.

The optimization is applied if the optimizer estimates that it will make the query more efficient. The hint overrides that decision. If the hint is set toFALSE, the optimization isn't considered. If the hint is set toTRUE, the optimization will be applied as long as it's legal to do so.

SCAN_METHODAUTO (default)
|BATCH
|ROW		 Use this hint to enforce the query scan method.

By default, Spanner sets the scan method asAUTO (automatic) which means depending on the heuristics of the query, batch-oriented query processing might be used to improve query performance. If you want to change the default scanning method fromAUTO, you can use the hint to enforce aROW orBATCH oriented processing method. For more information seeOptimize scans.

INDEX_STRATEGYFORCE_INDEX_UNION

Use theINDEX_STRATEGY=FORCE_INDEX_UNION hint to access data, using the Index Union pattern (reading from two or more indexes and unioning the results). This hint is useful when the condition in theWHERE clause is a disjunction. If an index union isn't possible, an error is raised.

SEEKABLE_KEY_SIZE0 to16

Forces the seekable key size to be equal to the specified value.

The seekable key size is the length of the key (primary key or index key) that's used in a seekable condition, while the rest of the key is used in a residual condition.

This hint requires theFORCE_INDEX hint to also be specified.

The following example shows how to use asecondary indexwhen reading from a table, by appending an index directive of the form@{FORCE_INDEX=index_name} to the table name:

SELECTs.SingerId,s.FirstName,s.LastName,s.SingerInfoFROMSingers@{FORCE_INDEX=SingersByFirstLastName}ASsWHEREs.FirstName="Catalina"ANDs.LastName >"M";

You can include multiple indexes in a query, though only a singleindex is supported for each distinct table reference. Example:

SELECTs.SingerId,s.FirstName,s.LastName,s.SingerInfo,c.ConcertDateFROMSingers@{FORCE_INDEX=SingersByFirstLastName}ASsJOINConcerts@{FORCE_INDEX=ConcertsBySingerId}AScONs.SingerId=c.SingerIdWHEREs.FirstName="Catalina"ANDs.LastName >"M";

Read more about index directives inSecondary Indexes.

Join hints

The following join hints are supported:

Hint keyPossible valuesDescription
FORCE_JOIN_ORDERTRUE
FALSE (default)		If set to true, use the join order that's specified in the query.
JOIN_METHODHASH_JOIN
APPLY_JOIN
MERGE_JOIN
PUSH_BROADCAST_HASH_JOIN
 When implementing a logical join, choose a specific alternative to use for the underlying join method. Learn more inJoin methods To use a HASH join, either useHASH JOIN orJOIN@{JOIN_METHOD=HASH_JOIN}, but not both.
HASH_JOIN_BUILD_SIDEBUILD_LEFT
BUILD_RIGHT		 Specifies which side of the hash join is used as the build side. Can only be used withJOIN_METHOD=HASH_JOIN
BATCH_MODETRUE (default)
FALSE		 Used to disable batched apply join in favor of row-at-a-time apply join. Can only be used withJOIN_METHOD=APPLY_JOIN.
HASH_JOIN_EXECUTIONMULTI_PASS (default)
ONE_PASS		 For a hash join, specifies what should be done when the hash table size reaches its memory limit. Can only be used whenJOIN_METHOD=HASH_JOIN. SeeHash Join Execution for more details.

Join methods

Join methods are specific implementations of the various logicaljoin types. Some join methods are available only for certain jointypes. The choice of which join method to use depends on the specificsof your query and of the data being queried. The best way to figureout if a particular join method helps with the performance of yourquery is to try the method and view the resultingquery execution plan. SeeQuery Execution Operators for more details.

Join MethodDescriptionOperands
HASH_JOIN The hash join operator builds a hash table out of one side (the build side), and probes in the hash table for all the elements in the other side (the probe side). Different variants are used for various join types. View the query execution plan for your query to see which variant is used. Read more about theHash join operator.
APPLY_JOIN The apply join operator gets each item from one side (the input side), and evaluates the subquery on other side (the map side) using the values of the item from the input side. Different variants are used for various join types. Cross apply is used for inner join, and outer apply is used for left joins. Read more about theCross apply andOuter apply operators.
MERGE_JOIN The merge join operator joins two streams of sorted data. The optimizer adds Sort operators to the plan if the data isn't already providing the required sort property for the given join condition. The engine provides a distributed merge sort by default, which when coupled with merge join may allow for larger joins, potentially avoiding disk spilling and improving scale and latency. Different variants are used for various join types. View the query execution plan for your query to see which variant is used. Read more about theMerge join operator.
PUSH_BROADCAST_HASH_JOIN The push broadcast hash join operator builds a batch of data from the build side of the join. The batch is then sent in parallel to all the local splits of the probe side of the join. On each of the local servers, a hash join is executed between the batch and the local data. This join is most likely to be beneficial when the input can fit within one batch, but isn't strict. Another potential area of benefit is when operations can be distributed to the local servers, such as an aggregation that occurs after a join. A push broadcast hash join can distribute some aggregation where a traditional hash join can't. Different variants are used for various join types. View the query execution plan for your query to see which variant is used. Read more about thePush broadcast hash join operator.

Hash Join Execution

To execute a hash join between two tables, Spanner first scansrows from the build side and loads them into a hash table. Then it scans rowsfrom the probe side, while comparing them against the hash table. If the hashtable reaches its memory limit, depending on the value of theHASH_JOIN_EXECUTION query hint, the hash join has one of the followingbehaviors:

  • HASH_JOIN_EXECUTION=MULTI_PASS (default): The query engine splits thebuild side table into partitions in a way that the size of a hash tablecorresponding to each partition is less than the memory size limit. Forevery partition of the build side table, the probe side is scanned once.
  • HASH_JOIN_EXECUTION=ONE_PASS: The query engine writes both the build sidetable and the probe side table to disk in partitions in a way that the hashtable of the build side table in each partition is less than the memorylimit. The probe side is only scanned once.

Graph hints

Hints are supported for graphs. For more information, seeGraph hints.

Value tables

In addition to standard SQLtables, GoogleSQL supportsvalue tables.In a value table, rather than having rows made up of a list of columns, each rowis a single value ofa specific type, and there are no column names.

In the following example, a value table for aSTRUCT is produced with theSELECT AS VALUE statement:

SELECT*FROM(SELECTASVALUESTRUCT(123ASa,FALSEASb))/*-----+-------* | a   | b     | +-----+-------+ | 123 | FALSE | *-----+-------*/

Value tables are often but not exclusively used with compound data types.A value table can consist of any supported GoogleSQL data type,although value tables consisting of scalar types occur less frequently thanstructs.

Return query results as a value table

Spanner doesn't support value tables as base tables indatabase schemas and doesn't support returning value tables in query results.As a consequence, value table producing queries aren't supported astop-level queries.

Value tables can also occur as the output of theUNNESToperator or asubquery. TheWITH clauseintroduces a value table if the subquery used produces a value table.

In contexts where a query with exactly one column is expected, a value tablequery can be used instead. For example, scalar andarraysubqueries normally require a single-column query,but in GoogleSQL, they also allow using a value table query.

Use a set operation on a value table

InSET operations likeUNION ALL you can combine tables with value tables,provided that the table consists of a single column with a type that matches thevalue table's type. The result of these operations is always a value table.

Appendix A: examples with sample data

These examples include statements which perform queries on theRoster andTeamMascot,andPlayerStats tables.

Sample tables

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

Roster table

TheRoster table includes a list of player names (LastName) and theunique 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 thisWITH clause to emulate a temporary table name for theexamples in this reference:

WITHRosterAS(SELECT'Adams'asLastName,50asSchoolIDUNIONALLSELECT'Buchanan',52UNIONALLSELECT'Coolidge',52UNIONALLSELECT'Davis',51UNIONALLSELECT'Eisenhower',77)SELECT*FROMRoster

PlayerStats table

ThePlayerStats table includes a list of player names (LastName) and theunique 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 thisWITH clause to emulate a temporary table name for theexamples in this reference:

WITHPlayerStatsAS(SELECT'Adams'asLastName,51asOpponentID,3asPointsScoredUNIONALLSELECT'Buchanan',77,0UNIONALLSELECT'Coolidge',77,1UNIONALLSELECT'Adams',52,4UNIONALLSELECT'Buchanan',50,13)SELECT*FROMPlayerStats

TeamMascot table

TheTeamMascot table includes a list of unique school IDs (SchoolID) and themascot for that school (Mascot).

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

You can use thisWITH clause to emulate a temporary table name for theexamples in this reference:

WITHTeamMascotAS(SELECT50asSchoolID,'Jaguars'asMascotUNIONALLSELECT51,'Knights'UNIONALLSELECT52,'Lakers'UNIONALLSELECT53,'Mustangs')SELECT*FROMTeamMascot

GROUP BY clause

Example:

SELECTLastName,SUM(PointsScored)FROMPlayerStatsGROUPBYLastName;
LastNameSUM
Adams7
Buchanan13
Coolidge1

UNION

TheUNION operator combines the result sets of two or moreSELECT statementsby pairing columns from the result set of eachSELECT statement and verticallyconcatenating them.

Example:

SELECTMascotASX,SchoolIDASYFROMTeamMascotUNIONALLSELECTLastName,PointsScoredFROMPlayerStats;

Results:

XY
Jaguars50
Knights51
Lakers52
Mustangs53
Adams3
Buchanan0
Coolidge1
Adams4
Buchanan13

INTERSECT

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

SELECTLastNameFROMRosterINTERSECTALLSELECTLastNameFROMPlayerStats;

Results:

LastName
Adams
Coolidge
Buchanan

EXCEPT

The query below returns last names in Roster that arenot present inPlayerStats.

SELECTLastNameFROMRosterEXCEPTDISTINCTSELECTLastNameFROMPlayerStats;

Results:

LastName
Eisenhower
Davis

Reversing the order of theSELECT statements will return last names inPlayerStats that arenot present in Roster:

SELECTLastNameFROMPlayerStatsEXCEPTDISTINCTSELECTLastNameFROMRoster;

Results:

(empty)

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

Last updated 2025-07-02 UTC.