Subqueries in Standard SQL  |  BigQuery  |  Google Cloud

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 Guilds tables:

SELECT account, (SELECT mascot FROM Guilds WHERE Players.guild = id) AS player_mascot
FROM Players;

+---------------------------+
| account   | player_mascot |
+---------------------------+
| gorbie    | cardinal      |
| junelyn   | finch         |
| corba     | parrot        |
+---------------------------+

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

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

+---------------------------------------+
| account   | 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
accounts assigned to the red guild in the NPCs table:

SELECT ARRAY(SELECT account FROM NPCs WHERE guild = 'red') as red
FROM NPCs LIMIT 1;

+-----------------+
| 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 an account called
corba exists within the Players table:

SELECT "corba" IN (SELECT account 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 account FROM Players WHERE guild="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 accounts from the
Players table:

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

+-----------+
| account   |
+-----------+
| gorbie    |
| junelyn   |
| corba     |
+-----------+

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

SELECT account FROM (
  WITH red_guild AS (SELECT * FROM NPCs WHERE guild='red')
  SELECT * FROM red_guild);

+-----------+
| account   |
+-----------+
| niles     |
| jujul     |
+-----------+

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 Guilds and
Players tables are referenced.

SELECT mascot
FROM Guilds
WHERE NOT EXISTS (SELECT account
  FROM Players
  WHERE Guilds.id = Players.guild)

+----------+
| mascot   |
+----------+
| sparrow  |
+----------+

In this example, a correlated scalar subquery returns the mascots for
a list of players, using the Players
and Guilds tables:

SELECT account, (SELECT mascot FROM Guilds WHERE Players.guild = id) AS player_mascot
FROM Players;

+---------------------------+
| account   | player_mascot |
+---------------------------+
| gorbie    | cardinal      |
| junelyn   | finch         |
| corba     | parrot        |
+---------------------------+

Volatile subqueries

A volatile subquery is a subquery that does not 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
is not always the same.

Examples

In this example, a random number of accounts is returned from the
Players table.

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

-- The results are not always the same when you execute
-- the preceding query, but will look similar to this:
+---------+
| account |
+---------+
| 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.
  • 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.

Common tables used in examples

Some examples reference a table called Players:

+-----------------------------+
| account   | level   | guild |
+-----------------------------+
| gorbie    | 29      | red   |
| junelyn   | 2       | blue  |
| corba     | 43      | green |
+-----------------------------+

Some examples reference a table called NPCs:

+-------------------+
| account   | guild |
+-------------------+
| niles     | red   |
| jujul     | red   |
| effren    | blue  |
+-------------------+

Some examples reference a table called Guilds:

+-------------------+
| mascot   | id     |
+-------------------+
| 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 account, 29 AS level, 'red' AS guild UNION ALL
    SELECT 'junelyn', 2 , 'blue' UNION ALL
    SELECT 'corba', 43, 'green'),
  NPCs AS (
    SELECT 'niles' AS account, 'red' AS guild UNION ALL
    SELECT 'jujul', 'red' UNION ALL
    SELECT 'effren', 'blue'),
  Guilds AS (
    SELECT 'cardinal' AS mascot , 'red' AS id UNION ALL
    SELECT 'parrot', 'green' UNION ALL
    SELECT 'finch', 'blue' UNION ALL
    SELECT 'sparrow', 'yellow')
SELECT * FROM (
  SELECT account, guild FROM Players UNION ALL
  SELECT account, guild FROM NPCs)

Related Articles

Leave a Reply

Back to top button