If you’re getting an error that reads something like “1052 (23000): Column ‘name’ in field list is ambiguous” in MySQL, it looks like you could be referencing a column name in a query without qualifying it with the table name.

This can happen when you perform a join between tables that use the same name for one or more columns.

To fix this issue, be sure to qualify column names with the table names when performing joins across tables.

Example of Error

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

SELECT
    id,
    name,
    location
FROM employees
JOIN departments 
    ON employees.department_id = departments.department_id;

Output:

1052 (23000): Column 'name' in field list is ambiguous

At first glance, this query might look fine. It might be difficult to see what we did wrong. But the error message provides a clue.

Now let’s look at the tables that we were trying to query:

Employees:
+----+---------------+---------------+----------+
| id | name          | department_id | salary   |
+----+---------------+---------------+----------+
|  1 | Blake Haley   |             1 | 60000.00 |
|  2 | Cindy Tesha   |             2 | 75000.00 |
|  3 | Sammy Fulcher |             2 | 72000.00 |
|  4 | Amanda Green  |             3 | 50000.00 |
|  5 | Billy White   |          NULL | 55000.00 |
+----+---------------+---------------+----------+

Departments:
+---------------+-----------------+---------------+
| department_id | name            | location      |
+---------------+-----------------+---------------+
|             1 | Human Resources | New York      |
|             2 | Engineering     | San Francisco |
|             3 | Marketing       | Chicago       |
+---------------+-----------------+---------------+

We can see that both tables have a name column. Therein lies the problem.

Our query was asking for the name column, but MySQL didn’t know which name column to return – the one in the Employees table or the one in the Departments table?

Solution

To fix this issue, we must qualify the column name with the table name for any columns that share the same name between tables:

SELECT
    id,
    employees.name,
    location
FROM employees
JOIN departments 
    ON employees.department_id = departments.department_id;

Output:

+----+---------------+---------------+
| id | name          | location      |
+----+---------------+---------------+
|  1 | Blake Haley   | New York      |
|  2 | Cindy Tesha   | San Francisco |
|  3 | Sammy Fulcher | San Francisco |
|  4 | Amanda Green  | Chicago       |
+----+---------------+---------------+

This time it worked without error.

Note that we didn’t need to qualify the other two columns. That’s because those columns are unique within the query – only one table has an id column and only one has a location column.

That said, it’s good practice to qualify all column names with the table names – even if there aren’t any duplicate names between tables. Therefore, we can modify our query as follows:

SELECT
    employees.id,
    employees.name,
    departments.location
FROM employees
JOIN departments 
    ON employees.department_id = departments.department_id;

And to take it a step further, we can add table aliases to make our code more concise:

SELECT
    e.id,
    e.name,
    d.location
FROM employees e
JOIN departments d
    ON e.department_id = d.department_id;