SQL ambiguous column how to solve explained with query

The name ambiguous column is very common error in SQL. I found many scenarios where people or developers confused with this error.

The ambiguous columns means – SQL does not understand which column it has to consider. SQL boot-camp is really good to know all these things.

Let me explain in detail…

In a classroom there are two boys with same name…

Okay…

If teacher calls the name, both boys look up their head. So, it is confusion to teacher and the class room…..students who are sitting inside.

So, teacher made some change…

Added some uniqueness to each boy name…

Then, problem is resolved.

Enter your email address to follow this blog and receive notifications of new posts by email.

The fix or resolution for 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.

The modified query is…

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

According to BURLESON, 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. Read his example here.

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:

Advertisements

Author: Srini

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

Start Discussion

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s