MySQL Subquery Returns More Than 1 Row: What Error 1242 Is Telling You

ERROR 1242 (21000): Subquery returns more than 1 row means you used a subquery in a position where SQL expects exactly one value, and it produced several. The database is not confused — it is refusing to guess which of the rows you meant.

This error is one of the friendlier ones MySQL produces, because it fails immediately and states the problem precisely. The trap is that the obvious fix — bolt LIMIT 1 on the end — makes the error disappear while leaving you with a query that silently returns arbitrary results. There are four correct fixes and choosing between them requires deciding what you actually meant, which is why the error is worth reading properly.

What the error actually means
Fix 1: use IN when you meant a set
Fix 2: aggregate when you meant one value
Fix 3: correlate the subquery properly
Fix 4: rewrite as a join
The fix that is not a fix
Diagnosing it in the first place
How this fits the rest of the stack
FAQ

What the error actually means

SQL distinguishes between two kinds of subquery by where it appears.

A scalar subquery appears where a single value belongs — one side of =, <, >, or in a SELECT list. It must return at most one row and one column. Return zero rows and you get NULL; return two rows and you get error 1242.

A row-set subquery appears where a set belongs — after IN, NOT IN, EXISTS, ANY, or ALL. It can return as many rows as it likes.

Here is the failing shape in its most common form:

SELECT name, email
FROM customers
WHERE id = (SELECT customer_id FROM orders WHERE total > 100);

If more than one order exceeds 100, the subquery returns several customer_id values and = cannot compare one value against several. Hence 1242.

The important question the error is really asking: did you expect one row, or many? If one, your data does not match your assumption and that is a bug worth understanding. If many, you used the wrong operator and the fix is one word.

Fix 1: use IN when you meant a set

This is the correct fix in the majority of cases, and it is a one-word change:

SELECT name, email
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 100);

IN accepts any number of rows and tests membership. Semantically this is almost always what the query meant: every customer with a large order, not the single customer with one.

One caution that catches people out. NOT IN behaves unexpectedly when the subquery can return NULL. If any returned value is NULL, NOT IN evaluates to NULL rather than TRUE for every row, and the query returns nothing at all with no error. Guard against it:

SELECT name FROM customers
WHERE id NOT IN (
    SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

Or use NOT EXISTS, which does not have this behaviour and is generally the safer construction for anti-joins.

Fix 2: aggregate when you meant one value

Sometimes you genuinely want a single value, and the subquery is returning many because you have not told it how to collapse them.

-- Fails: many order dates per customer
SELECT name, (SELECT order_date FROM orders o WHERE o.customer_id = c.id)
FROM customers c;

-- Works: one value per customer, and states which one
SELECT name, (SELECT MAX(order_date) FROM orders o WHERE o.customer_id = c.id)
FROM customers c;

MAX, MIN, SUM, COUNT and AVG all reduce a set to a single value. The version with the aggregate is not just working code — it is more honest code, because it says in the query which of the many rows you want.

This is the fix to reach for whenever the subquery sits in the SELECT list rather than the WHERE clause.

Fix 3: correlate the subquery properly

Often the subquery returns many rows because it is missing the correlation that would restrict it to one. The clue is a subquery that does not reference the outer query at all:

-- Fails: no link to the outer row, so it returns every price
SELECT p.name
FROM products p
WHERE p.price = (SELECT price FROM price_history);

-- Works: correlated and restricted to one row
SELECT p.name
FROM products p
WHERE p.price = (
    SELECT price FROM price_history h
    WHERE h.product_id = p.id
    ORDER BY h.changed_at DESC
    LIMIT 1
);

Note that LIMIT 1 appears here legitimately, because it is paired with an ORDER BY that makes the choice deterministic. That is the distinction between using LIMIT 1 as a fix and using it as a cover-up: with an explicit ordering it means the most recent one, and without it means whichever one the engine happened to produce.

Fix 4: rewrite as a join

If the subquery is correlated and running per outer row, a join is usually clearer and frequently faster:

-- Subquery form
SELECT name, email FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 100);

-- Join form
SELECT DISTINCT c.name, c.email
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.total > 100;

The DISTINCT is doing real work here and is worth understanding rather than pasting. A join produces one output row per matching pair, so a customer with three qualifying orders appears three times. IN does not have this problem because it tests membership rather than combining rows. Rewriting IN as a join without adding DISTINCT is a quiet way to introduce duplicates.

Modern MySQL optimises many IN subqueries into semi-joins anyway, so the performance argument is weaker than it once was. Prefer the join when it reads more clearly, not automatically.

The fix that is not a fix

Adding a bare LIMIT 1 to make the error go away:

-- Compiles. Silently wrong.
SELECT name FROM customers
WHERE id = (SELECT customer_id FROM orders WHERE total > 100 LIMIT 1);

Without an ORDER BY, this returns whichever row the engine produced first, which is not defined and can change with the execution plan, an index addition, or a version upgrade. The query now returns a plausible-looking answer that may be a different plausible-looking answer tomorrow.

This is worse than the error, because the error was visible and this is not. A query that fails loudly gets fixed. A query that returns an arbitrary row gets trusted, and the resulting bug surfaces later somewhere far away from the SQL.

LIMIT 1 is legitimate with an ORDER BY that makes the selection deterministic — latest, highest, first alphabetically. It is a bug without one.

Diagnosing it in the first place

When you hit 1242 in a query complex enough that the offending subquery is not obvious, run the subquery on its own:

SELECT customer_id FROM orders WHERE total > 100;

Two useful outcomes. If it returns many rows and that is what you expected, you need IN or an aggregate. If it returns many rows and you expected one, you have found a data assumption that is not true — a missing unique constraint, duplicate rows that should not exist, or a join key that is less unique than you believed. That second case is the more valuable discovery, and it is the reason not to reach for LIMIT 1 reflexively.

In production, this class of error is a good argument for keeping query logs somewhere you can actually read them. On RunxBuild, managed MySQL and Postgres run beside your application service with backups, connection limits and private networking, and the runtime logs from the service sit in the same place as its deploy logs — see Databases on RunxBuild. A failing query is easier to fix when the error and the request that produced it are on the same screen.

How this fits the rest of the stack

Error 1242 is MySQL declining to pick a row for you, and the useful response is to decide which behaviour you meant rather than to silence it. Use IN when you meant a set, an aggregate when you meant one specific value, a correlation when the subquery was missing its link to the outer row, and a join when that reads better — remembering the DISTINCT. Reach for LIMIT 1 only with an ORDER BY beside it. If you are sizing the database and the service that runs these queries, the RunxBuild hosting calculator itemises them separately.

Useful related references:

FAQ

What causes MySQL error 1242?

A subquery used in a position that expects a single value — one side of an equality or comparison, or in the SELECT list — returned more than one row. MySQL refuses to guess which row you meant, so it raises the error instead of returning an arbitrary result.

How do I fix subquery returns more than 1 row?

Choose based on intent: use IN if you meant a set of values, an aggregate like MAX or MIN if you meant one specific value, add the missing correlation if the subquery should have been restricted to the outer row, or rewrite as a join with DISTINCT where appropriate.

Is adding LIMIT 1 a valid fix for error 1242?

Only with an ORDER BY beside it. Without one, the query returns whichever row the engine produced first, which is undefined and can change with the execution plan or a version upgrade. That converts a visible error into a silent correctness bug.

What is the difference between a scalar and a row subquery?

A scalar subquery appears where one value is expected and must return at most one row and one column. A row-set subquery appears after IN, EXISTS, ANY or ALL and may return any number of rows. Error 1242 means a scalar position received a multi-row result.

Why does NOT IN return no rows with a subquery?

Because if the subquery returns any NULL, the NOT IN comparison evaluates to NULL rather than TRUE for every row, so nothing matches and no error is raised. Filter NULLs out inside the subquery, or use NOT EXISTS, which does not have this behaviour.