What causes ORA-01427?

A lookup subquery returns multiple matches because the lookup key is not unique. Pipeline SQL was designed for a small reference table that has since grown. Cartesian join in the subquery produces multiple rows. Missing WHERE clause in the subquery

How do I fix ORA-01427?

Step 1: Change = to IN if multiple matching rows are acceptable: `WHERE id IN (SELECT id FROM ref WHERE ...)`.. Step 2: Add a ROWNUM = 1 or MAX/MIN aggregate to limit the subquery to one row.. Step 3: Add a UNIQUE constraint to the lookup column if duplicates should not exist.. Step 4: Investigate why the subquery returns multiple rows — it may signal a data quality issue.. Step 5: Replace the correlated subquery with a JOIN for better performance and explicit multi-row handling.

Medium severitysql

Oracle Database Error:
ORA-01427

What does this error mean?

A subquery used in a WHERE clause (=, !=, <, >) or in a column expression returned multiple rows where exactly one was expected.

Common causes

1A lookup subquery returns multiple matches because the lookup key is not unique
2Pipeline SQL was designed for a small reference table that has since grown
3Cartesian join in the subquery produces multiple rows
4Missing WHERE clause in the subquery

How to fix it

1Step 1: Change = to IN if multiple matching rows are acceptable: `WHERE id IN (SELECT id FROM ref WHERE ...)`.
2Step 2: Add a ROWNUM = 1 or MAX/MIN aggregate to limit the subquery to one row.
3Step 3: Add a UNIQUE constraint to the lookup column if duplicates should not exist.
4Step 4: Investigate why the subquery returns multiple rows — it may signal a data quality issue.
5Step 5: Replace the correlated subquery with a JOIN for better performance and explicit multi-row handling.

Example log output

ORA-01427: single-row subquery returns more than one row
SQL: SELECT * FROM orders WHERE customer_id = (SELECT id FROM customers WHERE region = 'US')

Frequently asked questions

What is the fastest fix for ORA-01427?

Change the comparison operator from = to IN: `WHERE key = (subquery)` → `WHERE key IN (subquery)`. If you truly need one row, use `WHERE key = (SELECT MAX(col) FROM ref WHERE ...)`.

Source · docs.oracle.com/error-help/db/ora-01427