Fix “More Than One Row Returned by a Subquery” in SQL

The symptom

A subquery sits where SQL expects one value: after = or >, or as a column in the SELECT list. That's a scalar subquery, and it may return one row or none. This one returned more, and the database can't choose between them for you.

The bench below looks for customers with a pending order. Garden Shop has three pending orders, so the subquery returns three customer IDs, and = has nothing single to compare with.

The fix: IN for a list

When any of the rows should count as a match, you wanted in, not =. It compares against the whole list:

select customer_id, first_name
from customers
where customer_id = (
  select customer_id
  from orders
  where status = 'pending'
)
order by customer_id;

select customer_id, first_name
from customers
where customer_id in (
  select customer_id
  from orders
  where status = 'pending'
)
order by customer_id;

That returns Nadia, Owen, and Mia, the three customers with a pending order. The same goes for the other comparisons: > any (...) and > all (...) compare against a list where > can't.

When you need one value per row

In a SELECT list, a list won't do: each output row has one cell to fill. This asks for the product on each order, and most orders have more than one line item:

select o.order_id,
       (select oi.product_id
        from order_items as oi
        where oi.order_id = o.order_id) as product_id
from orders as o
order by o.order_id;

Decide which value you meant, and say so in the subquery. Every choice below returns one row per order.

select o.order_id,
       (select count(*)
        from order_items as oi
        where oi.order_id = o.order_id) as line_items
from orders as o
order by o.order_id
limit 5;

select o.order_id,
       (select string_agg(p.product_name, ', ' order by p.product_name)
        from order_items as oi
        join products as p on p.product_id = oi.product_id
        where oi.order_id = o.order_id) as products
from orders as o
order by o.order_id
limit 5;

select o.order_id,
       (select p.product_name
        from order_items as oi
        join products as p on p.product_id = oi.product_id
        where oi.order_id = o.order_id
        order by oi.quantity * oi.unit_price desc, p.product_name
        limit 1) as biggest_line
from orders as o
order by o.order_id
limit 5;

The tie-breaker after the sort matters: without it, two lines worth the same amount leave limit 1 free to return either. If you want every product as its own row, drop the subquery and join order_items instead.

Find the keys with more than one match

Run the subquery on its own and group by the column it matches on. Every group with a count above 1 is a row that breaks the scalar subquery:

select order_id, count(*) as line_items
from order_items
group by order_id
having count(*) > 1
order by line_items desc, order_id
limit 5;

Order 24 has three line items, and most others have two. When you expected one match per key, this is how you find the duplicates.

Don't turn the check off

DuckDB's message ends with a suggestion: Use "SET scalar_subquery_error_on_multiple_rows=false" to revert to the previous behavior of returning a random row. That swaps an error for a wrong answer that looks right. The setting stays on for the rest of the connection, so every later query loses the check too (in the SQLShed playground, the rest of that Run). Fix the subquery instead.

How other engines word it

SQLite behaves like DuckDB with the check turned off: it uses the first row and says nothing.

Related errors

• Fix “Aggregate Functions Are Not Allowed in WHERE” in SQL

  WHERE filters rows before grouping, so aggregates do not exist yet. Use HAVING.

• Fix “Column Must Appear in the GROUP BY Clause” in SQL

  A selected column is neither grouped nor aggregated, so this engine cannot derive one value for the group.

• Fix “Catalog Error: Table Does Not Exist” in SQL

  No table by that name is visible to the query. A typo, a missing schema, or a table that's already gone.

Learn more

• SQL error messages explained
• Lesson 10.1 · Scalar subqueries
• Lesson 10.2 · IN with subqueries
• Lesson 10.4 · Correlated subqueries vs joins
• Reference · Subqueries and EXISTS