SQL Server Error Messages - Msg 209¶

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.