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