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_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, 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_ID	FIRST_NAME	LAST_NAME
1	Daniel	SCOTT
2	Anthony	SIMMONS
3	Sophia	THOMPSON
4	Emily	PETERSON
5	David	DAWSON
6	Gabriel	LEWIS