GoogleSQL for BigQuery supports subqueries.

## About subqueries

A subquery is a [query](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax) that appears inside another
query statement. Subqueries are also referred to as sub-`SELECT`s or
nested `SELECT`s. The full `SELECT` syntax is valid in subqueries.

## Common tables used in examples

Some examples reference a table called `Players`:

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

Some examples reference a table called `NPCs`:

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

Some examples reference a table called `Mascots`:

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

You can use this `WITH` clause to emulate temporary table names for
`Players` and `NPCs`
in subqueries that support the `WITH` clause.:

    WITH
      Players AS (
        SELECT 'gorbie' AS username, 29 AS level, 'red' AS team UNION ALL
        SELECT 'junelyn', 2 , 'blue' UNION ALL
        SELECT 'corba', 43, 'green'),
      NPCs AS (
        SELECT 'niles' AS username, 'red' AS team UNION ALL
        SELECT 'jujul', 'red' UNION ALL
        SELECT 'effren', 'blue'),
      Mascots AS (
        SELECT 'cardinal' AS mascot , 'red' AS team UNION ALL
        SELECT 'parrot', 'green' UNION ALL
        SELECT 'finch', 'blue' UNION ALL
        SELECT 'sparrow', 'yellow')
    SELECT * FROM (
      SELECT username, team FROM Players UNION ALL
      SELECT username, team FROM NPCs);

## 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](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/subqueries#correlated_subquery_concepts).

### 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`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/subqueries#example_tables) and [`Mascots`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/subqueries#example_tables)
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`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/subqueries#example_tables) 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](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/array_functions#array) for full semantics.

**Examples**

In this example, an ARRAY subquery returns an array of usernames assigned to the
red team in the [`NPCs`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/subqueries#example_tables) 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](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/operators#in_operators).

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`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/subqueries#example_tables) 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`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/subqueries#example_tables) 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`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/subqueries#example_tables) table:

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

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

In this example, a list of [`NPCs`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/subqueries#example_tables)
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](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/subqueries#evaluation_rules_subqueries).

**Examples**

In this example, a list of mascots that don't have any players assigned to them
are returned. The [`Mascots`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/subqueries#example_tables) and [`Players`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/subqueries#example_tables)
tables are referenced.

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

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

In this example, a correlated scalar subquery returns the mascots for a list of
players, using the [`Players`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/subqueries#example_tables) and [`Mascots`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/subqueries#example_tables)
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        |
     +---*/

## Volatile subqueries

A volatile subquery is a subquery that doesn't always produce the same result
over the same inputs. For example, if a subquery includes a function
that returns a random number, the subquery is volatile because the result
isn't always the same.

**Examples**

In this example, a random number of usernames are returned from the
[`Players`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/subqueries#example_tables) table.

    SELECT
      results.username
    FROM
      (SELECT * FROM Players WHERE RAND() < 0.5) AS results;

    -- 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 per row, depending on your [query plan](https://en.wikipedia.org/wiki/Query_plan).
- A correlated subquery must be logically re-evaluated for every distinct set of parameter values. Depending on your query plan, a correlated subquery may be re-evaluated once per row, even if multiple rows have the same parameter values.