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 |
