ORA-01427 in Oracle

ORA-01427: single-row subquery returns more than one row.
This error occurs when we try to compare multiple values with one value in Oracle.

Let's see how this error  occurs and how we can get rid of it.

Let's create two tables:-

CREATE TABLE P_TEST
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(10),
DEPTNO NUMBER
);

CREATE TABLE P_TEST_2
(
DEPTNO NUMBER,
DEPTNAME VARCHAR2(10)
);

Insert some data into it:-

INSERT INTO P_TEST

VALUES (1,'CHANCHAL',10);

INSERT INTO P_TEST

VALUES (2,'WANKHADE',20);

INSERT INTO P_TEST

VALUES (3,'SCOTT',30);

INSERT INTO P_TEST_2

VALUES (10,'FIANANCE');

INSERT INTO P_TEST_2

VALUES (20,'HR');

INSERT INTO P_TEST_2

VALUES (30,'IT');

Now try to compare the Deptno with subquery:-

SQL> SELECT DEPTNO FROM P_TEST

  2  WHERE DEPTNO =

  3  (SELECT DEPTNO FROM P_TEST_2);

(SELECT DEPTNO FROM P_TEST_2)

 *

ERROR at line 3:

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

It's showing error. 

To avoid this error, we need to use IN operator instate of  equals(=) operator shown below:-

SQL> SELECT * FROM P_TEST

  2  WHERE DEPTNO IN

  3  (SELECT DEPTNO FROM P_TEST_2);

    EMP_NO     EMP_NAME          DEPTNO

   ----------       ----------               ----------

         1              CHANCHAL                    10

         2              WANKHADE                   20

         3               SCOTT                         30

3 rows selected.

Read Also:-