Solved: SQL ambiguous column name [100% Working]

What is an ambiguous column name error in SQL?

SQL ambiguous column name is one most common error we are facing while performing a join query with two or more tables, this type of error occurs when we try to retrieve data from two or more tables using SQL join and more than one tables have the same column name appears in the selection.

 

What is the cause of this error?

The root cause of this error is the same column name in two or more tables and selecting the same column with the same name when performing a join

 

According to Oracle documents

ORA-00918 column ambiguously defined

• Cause of error: When the same column name exists in more than one table in a join than one table and is thus referenced ambiguously.
• Action: To overcome this error we need to prefix references to column names that exist in multiple tables with either the table name or a table alias and a period (.),

 

Example of SQL ambiguous column name

Create two tables for patient and doctor as follow

patient_table(patient_id,patient_name,doctor_id,city)

doctor_table(doctor_id,doctor_name,city)

Next create PATIENT Table

Create table patient_table
(
   patient_id int primary key, 
   patient_name varchar(20),
   doctor_id int,
   city varchar(20)
)

Next create DOCTOR Table

Create table doctor_table
(
   doctor_id int primary key, 
   doctor_name varchar(20),
   city varchar(20)
)

 

Example 1: Write SQL query to display all patient data with doctor id and doctor city name

select patient_id as 'Patient ID' ,patient_name as 'Patient Name',doctor_id as 'Doctor ID',city as 'Doctor City' from patient_table , doctor_table where patient_table.doctor_id=doctor_table.doctor_id

• In the above query, we used an inner join between two tables patient and doctor to retrieve data from both the tables
• In the above query we specify city and doctor_id columns, both the columns are common in both the tables so we will get an ambiguous error for both the columns

OUTPUT:

Error Message :

Msg 209,Level 16,State 1,Line 1
Ambiguous column name 'doctor_id'
Msg 209,Level 16,State 1,Line 1
Ambiguous column name 'city'

 

Solve the “Ambiguous Column Name” Error in SQL

To solve the ambiguous column name error we need to prefixed column name by its table name when referenced with the SQL select statement, the column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN

Example 2 : Write SQL query to display all patient data with doctor id and doctor city name , also specify table name with column name in the SQL select statement

select patient_table.patient_id as 'Patient ID' ,patient_table.patient_name as 'Patient Name',doctor_table.doctor_id as 'Doctor ID',doctor_table.city as 'Doctor City' from patient_table , doctor_table where patient_table.doctor_id=doctor_table.doctor_id

OUTPUT:

 

Summary

In this article on SQL ambiguous column names, we have covered what is ambiguous column name error in SQL, the cause of ambiguous column name error, how this error explained in oracle document ORA-00918 and also explained SSQLambiguous column name error with practical examples.

 

References

SQL joins

 

Read More

SQL Ambiguous Column Name

 

Related Keywords: ambiguous column name join, sql ambiguous column name, ambiguous column name, ambiguous column name sql, column reference is ambiguous, ms sql ambiguous column name, sql query ambiguous column name