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_IDFIRST_NAMELAST_NAME
1DanielSCOTT
2AnthonySIMMONS
3SophiaTHOMPSON
4EmilyPETERSON
5DavidDAWSON
6GabrielLEWIS

Orders table

ORDER_IDCOURSE_IDSTUDENT_ID
116
345
2156
2214

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_IDFIRST_NAMELAST_NAME
6GabrielLEWIS