Table of Contents
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
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
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
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.
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