GoogleSQL for BigQuery supports subqueries.

About subqueries

A subquery is aquery that appears inside anotherquery statement. Subqueries are also referred to as sub-SELECTs ornestedSELECTs. The fullSELECT syntax is valid in subqueries.

Expression subqueries

Expression subqueries are used ina query wherever expressions are valid. They return a single value, as opposedto a column or table. Expression subqueries can becorrelated.

Scalar subqueries

( subquery )

Description

A subquery inside an expression is interpreted as a scalar subquery.Scalar subqueries are often used in theSELECT list orWHERE clause.

A scalar subquery must select a single column. Trying to select multiplecolumns will result in an analysis error. ASELECT list with a singleexpression is the simplest way to select a single column. The result typeof the scalar subquery is the type of that expression.

Another possibility is to useSELECT AS STRUCT to define a subquery thatselects a singleSTRUCT type value whose fields are defined by one or moreexpressions.

If the subquery returns exactly one row, that single value is thescalar subquery result. If the subquery returns zero rows, the result isNULL.If the subquery returns more than one row, the query fails with a runtime error.

Examples

In this example, a correlated scalar subquery returns the mascots for a list ofplayers, using thePlayers andMascotstables:

SELECTusername,(SELECTmascotFROMMascotsWHEREPlayers.team=Mascots.team)ASplayer_mascotFROMPlayers;/*---------------------------+ | username  | player_mascot | +---------------------------+ | gorbie    | cardinal      | | junelyn   | finch         | | corba     | parrot        | +---------------------------*/

In this example, an aggregate scalarsubquery calculatesavg_level, the average level of a user in thePlayers table.

SELECTusername,level,(SELECTAVG(level)FROMPlayers)ASavg_levelFROMPlayers;/*---------------------------------------+ | username  | level      | avg_level    | +---------------------------------------+ | gorbie    | 29         | 24.66        | | junelyn   | 2          | 24.66        | | corba     | 43         | 24.66        | +---------------------------------------*/

Array subqueries

ARRAY ( subquery )

Description

An ARRAY subquery is a special case of expression subquery, in that it returnsan ARRAY. If the subquery returns zerorows, returns an empty ARRAY.Never returns aNULL ARRAY.

TheSELECT list in an ARRAY subquery must have exactly one column ofany type, which defines the element type of the array returned by thearray subquery. If not, an error is returned. When the subquery is written withSELECT AS STRUCT,theSELECT list can include multiple columns, and the value returned bythe array subquery is an ARRAY of the constructedSTRUCTs.Selecting multiple columns without usingSELECT AS is an error.

ARRAY subqueries can useSELECT AS STRUCT to buildarrays of structs.

SeeArray functions for full semantics.

Examples

In this example, an ARRAY subquery returns an array of usernames assigned to thered team in theNPCs table:

SELECTARRAY(SELECTusernameFROMNPCsWHEREteam='red')ASred;/*-----------------+ | red             | +-----------------+ | [niles,jujul]   | +-----------------*/

IN subqueries

value [ NOT ] IN ( subquery )

Description

Returns TRUE ifvalue is in the set of rows returned by the subquery.Returns FALSE if the subquery returns zero rows.

The subquery's SELECT list must have a single column of any type andits type must be comparable to the type forvalue. If not, an error isreturned. For full semantics, includingNULL handling, see theIN operator.

If you need to use anIN subquery with an array, these are equivalent:

value[NOT]IN(subquery)value[NOT]INUNNEST(ARRAY(subquery))

Examples

In this example, theIN operator that checks to see if a username calledcorba exists within thePlayers table:

SELECT'corba'IN(SELECTusernameFROMPlayers)ASresult;/*--------+ | result | +--------+ | TRUE   | +--------*/

EXISTS subqueries

EXISTS( subquery )

Description

Returns TRUE if the subquery produces one or more rows. Returns FALSE if thesubquery produces zero rows. Never returnsNULL. Unlike all otherexpression subqueries, there are no rules about the column list.Any number of columns may be selected and it will not affect the query result.

Examples

In this example, theEXISTS operator that checks to see if any rows areproduced, using thePlayers table:

SELECTEXISTS(SELECTusernameFROMPlayersWHEREteam='yellow')ASresult;/*--------+ | result | +--------+ | FALSE  | +--------*/

Table subqueries

FROM ( subquery ) [ [ AS ] alias ]

Description

With table subqueries, the outer query treats the result of the subquery as atable. You can only use these in theFROM clause.

Examples

In this example, a subquery returns a table of usernames from thePlayers table:

SELECTresults.usernameFROM(SELECT*FROMPlayers)ASresults;/*-----------+ | username  | +-----------+ | gorbie    | | junelyn   | | corba     | +-----------*/

In this example, a list ofNPCsassigned to the red team are returned.

SELECTusernameFROM(WITHred_teamAS(SELECT*FROMNPCsWHEREteam='red')SELECT*FROMred_team);/*-----------+ | username  | +-----------+ | niles     | | jujul     | +-----------*/

Correlated subqueries

A correlated subquery is a subquery that references a column from outside thatsubquery. Correlation prevents reusing of the subquery result. You can learnmore about thishere.

Examples

In this example, a list of mascots that don't have any players assigned to themare returned. TheMascots andPlayerstables are referenced.

SELECTmascotFROMMascotsWHERENOTEXISTS(SELECTusernameFROMPlayersWHEREMascots.team=Players.team);/*----------+ | mascot   | +----------+ | sparrow  | +----------*/

In this example, a correlated scalar subquery returns the mascots for a list ofplayers, using thePlayers andMascotstables:

SELECTusername,(SELECTmascotFROMMascotsWHEREPlayers.team=Mascots.team)ASplayer_mascotFROMPlayers;/*---------------------------+ | username  | player_mascot | +---------------------------+ | gorbie    | cardinal      | | junelyn   | finch         | | corba     | parrot        | +---------------------------*/

Volatile subqueries

A volatile subquery is a subquery that doesn't always produce the same resultover the same inputs. For example, if a subquery includes a functionthat returns a random number, the subquery is volatile because the resultisn't always the same.

Examples

In this example, a random number of usernames are returned from thePlayers table.

SELECTresults.usernameFROM(SELECT*FROMPlayersWHERERAND() <0.5)ASresults;-- The results aren't always the same when you execute-- the preceding query, but will look similar to this:/*----------+ | username | +----------+ | gorbie   | | junelyn  | +----------*/

Evaluation rules for subqueries

Some subqueries are evaluated once, others more often.

• A non-correlated, volatile subquery may be re-evaluated once perrow, depending on yourquery plan.
• A correlated subquery must be logically re-evaluated for every distinct setof parameter values. Depending on your query plan, a correlatedsubquery may be re-evaluated once per row, even if multiplerows have the same parameter values.

Common tables used in examples

Some examples reference a table calledPlayers:

/*-----------------------------+ | username  | level   | team  | +-----------------------------+ | gorbie    | 29      | red   | | junelyn   | 2       | blue  | | corba     | 43      | green | +-----------------------------*/

Some examples reference a table calledNPCs:

/*-------------------+ | username  | team  | +-------------------+ | niles     | red   | | jujul     | red   | | effren    | blue  | +-------------------*/

Some examples reference a table calledMascots:

/*-------------------+ | mascot   | team   | +-------------------+ | cardinal | red    | | parrot   | green  | | finch    | blue   | | sparrow  | yellow | +-------------------*/

You can use thisWITH clause to emulate temporary table names forPlayers andNPCsin subqueries that support theWITH clause.:

WITHPlayersAS(SELECT'gorbie'ASusername,29ASlevel,'red'ASteamUNIONALLSELECT'junelyn',2,'blue'UNIONALLSELECT'corba',43,'green'),NPCsAS(SELECT'niles'ASusername,'red'ASteamUNIONALLSELECT'jujul','red'UNIONALLSELECT'effren','blue'),MascotsAS(SELECT'cardinal'ASmascot,'red'ASteamUNIONALLSELECT'parrot','green'UNIONALLSELECT'finch','blue'UNIONALLSELECT'sparrow','yellow')SELECT*FROM(SELECTusername,teamFROMPlayersUNIONALLSELECTusername,teamFROMNPCs);