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

Everyday developers, testers and newbie’s face ORA-01427 error and I have seen many users looking for a solution for this error.

First let’s understand why users receive this error.

SQL> select count(*) from employee where employee_id = (select employee_id from salary where gross_sal < 5000 );
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

This error is raised when a sub-query returns more than one row.

Make sure you use multiple row sub query operator instead of single row sub-query operator to resolve the issue.

SQL> select count(*) from employee where employee_id in (select employee_id from salary where gross_sal < 5000 );

Now this query works fine.

Single row sub query operators are = (equal) , < (less than) , <=( less than or equal) , >(greater than) , >= (greater than or equal), <>(not equal ) ,!=(not equal)

Multiple row sub query operators are IN (equal to any member in the list), ANY (compare value to each value returned by the subquery), ALL (compare value to every value returned by the subquery)

Few guide lines for using Sub queries:
1. Use single-row operators with sing row sub queries
2. Use multiple-row operators with multiple row sub queries
3. Make sure you enclose sub queries in parentheses
4. Always keep sub queries on the right side of the comparison operator
5. You can use inline view to avoid ORA-01427 error, please refer http://docs.oracle.com/cd/E11882_01/server.112/e25554/qradv.htm#DWHSG08032

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com/