ORA-01416: two tables cannot be outer-joined to each other

ORA-01416: two tables cannot be outer-joined to each other

Oracle PL/SQL error message: ORA-01416: two tables cannot be outer-joined to each other.

Cause:

Two tables in a join operation specified an outer join with respect to each other. If an outer join is specified on one of the tables in a join condition, it may not be specified on the other table.

Solution:

Remove the outer join specification (+) from one of the tables, then retry the operation.

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, orders o 
where s.student_id = s.student_id(+)
order by s.student_id;

Output:

ORA-01416: two tables cannot be outer-joined to each other

Correct:

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

Output:

STUDENT_IDFIRST_NAMELAST_NAME
1DanielSCOTT
2AnthonySIMMONS
3SophiaTHOMPSON
4EmilyPETERSON
5DavidDAWSON
6GabrielLEWIS