Subquery returns more than one row

You used a subquery somewhere that expects exactly one value, and it returned several. Most engines stop you — SQLite, notably, won't.

• PostgreSQL: ERROR: more than one row returned by a subquery used as an expression
• MySQL: Subquery returns more than 1 row
• SQL Server: Msg 512, Level 16: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression.

The cause

A subquery used where a single value is expected — after =, in a SELECT list, as a scalar in an expression — has to actually return one row and one column. If the condition that connects it to the outer query isn't selective enough, it can return several rows for a given outer row, and the engine has no rule for which one to use:

-- one row per customer expected — but a customer can have several orders
SELECT name,
       (SELECT amount FROM orders WHERE customer_id = c.customer_id) AS an_amount
FROM customers c;

Any customer with more than one order breaks this in Postgres, MySQL, or SQL Server.

The fix — say what you actually mean

Want an aggregate across all their orders? Wrap it:

Loading editor…

Want one specific row (most recent order, highest amount, ...)? Add the ordering and limit that picks it deterministically:

Loading editor…

Want every matching row, not one collapsed value? That's not a scalar subquery's job — join instead:

Loading editor…

The SQLite trap: it won't even tell you

This is one of the sharpest differences between SQLite and the server engines. The exact query that throws in Postgres, MySQL, and SQL Server runs silently in SQLite — it just picks one row from the match set and gives you no indication it did:

Loading editor…

That returned an answer for every customer, including the ones with multiple orders — each got some order's amount, not an error. Which order it picked isn't defined by the query; it depends on scan order, which can change with an index or a SQLite version upgrade. If a scalar subquery like this "just works" in SQLite, test it against Postgres or MySQL before you trust the result — they'll tell you what SQLite is hiding.