Subquery returns more than 1 row

What this means

Error 1242 (SQLSTATE 21000) is raised when a scalar subquery — one used in a context that expects a single value (e.g., WHERE col = (subquery)) — returns more than one row. The comparison operator = requires exactly one value on the right side.

Why it happens

1. 1Subquery in WHERE col = (...) returns multiple rows instead of one
2. 2Subquery used as a scalar expression in SELECT returns multiple rows
3. 3Missing LIMIT 1 or missing aggregation on a subquery expected to be scalar

How to reproduce

Equality comparison against a subquery that returns multiple rows.

SELECT * FROM orders
WHERE customer_id = (
  SELECT id FROM customers WHERE country = 'PT'
);
-- Multiple customers exist in Portugal

Fix 1: Use IN instead of = for multi-row subqueries

When any matching row is acceptable.

SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'PT'
);

Fix 2: Add LIMIT 1 or aggregation to force a scalar result

When only one specific row is expected (e.g., the most recent).

SELECT * FROM orders
WHERE customer_id = (
  SELECT id FROM customers WHERE country = 'PT'
  ORDER BY created_at DESC
  LIMIT 1
);

Fix 3: Use EXISTS for existence checks

When checking if any matching row exists.

SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM customers c
  WHERE c.id = o.customer_id AND c.country = 'PT'
);

What not to do

Sources

Confidence assessment

See also