SQL Error: Single-Row Subquery Returns More Than One Row

This error means SQL expected one value from a subquery but received two or more rows instead. In Oracle, the message is ORA-01427: single-row subquery returns more than one row. The fix is not to suppress extra rows blindly: first identify the subquery and outer-row values involved, then decide whether the relationship should return one row or a set of rows.

Use this table to match the failing SQL construct to the appropriate repair.

Symptom	Likely Cause	Correct Fix
`= (subquery)` fails	The subquery returns multiple matching values	Use `IN`, `ANY`, or `ALL` only if multiple values are valid
A `SELECT`-list subquery fails	A supposedly one-to-one lookup is actually one-to-many	Add the missing predicate, clean duplicate data, or use a meaningful aggregate
`UPDATE ... SET column = (subquery)` fails	More than one source row matches the row being updated	Make the lookup unique before updating
The query worked previously	New or changed data introduced another match	Find the duplicate or changed relationship and enforce the intended rule
A query uses “first row” logic	The SQL is discarding a row without a defined business rule	Add deterministic ordering only when the business rule defines which row wins

What the Single-Row Subquery Error Means

A subquery is a query inside another SQL statement. Whether it may return many rows depends on where and how you use it.

A scalar subquery is one used where SQL needs a single value, such as:

The right side of =, <, >, or another single-value comparison
A column expression in a SELECT list
The value assigned by UPDATE ... SET column = (...)
An expression in an INSERT or MERGE

For example, this query requires the subquery to return no more than one customer_id for each order:

SELECT o.order_id
FROM orders o
WHERE o.customer_id = (
    SELECT c.customer_id
    FROM customers c
    WHERE c.email = o.billing_email
);

If two customers have the same email address, the subquery returns two rows. SQL cannot decide whether o.customer_id should be compared with the first value, second value, or both values, so it raises the error.

A scalar subquery can return zero rows, but that has different behavior: it evaluates to NULL. In a comparison such as column = (subquery), comparing to NULL does not match rows. The error occurs specifically when the scalar subquery returns more than one row.

By contrast, a subquery used with IN is expected to return a set:

SELECT o.order_id
FROM orders o
WHERE o.customer_id IN (
    SELECT c.customer_id
    FROM customers c
    WHERE c.email = o.billing_email
);

How to Fix the Single-Row Subquery Error

Fix the error by determining whether the scalar subquery should return one row or several, then apply the matching SQL change.

Step 1: Find the Scalar Subquery

Start with the statement Oracle reports as failing. Look for subqueries inside parentheses after constructs that expect a single value:

= (SELECT ...)
<> (SELECT ...)
< (SELECT ...)
SET column_name = (SELECT ...)
SELECT (SELECT ...) AS column_name

Do not assume the first nested query you see is the problem. A large statement can contain several scalar subqueries, and a correlated subquery may fail only for certain outer rows.

A correlated subquery references a value from the outer query, as c.email = o.billing_email does in the earlier example. That means it may return one row for most orders and several rows for only one problematic order.

Step 2: Run the Inner Query for the Failing Outer-Row Values

Copy the subquery and replace its outer reference with the value from the row that fails. For example, if an order has billing_email = '[email protected]', run:

SELECT c.customer_id, c.email
FROM customers c
WHERE c.email = '[email protected]';

If this returns multiple rows, you have confirmed the immediate cause.

Next, find every outer row that would produce multiple matches. Re-create the correlation as a join, retaining the same predicates used by the original subquery:

SELECT
    o.order_id,
    o.billing_email,
    COUNT(*) AS matching_customers
FROM orders o
JOIN customers c
    ON c.email = o.billing_email
GROUP BY
    o.order_id,
    o.billing_email
HAVING COUNT(*) > 1;

If the original subquery includes filters such as status, tenant ID, effective date, or record type, include those exact conditions in the diagnostic query. A generic duplicate check may find unrelated duplicates while missing the rows that actually trigger the failure.

I use a database client to run the isolated inner query first, because it separates a data-cardinality problem from the complexity of the surrounding statement.

Step 3: Decide Whether One Match or Many Matches Is Correct

Before changing SQL syntax, determine the intended relationship.

Ask these questions:

Should the outer row have exactly one related row?
Can it legitimately have several related rows?
If several rows are possible, does the query need to match any of them, all of them, the newest one, or an aggregate result?
Are the extra rows truly duplicate data, or are they separate business records that the old query failed to account for?

If multiple matches are valid, use a set comparison; if exactly one match is required, tighten the predicates or correct the duplicate data rather than arbitrarily discarding a row.

This decision is the important part of the repair. Replacing = with IN, adding DISTINCT, or choosing a row with MAX() can remove the error while silently changing what the query means.

Step 4: Apply the Smallest Correct SQL Change

Choose the repair that matches the relationship you identified.

If one related record is required, add the predicate that makes it unique. Common missing conditions include:

Tenant, organization, or account ID
Current-record indicator
Active status
Effective-date range
Record type
A more specific key from the outer row

Then fix any duplicate data that violates the intended business rule. Once existing rows are clean, a unique constraint or unique index on the real business key can prevent the same failure from returning later.

If several matching values are valid and the outer value should match any of them, use IN:

SELECT o.order_id
FROM orders o
WHERE o.customer_id IN (
    SELECT c.customer_id
    FROM customers c
    WHERE c.email = o.billing_email
);

If the query needs a calculated result from multiple related rows, use an aggregate only when the aggregate is the intended answer. For example, counting employees per department is a valid scalar aggregate:

SELECT
    d.department_id,
    (
        SELECT COUNT(*)
        FROM employees e
        WHERE e.department_id = d.department_id
    ) AS employee_count
FROM departments d;

After changing the query, rerun the isolated subquery, the full statement, and a targeted test for the rows that previously produced multiple matches.

Choose the Right Multi-Row Comparison

Changing a scalar comparison to a multi-row comparison is correct only when the question itself is about a set.

Use IN for Membership

Use IN when a value may equal one of several values returned by the subquery.

SELECT e.employee_id
FROM employees e
WHERE e.department_id IN (
    SELECT d.department_id
    FROM departments d
    WHERE d.region = 'West'
);

This asks: “Is this employee’s department among the departments in the West region?”

IN is usually clearer than a chain of OR conditions and is the usual repair when = (subquery) was incorrectly used for a many-row lookup.

Use EXISTS when you need to know whether a related row exists, rather than retrieve and compare a particular value.

SELECT e.employee_id
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
      AND d.region = 'West'
);

This form works well for correlated conditions and avoids treating the subquery as a scalar value.

Use ANY and ALL for Comparisons

Use ANY or ALL when comparing a value to a set with an operator.

SELECT p.product_id
FROM products p
WHERE p.price > ANY (
    SELECT s.price
    FROM sale_prices s
    WHERE s.sale_name = 'Clearance'
);

> ANY means greater than at least one returned price. > ALL means greater than every returned price. These are not interchangeable, so use them only when the business rule calls for that exact comparison.

Be Careful With NOT IN and NULL

NOT IN can produce surprising results if the subquery returns NULL. A comparison against a set containing NULL becomes unknown rather than true, which can cause no rows to be returned.

For exclusion checks where the inner value may be null, NOT EXISTS is often clearer:

SELECT e.employee_id
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM terminated_employees t
    WHERE t.employee_id = e.employee_id
);

When You Really Need One Row

Sometimes the result must be a single row, but several related rows are valid in the database. In that case, the SQL needs a defined selection rule.

For example, if the business rule is “use the most recently created customer record,” make that rule explicit with an ORDER BY and a stable tie-breaker:

SELECT c.customer_id
FROM customers c
WHERE c.email = '[email protected]'
ORDER BY c.created_at DESC, c.customer_id DESC
FETCH FIRST 1 ROW ONLY;

This is appropriate only when choosing the newest record is a real requirement. Without an ORDER BY, a row-limiting clause does not express which row should win. It merely hides the ambiguity.

Similarly, MAX(), MIN(), and DISTINCT are not generic fixes:

MAX() is correct when you need the highest value.
MIN() is correct when you need the lowest value.
DISTINCT is correct when duplicate projected values are semantically identical.
None of them is correct merely because the query needs to stop failing.

For an UPDATE, be especially careful. A scalar subquery in the SET clause must produce at most one value per row being updated. Also verify that the outer UPDATE has the intended WHERE clause before running it; fixing the subquery does not protect against updating every row.

Diagnose Deeply Nested Oracle Queries

Oracle allows WHERE-clause subqueries nested up to 255 levels. Long nested statements can therefore be valid SQL while still being difficult to debug.

Format the query so each subquery is indented and every table has a distinct alias. Then work from the innermost query outward:

Run each uncorrelated subquery by itself.
For correlated subqueries, substitute a known failing outer-row value.
Record the expected cardinality: zero or one row, exactly one row, or many rows.
Check every scalar location separately.
Replace nested logic with joins or common table expressions temporarily if that makes the matching relationships easier to inspect.

A common source of the error is incomplete correlation. For example, a subquery may join on customer_id but omit organization_id, causing records from several organizations to match. The query may appear correct in a small test database and fail only after additional data is loaded.

A common table expression can make this investigation easier because you can run each logical stage independently. It is not automatically faster than a subquery; use it first for readability and to make row counts visible.

Confirm the Fix and Prevent the Error From Returning

After the query runs, verify that it returns the right answer, not merely that it no longer raises an error.

Use this checklist:

Run the formerly failing outer-row values again.
Confirm the scalar subquery now returns zero or one row where appropriate.
Confirm a set-based rewrite returns all intended matches.
Test rows with no related record, one related record, and multiple related records.
Check that an aggregate returns the intended business value.
Review NULL behavior, especially after changing to NOT IN.
Add or repair data constraints when the relationship is supposed to be unique.

If the statement worked in the past, compare the newly matching rows with the old assumptions in the SQL. A new customer record, changed status, missing end date, duplicated import, or removed uniqueness constraint can turn a formerly one-row lookup into a many-row lookup.

Frequently Asked Questions

Can a subquery return multiple rows?

Yes. A subquery can return multiple rows when it is used in a set context such as IN, EXISTS, ANY, ALL, or a FROM clause. It cannot return multiple rows where SQL requires a scalar value, such as the right side of = (subquery) or a single SELECT-list expression.

How can I get one row in SQL?

Get one row by defining why that row is the correct one. Add predicates that identify a unique record, or use ORDER BY with FETCH FIRST 1 ROW ONLY when the business rule explicitly selects a row such as the newest record. Do not limit rows without ordering, because the selected row is not a reliable business decision.

Why did ORA-01427 start happening when the query used to work?

ORA-01427 usually starts when data changes cause a formerly one-row subquery to return multiple matches. The query may have depended on an unstated uniqueness assumption, such as one active record per customer or one row per tenant and date. Find the new matching rows, then either enforce uniqueness or rewrite the query to handle a valid one-to-many relationship.

Is a CTE faster than a subquery?

A common table expression is not inherently faster than a subquery. Use a CTE when it makes joins, filters, and intermediate row counts easier to read and test; then inspect the execution plan if performance matters. The key fix for this error is correct row cardinality, not whether the SQL uses a CTE.

Should I use DISTINCT to fix a single-row subquery error?

Use DISTINCT only when repeated rows represent the same meaningful result after projection. If the subquery returns two different IDs, dates, statuses, or other business values, DISTINCT will not solve the underlying ambiguity. Start by running the subquery for the failing outer-row values and decide whether the extra row is valid data or a data-quality problem.