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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.