# Scalar subquery produced more than one element

> A subquery used as a single value returned two or more rows. It is a data error, so the query can work for months and then fail, and a dry run will never catch it.

Source: https://docs.querylab.io/bigquery-errors/scalar-subquery-produced-more-than-one-element/

Updated: 2026-10-25

```text
Scalar subquery produced more than one element
```

## What BigQuery means

You put a subquery somewhere that a single value goes, and at run time it returned more than one row.

A subquery in parentheses in the select list, on one side of a comparison, or inside a function argument is a scalar subquery. BigQuery allows it to return zero rows, in which case the value is NULL, and exactly one row. Two rows has no answer, so it fails.

The important part: this is a **data** error. The query is valid SQL. Nothing about it is wrong until a row shows up that makes the subquery return two values. That is why it tends to appear in production months after the query was written, and why a dry run cannot warn you about it: BigQuery has to read the data to find out.

There is no position in the message, and no column name. You get the text and nothing else.

## The smallest query that reproduces it

```sql
WITH orders AS (
  SELECT 'c1' AS customer_id, 'o1' AS order_id, DATE '2026-01-03' AS created_at UNION ALL
  SELECT 'c1', 'o2', DATE '2026-02-11' UNION ALL
  SELECT 'c2', 'o3', DATE '2026-01-20'
)
SELECT (SELECT order_id FROM orders WHERE customer_id = 'c1') AS first_order
```

Customer `c1` has two orders. The subquery has to produce one `order_id` and cannot.

Change `'c1'` to `'c2'` and the same query succeeds, because that customer happens to have one order.

## The fix

Pick the behaviour you actually want.

**One specific row.** Make the condition unique, or order and take the first:

```sql
SELECT (
  SELECT order_id FROM orders
  WHERE customer_id = 'c1'
  ORDER BY created_at DESC
  LIMIT 1
) AS latest_order
```

`LIMIT 1` without an `ORDER BY` gives you an arbitrary row, and a different one on different runs. If any row will genuinely do, that is fine. Otherwise the `ORDER BY` is what makes it deterministic; `LIMIT 1` on its own does not.

**One value derived from all of them.** Aggregate:

```sql
SELECT (SELECT MAX(order_id) FROM orders WHERE customer_id = 'c1') AS last_order
```

An aggregate with no `GROUP BY` always returns exactly one row, so this can never fail this way.

**All of them.** Build an array and stop pretending it is scalar:

```sql
SELECT (SELECT ARRAY_AGG(order_id) FROM orders WHERE customer_id = 'c1') AS all_orders
```

**Actually a join.** If the subquery repeats for every outer row, a `LEFT JOIN` is usually clearer and faster:

```sql
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o USING (customer_id)
```

This changes the row count, which is often what you wanted in the first place.

## Finding the rows that break it

Group by the subquery's key and look for counts above one:

```sql
SELECT customer_id, COUNT(*) AS n
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY n DESC
LIMIT 20
```

If this returns nothing today, the query will work today. That is not the same as the query being correct.

## Neighbouring errors

A different message, caught at planning time rather than run time, for a subquery with too many **columns** rather than too many rows:

```sql
WITH t AS (SELECT 1 AS x, 2 AS y)
SELECT (SELECT * FROM t) AS v
```

```text
Scalar subquery cannot have more than one column unless using SELECT AS STRUCT to build STRUCT values at [2:8]
```

As the message says, `SELECT AS STRUCT` makes it one value:

```sql
SELECT (SELECT AS STRUCT * FROM t) AS v
```

Other errors from the same family:

| Message                                                              | Cause                                                                       |
| -------------------------------------------------------------------- | --------------------------------------------------------------------------- |
| `Array cannot have a null element; error in writing field X`         | `ARRAY_AGG` over values that include NULL. Use `ARRAY_AGG(x IGNORE NULLS)`. |
| `UPDATE/MERGE must match at most one source row for each target row` | the same uniqueness problem in a write. Deduplicate the source.             |

See [Subqueries](https://docs.querylab.io/bigquery/subqueries/) for the full list of places a scalar subquery can appear.

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

- [Aggregate functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions)