EC
1242
MariaDBERRORCommonQuery SyntaxHIGH confidence

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.

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

expected output

ERROR 1242 (21000): Subquery returns more than 1 row

Fix 1

Use IN instead of = for multi-row subqueries

WHEN When any matching row is acceptable.

Use IN instead of = for multi-row subqueries
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'PT'
);

Why this works

IN accepts a set of values, making it the correct operator when the subquery may return multiple rows.

Fix 2

Add LIMIT 1 or aggregation to force a scalar result

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

Add LIMIT 1 or aggregation to force a scalar result
SELECT * FROM orders
WHERE customer_id = (
  SELECT id FROM customers WHERE country = 'PT'
  ORDER BY created_at DESC
  LIMIT 1
);

Why this works

LIMIT 1 guarantees at most one row is returned, making the subquery safe to use with =.

Fix 3

Use EXISTS for existence checks

WHEN When checking if any matching row exists.

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

Why this works

EXISTS short-circuits on the first match and is not affected by how many rows the subquery returns.

What not to do

✕ Blindly add LIMIT 1 without an ORDER BY

Without ORDER BY, the row returned is non-deterministic and results will vary across queries.

Sources
Official documentation ↗

MariaDB Server error code 1242 / ER_SUBQUERY_NO_1_ROW

MariaDB Subqueries ↗MariaDB IN Operator ↗

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MariaDB errors