How to Fix ORA-00918: Column Ambiguously Defined in Oracle

ORA-00918 means a query refers to a column name that exists in more than one table or view of the FROM clause, without saying which one it means. Oracle refuses to guess. It typically appears after adding a join to a working query, when both tables have a column with the same name, such as an ID or a NAME. The fix is to qualify the column with its table name or alias.

Reproduce the Error

Both tables have dept_id and name columns, and the select list uses them without a table prefix:

Example:

create table demo_dept (dept_id number primary key, name varchar2(30));
create table demo_emp  (emp_id number, name varchar2(30), dept_id number references demo_dept);
insert into demo_dept values (10, 'Sales');
insert into demo_emp  values (1, 'Mia', 10);
commit;

select name, dept_id
from   demo_emp join demo_dept on demo_dept.dept_id = demo_emp.dept_id;

Output:

Table DEMO_DEPT created.

Table DEMO_EMP created.

1 row inserted.

1 row inserted.

Commit complete.

Error starting at line : 7
In command -
select name, dept_id
from   demo_emp join demo_dept on demo_dept.dept_id = demo_emp.dept_id
Error at Command Line : 7 Column : 14
Error report -
SQL Error: ORA-00918: DEPT_ID: column ambiguously specified - appears in  and

Oracle AI Database 26ai names the ambiguous column, DEPT_ID, at the start of the message; older releases show only column ambiguously defined. In this test the table names after appears in were left blank.

Fix: Qualify the Columns

Prefix each shared column with its table alias. With JOIN ... USING, the join column becomes one column that is written without a prefix:

Example:

select e.name as employee, d.name as department, e.dept_id
from   demo_emp e join demo_dept d on d.dept_id = e.dept_id;

-- USING makes the join column one column, written without a prefix
select e.name as employee, dept_id
from   demo_emp e join demo_dept d using (dept_id);

Output:

EMPLOYEE    DEPARTMENT       DEPT_ID
___________ _____________ __________
Mia         Sales                 10

EMPLOYEE       DEPT_ID
___________ __________
Mia                 10

Other Common Causes

• SELECT * over a join, wrapped in an outer query or a view: the inner result has two columns with the same name.
• A column in ORDER BY that exists in two joined tables and is not in the select list under a unique alias.
• An UPDATE or DELETE with a subquery that joins the target table again without aliases.

Things to Know

• Qualify every column in queries with more than one table, even when the name is unique today; adding a column to a table later can make it ambiguous.
• Give columns with the same name different aliases in the select list, such as employee and department.
• With USING, the join column must not be qualified at all.

Related Guides

• Oracle SQL INNER JOIN: Complete Guide to Matching Records Between Tables
• How to Fix ORA-00904: Invalid Identifier in Oracle

Conclusion

ORA-00918 means a column name matches more than one table in the query. Qualify it with a table alias, give same-named columns different aliases in the select list, or join with USING for the shared key.