# 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.

Source: https://docs.querylab.io/bigquery/subqueries/

Updated: 2026-10-14

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:

**each customer's order total**

```sql
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
```

Result in BigQuery (3 rows):

| name | total |
| --- | --- |
| ana | 75 |
| ben | 20 |
| cai | NULL |

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

**a subquery that finds nothing**

```sql
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
```

Result in BigQuery (1 row):

| big_order |
| --- |
| NULL |

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`:

**each customer's largest order**

```sql
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
```

Result in BigQuery (3 rows):

| name | top_order |
| --- | --- |
| ana | o2 |
| ben | o3 |
| cai | NULL |

## ARRAY subqueries: 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:

**each customer's order IDs**

```sql
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
```

Result in BigQuery (3 rows):

| name | order_ids |
| --- | --- |
| ana | [o1, o2] |
| ben | [o3] |
| cai | [] |

This is how you build nested data from flat tables, with an `ORDER BY` inside to fix the element order. See [Arrays](https://docs.querylab.io/bigquery/arrays/).

## STRUCT subqueries: 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:

**order count and largest order per customer**

```sql
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
```

Result in BigQuery (3 rows):

| name | stats |
| --- | --- |
| ana | {n: 2, biggest: 45} |
| ben | {n: 1, biggest: 20} |
| cai | {n: 0, biggest: NULL} |

Read the fields with `stats.n` and `stats.biggest`, or expand them with `stats.*`. See [STRUCT](https://docs.querylab.io/bigquery/structs/).

## IN and EXISTS

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

**customers with an order over 25**

```sql
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
```

Result in BigQuery (1 row):

| name |
| --- |
| ana |

`NOT IN` returns no rows at all when the subquery contains a NULL; use `NOT EXISTS`. See [NOT IN with a NULL](https://docs.querylab.io/bigquery/null-handling/#not-in-with-a-null-returns-nothing).

## Subqueries inside subqueries

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

**the largest order above the average**

```sql
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
```

Result in BigQuery (1 row):

| name | above_avg_max |
| --- | --- |
| ana | 45 |

## No LATERAL

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

**LATERAL in FROM**

```sql
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
```

BigQuery rejects this query:

```text
LATERAL join is not supported at [12:22]
```

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](https://docs.querylab.io/bigquery/unnest/).

## Unsupported correlated subqueries

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:

```sql
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](https://docs.querylab.io/writing-sql/partial-execution/)). See [How a query is billed](https://docs.querylab.io/bigquery/how-queries-are-billed/#reading-a-table-twice).

Collect rows into an array with an ARRAY subquery. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Subqueries](https://cloud.google.com/bigquery/docs/reference/standard-sql/subqueries)

- [Troubleshoot query issues, correlated subqueries](https://cloud.google.com/bigquery/docs/troubleshoot-queries)