Oracle / PLSQL: ORA-01416 Error Message

Learn the cause and how to resolve the ORA-01416 error message in Oracle.

Description

When you encounter an ORA-01416 error, the following error message will appear:

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

Cause

You tried to join two tables, but you performed an outer join on both tables to each other. This has created a circular outer join between the two tables.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Correct your SQL so that you are not outer joining both tables to each other. You can only perform an outer join one way.

For example, if you tried to execute the following SQL statement:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_id
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id(+)
AND suppliers.supplier_name(+) = orders.supplier_name;

You would receive the following error message:

You could correct this SQL statement by removing the circular outer join. For example:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_id
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id(+)
AND suppliers.supplier_name = orders.supplier_name(+);