Skip Navigation Links
Home
Functions
Tips & Tricks
SQL Server 2005
SQL Server 2008
SQL Server 2012
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : 2 Ways to Execute Operating System Commands From SQL Server Management Studio
Error Messages
Home > SQL Server Error Messages > Msg 209 - Ambiguous column name '<Column Name>'.
SQL Server Error Messages - Msg 209 - Ambiguous column name '<Column Name>'.

Error Message

Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name '<Column Name>'.

Causes:

This error happens when you are joining 2 or more tables in a statement and there are columns among these tables that have the same name and you didn’t prefix the column name with the table name when referencing the column in your statement.

To illustrate, let’s say you have the following tables which contains the Employees and Supervisors of your company:

CREATE TABLE [dbo].[Supervisors] (
    [SupervisorID]      INT,
    [FirstName]         VARCHAR(50),
    [LastName]          VARCHAR(50)
)
CREATE TABLE [dbo].[Employees] (
    [EmployeeID]        INT,
    [FirstName]         VARCHAR(50),
    [LastName]          VARCHAR(50),
    [SupervisorID]      INT
)

You want to generate a list of your employees together with the name of their corresponding supervisors. In doing so, you issued the following SELECT statement:

SELECT [FirstName] AS [EmployeeFirstName],
       [LastName]  AS [EmployeeLastName], 
       [FirstName] AS [SupervisorFirstName],
       [LastName]  AS [SupervisorLastName]
FROM [dbo].[Employees] Emp INNER JOIN [dbo].[Supervisors] Sup
ON Emp.[SupervisorID] = Sup.[SupervisorID]

Issuing this SELECT statement in Query Analyzer will generate the following errors:

Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'FirstName'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'LastName'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'FirstName'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'LastName'.

Solution / Work Around:

To avoid this error, make sure that you prefix the column name with the table name or table alias for those columns that exist in more than 1 table. Here’s an updated SELECT statement that will not generate the error:

SELECT Emp.[FirstName] AS [EmployeeFirstName],
       Emp.[LastName]  AS [EmployeeLastName], 
       Sup.[FirstName] AS [SupervisorFirstName],
       Sup.[LastName]  AS [SupervisorLastName]
FROM [dbo].[Employees] Emp INNER JOIN [dbo].[Supervisors] Sup
ON Emp.[SupervisorID] = Sup.[SupervisorID]

It is a good practice to always prefix column names with the table name or table alias so that in case additional columns are added to a table that have the same name as existing columns in another table, you won’t encounter this error.

Related Articles :