Subqueries

GoogleSQL for BigQuery supports subqueries.

About subqueries

A subquery is a query that appears inside another query statement. Subqueries are also referred to as sub-SELECTs or nested SELECTs. The full SELECT syntax is valid in subqueries.

Expression subqueries

Expression subqueries are used in a query wherever expressions are valid. They return a single value, as opposed to a column or table. Expression subqueries can be correlated.

Scalar subqueries

( subquery )

Description

A subquery inside an expression is interpreted as a scalar subquery. Scalar subqueries are often used in the SELECT list or WHERE clause.

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

Another possibility is to use SELECT AS STRUCT to define a subquery that selects a single STRUCT type value whose fields are defined by one or more expressions.

If the subquery returns exactly one row, that single value is the scalar subquery result. If the subquery returns zero rows, the result is NULL. 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 of players, using the Players and Mascots tables:

SELECT
  username,
  (SELECT mascot FROM Mascots WHERE Players.team = Mascots.team) AS player_mascot
FROM
  Players;

/*---------------------------*
 | username  | player_mascot |
 +---------------------------+
 | gorbie    | cardinal      |
 | junelyn   | finch         |
 | corba     | parrot        |
 *---------------------------*/

In this example, an aggregate scalar subquery calculates avg_level, the average level of a user in the Players table.

SELECT
  username,
  level,
  (SELECT AVG(level) FROM Players) AS avg_level
FROM
  Players;

/*---------------------------------------*
 | 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 returns an ARRAY. If the subquery returns zero rows, returns an empty ARRAY. Never returns a NULL ARRAY.

The SELECT list in an ARRAY subquery must have exactly one column of any type, which defines the element type of the array returned by the array subquery. If not, an error is returned. When the subquery is written with SELECT AS STRUCT, the SELECT list can include multiple columns, and the value returned by the array subquery is an ARRAY of the constructed STRUCTs. Selecting multiple columns without using SELECT AS is an error.

ARRAY subqueries can use SELECT AS STRUCT to build arrays of structs.

See Array functions for full semantics.

Examples

In this example, an ARRAY subquery returns an array of usernames assigned to the red team in the NPCs table:

SELECT
  ARRAY(SELECT username FROM NPCs WHERE team = 'red') AS red;

/*-----------------*
 | red             |
 +-----------------+
 | [niles,jujul]   |
 *-----------------*/

IN subqueries

value [ NOT ] IN ( subquery )

Description

Returns TRUE if value 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 and its type must be comparable to the type for value. If not, an error is returned. For full semantics, including NULL handling, see the IN operator.

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

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

Examples

In this example, the IN operator that checks to see if a username called corba exists within the Players table:

SELECT
  'corba' IN (SELECT username FROM Players) AS result;

/*--------*
 | result |
 +--------+
 | TRUE   |
 *--------*/

EXISTS subqueries

EXISTS( subquery )

Description

Returns TRUE if the subquery produces one or more rows. Returns FALSE if the subquery produces zero rows. Never returns NULL. Unlike all other expression 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, the EXISTS operator that checks to see if any rows are produced, using the Players table:

SELECT
  EXISTS(SELECT username FROM Players WHERE team = 'yellow') AS result;

/*--------*
 | result |
 +--------+
 | FALSE  |
 *--------*/

Table subqueries

FROM ( subquery ) [ [ AS ] alias ]

Description

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

Examples

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

SELECT results.username
FROM (SELECT * FROM Players) AS results;

/*-----------*
 | username  |
 +-----------+
 | gorbie    |
 | junelyn   |
 | corba     |
 *-----------*/

In this example, a list of NPCs assigned to the red team are returned.

SELECT
  username
FROM (
  WITH red_team AS (SELECT * FROM NPCs WHERE team = 'red')
  SELECT * FROM red_team
);

/*-----------*
 | username  |
 +-----------+
 | niles     |
 | jujul     |
 *-----------*/

Correlated subqueries

A correlated subquery is a subquery that references a column from outside that subquery. Correlation prevents reusing of the subquery result. You can learn more about this here.

Examples

In this example, a list of mascots that don't have any players assigned to them are returned. The Mascots and Players tables are referenced.

SELECT mascot
FROM Mascots
WHERE
  NOT EXISTS(SELECT username FROM Players WHERE Mascots.team = Players.team);

/*----------*
 | mascot   |
 +----------+
 | sparrow  |
 *----------*/

In this examp