Fix "Subquery Returned More Than 1 Value" in SQL

By Sharon Ben-Moshe · September 11, 2026 · 2 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

How to resolve SQL Server Msg 512 and PostgreSQL 21000: Subquery returned more than 1 value. Replace scalar subqueries with JOINs, IN, or TOP 1.

Quick answer

This error (SQL Server Msg 512, PostgreSQL 21000) happens when a scalar subquery used in a SELECT list or comparison (=, <, >) returns multiple rows instead of exactly one. Resolve it by replacing '=' with 'IN', rewriting the scalar subquery as an INNER/LEFT JOIN, or restricting the subquery to a single row using LIMIT 1 or TOP 1 with ORDER BY.

FAQs

What is a scalar subquery in SQL?

A scalar subquery is a subquery enclosed in parentheses that must return exactly one column and at most one row. SQL engines treat it as a single value expression.

Why does '=' fail with multiple rows while 'IN' succeeds?

The equality operator '=' requires a single comparison value on each side. The IN operator tests set membership and accepts zero, one, or thousands of rows.

Is adding TOP 1 or LIMIT 1 always the right solution?

Not always. If the business logic expects a one-to-one relationship, multiple rows indicate dirty data or a missing join key. Adding LIMIT 1 masks underlying data defects by arbitrarily picking a row.

Can an aggregate function prevent this error?

Yes. If you need a single summarizing value (such as MAX, MIN, or AVG), using an aggregate inside the subquery guarantees that it returns exactly one row even when multiple matches exist.

SQL Errors

Fix BigQuery "Unrecognized Name" Error: Scoping and Syntax Rules

By Sharon Ben-Moshe · Sep 29, 2026

Fix Google BigQuery's "Unrecognized name: column" error. Learn alias visibility rules across WHERE and QUALIFY, case sensitivity, and struct unnesting.

Read post

SQL Errors

SQLite "Datatype Mismatch" (Error 20): Type Affinity and Strict Tables

By Sharon Ben-Moshe · Sep 26, 2026

Understand SQLite Error 20 (datatype mismatch). Learn how SQLite type affinity, CHECK constraints, and STRICT tables enforce data integrity.

Read post

SQL Errors

Try it now

Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.

Open SQL Formatter →

Key takeaways

Scalar subqueries in SELECT clauses or WHERE = comparisons must return at most one row.

Switch from WHERE col = (SELECT ...) to WHERE col IN (SELECT ...) if multiple values are valid.

Convert correlated scalar subqueries into LEFT JOINs for higher performance and clear cardinality.

If picking a single item, always provide an ORDER BY clause with LIMIT 1 / TOP 1 to guarantee deterministic results.

Anatomy of the scalar subquery crash

Consider a report that enriches an orders table with customer phone numbers. If a customer has multiple phone numbers in the database, a scalar subquery explodes at runtime:

Failing query versus robust JOIN approach

-- FAILS if customer has multiple phone numbers:
SELECT
  order_id,
  order_date,
  (
    SELECT phone_number
    FROM customer_phones
    WHERE customer_id = orders.customer_id
  ) AS customer_phone
FROM orders;

-- SAFE & PERFORMANT: Join with an explicit primary phone filter
SELECT
  o.order_id,
  o.order_date,
  cp.phone_number AS customer_phone
FROM orders o
LEFT JOIN customer_phones cp
  ON o.customer_id = cp.customer_id
  AND cp.is_primary = true;

Fixing subqueries in WHERE clauses

Another common trap occurs when comparing a column to a subquery with '=' instead of set operators:

Using IN or EXISTS instead of scalar equality

-- FAILS when multiple departments match:
SELECT employee_name, department_id
FROM employees
WHERE department_id = (
  SELECT id FROM departments WHERE region = 'EMEA'
);

-- FIXED: IN handles any number of matching IDs
SELECT employee_name, department_id
FROM employees
WHERE department_id IN (
  SELECT id FROM departments WHERE region = 'EMEA'
);

Common mistakes to avoid

Slapping LIMIT 1 on the subquery without ORDER BY: this picks a non-deterministic row and hides duplicate data bugs.

Relying on scalar subqueries in large SELECT lists: they execute repeatedly row-by-row (correlated execution) rather than taking advantage of hash joins.

Overlooking NULL values returned by IN subqueries, which can cause unexpected results with NOT IN.

Quick answer

FAQs

What is a scalar subquery in SQL?

Why does '=' fail with multiple rows while 'IN' succeeds?

Is adding TOP 1 or LIMIT 1 always the right solution?

Can an aggregate function prevent this error?

Keep reading

Fix BigQuery "Unrecognized Name" Error: Scoping and Syntax Rules

SQLite "Datatype Mismatch" (Error 20): Type Affinity and Strict Tables

Key takeaways

Anatomy of the scalar subquery crash

Fixing subqueries in WHERE clauses

Common mistakes to avoid

Related SQL guides

Official references