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.

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

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:

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:

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:

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:

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:

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:

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

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:

SELECT (SELECT AS STRUCT * FROM t) AS v

Other errors from the same family:

See Subqueries for the full list of places a scalar subquery can appear.