single-row subquery returns more than one row in Oracle

ORA-01427: single-row subquery returns more than one row error occures when we are comparing two or more values with one using equals to (=) sign in subquery.

We can avoid this error using IN clause shown below:-

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

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

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');

SQL>
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

Above sql statement is not working. Let's try to use below on with IN clause:-

SQL>
SQL>
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.