If you’re getting an error that reads “Ambiguous column name“, followed by a column name, in SQL Server, it could be that you’re performing a join on two tables, but you’re not using the table names or aliases when referring to columns within those tables.

To fix this issue, be sure to qualify the columns with their table names or aliases.

Example of Error

Here’s an example of code that produces the error:

SELECT
    ArtistName,
    AlbumName
FROM Artists
JOIN Albums
ON ArtistId = ArtistId;

Output:

Msg 209, Level 16, State 1, Line 6
Ambiguous column name 'ArtistId'.
Msg 209, Level 16, State 1, Line 6
Ambiguous column name 'ArtistId'.

In this case I got the error twice. That’s because the same problem applied to both columns in my query; they both weren’t qualified with their respective table name. In this case, both tables have an ArtistId column. That’s why SQL Server got confused – it didn’t know which one we were referring to each time we named the column.

We could get the same error with a subquery:

SELECT 
    ds.sale_date,
    ds.total_sales,
    SUM(prev.total_sales) AS running_total
FROM 
    daily_sales AS ds
JOIN (
    SELECT sale_date, total_sales
    FROM daily_sales
) AS prev ON sale_date <= sale_date
GROUP BY ds.sale_date, ds.total_sales
ORDER BY ds.sale_date;

Output:

Msg 209, Level 16, State 1, Line 10
Ambiguous column name 'sale_date'.

Here, the subquery has an alias, but I didn’t refer to it in the ON clause. In this case I only got the error once because only one of the columns wasn’t qualified.

Solution

To fix this issue, we need to qualify the column names with the table names or table aliases.

Here’s an example of fixing the first query:

SELECT
    ar.ArtistName,
    al.AlbumName
FROM Artists ar
JOIN Albums al
ON ar.ArtistId = al.ArtistId;

Here, I added table aliases to the tables, and then I referenced those aliases when referring to their columns. The above error was caused by the columns in the ON clause, and so that’s the part that resolved the issue. In my case, the columns in the SELECT list are not common to both tables (i.e. only one table contains ArtistName and only one table contains AlbumName) and so qualifying these columns is optional. That said, it’s good practice to qualify all column names like this when using joins.

Here’s an example of fixing the second query:

SELECT 
    ds.sale_date,
    ds.total_sales,
    SUM(prev.total_sales) AS running_total
FROM 
    daily_sales AS ds
JOIN (
    SELECT sale_date, total_sales
    FROM daily_sales
) AS prev ON prev.sale_date <= ds.sale_date
GROUP BY ds.sale_date, ds.total_sales
ORDER BY ds.sale_date;

In this case I qualified the column names in the ON clause, so that it became ON prev.sale_date <= ds.sale_date. Now SQL Server knows exactly which columns we’re referring to and it can carry on with processing the query.