ORA-01427: Subquery returns more than one row

When you run a SQL Statement with Subquery, you can get ” ORA-01427: Subquery returns more than one row ” error.

 

 

ORA-01427 single-row subquery returns more than one row

Error details are as follows.

 

SELECT *

FROM employee

WHERE emp_id = (SELECT emp_id

FROM person

WHERE person_name = 'Mehmet') ;

 

ORA-01427 single-row subquery returns more than one row

Cause: The outer query must use one of the keywords ANY, ALL, IN, or NOT IN to specify values to compare because the subquery returned more than one row.

Action: Use ANY, ALL, IN, or NOT IN to specify which values to compare or reword the query so only one row is retrieved.

 

 

ORA-01427

 

This error ORA-01427: Subquery returns more than one row is related with the keywords ANY, ALL, IN, or NOT IN to specify values to compare because the subquery returned more than one row.

 

 

single-row subquery returns more than one row

If your SQL Statement ( single row subquery ) returns only one row, then you need to use the equal comparison operators (=,<,>,<> ).

But If your SQL Statement ( multiple row subquery ) returns more than one row ( multiple rows ), then you need to use the Multiple-row Sub-query Operators like ( IN, ANY, ALL and etc.. )

 

To solve the above SQL Statement, you can use the IN operator instead of = operator as follows.

SELECT *
FROM employee
WHERE emp_id IN (SELECT emp_id
FROM person
WHERE person_name = 'Mehmet') ;

This solution is valid for both Select and Update, Insert and Delete operations.

If you got this error during the Update, Insert and Delete operations, then Check your Statement if Subquery returns more than one row, then use the Multiple-row Sub-query Operators like ( IN, ANY, ALL and etc.. )

 

Do you want to learn more details about Oracle SQL, then read the following articles.

Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course