How to Resolve SQL Ambiguous column Error

The ambiguous column error is common in SQL. I found a few scenarios where people or developers confused with this error.

What is Ambiguous Column?

The ambiguous columns mean, SQL does not understand which column it has to consider.

Let me explain in detail. In the classroom, there are two boys with the same name…

Okay.

If a teacher calls the name, both boys look up their heads. So, it is confusing to the teacher and the classroom…..students who are sitting inside.

So, the teacher made some changes…

Added some uniqueness to each boy’s name…

Then, the problem is resolved.

How to Resolve Ambiguous column error?

The fix or resolution for the ambiguous column is as follows:

SQL Query

SELECT invoice_numb, 
vendor_name
FROM vendors
INNER JOIN invoices
ON vendor_id = vendor_id
ORDER BY invoice_numb;

In the above query, the incorrectness is you are joining on vendor_id. The problem is vendor_id is present in both the tables. This is called ambiguous.

Modified Query

 SELECT invoice_numb, 
vendor_name
FROM vendors
INNER JOIN invoices
ON vendors.vendor_id = invoices.vendor_id
ORDER BY invoice_numb;

According to BURLESON, A column name used in a join exists in more than one table and is thus referenced ambiguously.

How I fixed?

In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced.

The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN. For example, if tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO. Read his example here.

Share to your friends how to resolve ambiguous column error.

According to Oracle documents.

ORA-00918 column ambiguously defined

Cause: A column name used in a join exists in more than one table and is thus referenced ambiguously.

In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced. The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN.

For example, if tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.

Action: Prefix references to column names that exist in multiple tables with either the table name or a table alias and a period (.), as in the examples above.

Also read,

32 Tricky SQL queries you need for all interviews

Please subscribe to “Srinimf Show” and rate and review podcasts:

Follow me on Social Media:

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.

Comments are closed.