Tag Archives: Join condition

ORA-00918: column ambiguously defined

ORA-00918: column ambiguously defined

If you are using the join condition between two tables then both table having the same name column then following error occurred:

SQL> select first_name , department_id from hr.employees emp , hr.departme
nts dept where emp.department_id = dept.department_id;
select first_name , department_id from hr.employees emp , hr.departments d
ept where emp.department_id = dept.department_id
ERROR at line 1:
ORA-00918: column ambiguously defined


In the we are using two table EMPLOYEES and DEPARTMENTS having one common column DEPARTMENT_ID which caused the error in SQL Statements. We need to use alias name or table reference with the column name. It will fixed the issue.

In following example, when we executed the above query we got the error because in SELECT Query we miss the use of table reference or table alias in front of using that common column. It make difficult for Oracle to understand that column belong to which table.

Highlighted the mistake done in the previous Query.
Use table alias or table reference in front of the common column

SQL> select first_name , emp.department_id from hr.employees emp , hr.departments dept where emp.department_id = dept.department_id;

---------- -------------
Ellen 80
Sundar 80