ORA-00918: column ambiguously defined

A column name exists in more than one table of the query and Oracle cannot tell which one you mean. Prefix the column with the table alias.

What ORA-00918 means

When a join brings together tables that share a column name, an unqualified reference to that name is ambiguous. Oracle refuses to guess and returns ORA-00918. The same happens when a subquery exposes two columns with the same name.

Common causes of ORA-00918

A join in which both tables have a column such as ID or NAME.
An unqualified name in WHERE, ORDER BY or the SELECT list.
A subquery that selects two columns with the same name.
A new column added to one table, which makes a previously working query ambiguous.

How to fix ORA-00918

Qualify every column in a join with its table alias.
Give duplicate columns distinct aliases in subqueries.
Make it a habit in joins even when the query works today: a later ALTER TABLE cannot break it then.

ORA-00918 example

Both tables have an ID column:

-- ORA-00918: column ambiguously defined
SELECT id FROM orders o JOIN order_lines l ON l.order_id = o.id;

-- Works
SELECT o.id FROM orders o JOIN order_lines l ON l.order_id = o.id;

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Try SQLlightspeed free for 30 days

Oracle documentation for ORA-00918. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.