Subqueries in BigQuery

Scalar, ARRAY, STRUCT, IN and EXISTS subqueries in BigQuery. A scalar subquery with no rows returns NULL and with two rows fails, ARRAY() collects many rows per outer row, SELECT AS STRUCT returns several values, and LATERAL isn't supported.

A subquery is a query inside another query. BigQuery supports them almost anywhere an expression or a table can go: in SELECT, WHERE, FROM and HAVING. A correlated subquery refers to columns of the outer query and conceptually runs once per outer row. Two more forms, ARRAY(subquery) and SELECT AS STRUCT, remove the main limitation of scalar subqueries: returning one value.

The examples use three customers and three orders: ana has two orders, ben one, cai none.

Scalar subqueries

A subquery in SELECT that returns one column is a scalar subquery. Correlated with the outer row, it computes a value per customer:

WITH customers AS (
  SELECT 1 AS customer_id, 'ana' AS name UNION ALL
  SELECT 2, 'ben' UNION ALL
  SELECT 3, 'cai'
),
orders AS (
  SELECT 'o1' AS order_id, 1 AS customer_id, 30 AS amount UNION ALL
  SELECT 'o2', 1, 45 UNION ALL
  SELECT 'o3', 2, 20
)
SELECT
  name,
  (SELECT SUM(amount) FROM orders AS o WHERE o.customer_id = c.customer_id) AS total
FROM customers AS c
ORDER BY name

Cai has no orders, so the SUM over zero rows is NULL. A scalar subquery that returns no row at all also gives NULL:

WITH customers AS (
  SELECT 1 AS customer_id, 'ana' AS name UNION ALL
  SELECT 2, 'ben' UNION ALL
  SELECT 3, 'cai'
),
orders AS (
  SELECT 'o1' AS order_id, 1 AS customer_id, 30 AS amount UNION ALL
  SELECT 'o2', 1, 45 UNION ALL
  SELECT 'o3', 2, 20
)
SELECT (SELECT order_id FROM orders WHERE amount > 1000) AS big_order

A scalar subquery that returns more than one row is an error (“Scalar subquery produced more than one element”). Make it return one row with an aggregate, a unique condition, or ORDER BY ... LIMIT 1:

WITH customers AS (
  SELECT 1 AS customer_id, 'ana' AS name UNION ALL
  SELECT 2, 'ben' UNION ALL
  SELECT 3, 'cai'
),
orders AS (
  SELECT 'o1' AS order_id, 1 AS customer_id, 30 AS amount UNION ALL
  SELECT 'o2', 1, 45 UNION ALL
  SELECT 'o3', 2, 20
)
SELECT name, (SELECT order_id FROM orders AS o WHERE o.customer_id = c.customer_id ORDER BY amount DESC LIMIT 1) AS top_order
FROM customers AS c
ORDER BY name

ARRAY subqueries: many rows per outer row

ARRAY(subquery) collects all the rows the subquery returns into an array, so it can’t fail on too many rows. Customers with no orders get an empty array:

WITH customers AS (
  SELECT 1 AS customer_id, 'ana' AS name UNION ALL
  SELECT 2, 'ben' UNION ALL
  SELECT 3, 'cai'
),
orders AS (
  SELECT 'o1' AS order_id, 1 AS customer_id, 30 AS amount UNION ALL
  SELECT 'o2', 1, 45 UNION ALL
  SELECT 'o3', 2, 20
)
SELECT
  name,
  ARRAY(SELECT order_id FROM orders AS o WHERE o.customer_id = c.customer_id ORDER BY order_id) AS order_ids
FROM customers AS c
ORDER BY name

This is how you build nested data from flat tables, with an ORDER BY inside to fix the element order. See Arrays.

STRUCT subqueries: several values per outer row

SELECT AS STRUCT returns one row of several columns as a single struct value, so one subquery can compute several things:

WITH customers AS (
  SELECT 1 AS customer_id, 'ana' AS name UNION ALL
  SELECT 2, 'ben' UNION ALL
  SELECT 3, 'cai'
),
orders AS (
  SELECT 'o1' AS order_id, 1 AS customer_id, 30 AS amount UNION ALL
  SELECT 'o2', 1, 45 UNION ALL
  SELECT 'o3', 2, 20
)
SELECT
  name,
  (SELECT AS STRUCT COUNT(*) AS n, MAX(amount) AS biggest FROM orders AS o WHERE o.customer_id = c.customer_id) AS stats
FROM customers AS c
ORDER BY name

Read the fields with stats.n and stats.biggest, or expand them with stats.*. See STRUCT.

IN and EXISTS

IN (subquery) and EXISTS (subquery) filter rows without joining:

WITH customers AS (
  SELECT 1 AS customer_id, 'ana' AS name UNION ALL
  SELECT 2, 'ben' UNION ALL
  SELECT 3, 'cai'
),
orders AS (
  SELECT 'o1' AS order_id, 1 AS customer_id, 30 AS amount UNION ALL
  SELECT 'o2', 1, 45 UNION ALL
  SELECT 'o3', 2, 20
)
SELECT name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE amount > 25)
ORDER BY name

NOT IN returns no rows at all when the subquery contains a NULL; use NOT EXISTS. See NOT IN with a NULL.

Subqueries inside subqueries

Subqueries nest, and an inner subquery can use the result of another:

WITH customers AS (
  SELECT 1 AS customer_id, 'ana' AS name UNION ALL
  SELECT 2, 'ben' UNION ALL
  SELECT 3, 'cai'
),
orders AS (
  SELECT 'o1' AS order_id, 1 AS customer_id, 30 AS amount UNION ALL
  SELECT 'o2', 1, 45 UNION ALL
  SELECT 'o3', 2, 20
)
SELECT name, (SELECT MAX(amount) FROM orders WHERE amount > (SELECT AVG(amount) FROM orders)) AS above_avg_max
FROM customers
ORDER BY name
LIMIT 1

No LATERAL

Postgres and others have LATERAL for a subquery in FROM that refers to earlier tables. BigQuery doesn’t:

WITH customers AS (
  SELECT 1 AS customer_id, 'ana' AS name UNION ALL
  SELECT 2, 'ben' UNION ALL
  SELECT 3, 'cai'
),
orders AS (
  SELECT 'o1' AS order_id, 1 AS customer_id, 30 AS amount UNION ALL
  SELECT 'o2', 1, 45 UNION ALL
  SELECT 'o3', 2, 20
)
SELECT c.name, o.order_id
FROM customers AS c, LATERAL (SELECT order_id FROM orders AS o WHERE o.customer_id = c.customer_id) AS o

Use a correlated ARRAY() subquery and UNNEST it, a join, or for arrays the correlated FROM t, UNNEST(t.items) form, which is a lateral join in all but name. See UNNEST.

When BigQuery can’t run a correlated subquery

BigQuery runs correlated subqueries by rewriting them into joins. When it can’t, the query fails with “Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.” Rewrite the subquery as a join with a pre-aggregated table, which is also usually faster:

SELECT c.name, t.total
FROM customers AS c
LEFT JOIN (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
) AS t USING (customer_id);

Cost

A subquery’s tables are billed like any other read: once per column per query, however many times the subquery runs for the outer rows. In Querylab.io, the diamond in the editor margin on a subquery’s first line shows its bytes and cost, and Shift-click runs it as a top-level query (Partial execution). See How a query is billed.