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

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

Oracle PL/SQL error message: 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.

Solution:

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

Students table

STUDENT_ID	FIRST_NAME	LAST_NAME
1	Daniel	SCOTT
2	Anthony	SIMMONS
3	Sophia	THOMPSON
4	Emily	PETERSON
5	David	DAWSON
6	Gabriel	LEWIS

Orders table

ORDER_ID	COURSE_ID	STUDENT_ID
1	1	6
3	4	5
21	5	6
22	1	4

Example:

select s.student_id, s.first_name, s.last_name
from students s 
where s.student_id = (select o.student_id 
						from orders o 
						where o.student_id=6
					);

Output:

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

Correct:

select s.student_id, s.first_name, s.last_name 
from students s 
where s.student_id IN (select o.student_id 
						from orders o 
						where o.student_id=6
					);

Output:

STUDENT_ID	FIRST_NAME	LAST_NAME
6	Gabriel	LEWIS