If you’re getting an error that reads something like “ERROR 1066 (42000): Not unique table/alias: ‘d’” in MySQL, it could be that you’re trying to assign a duplicate alias to a table. Or it could be that you’re doing a self-join without assigning table aliases.

Table names and aliases must be unique when doing queries in MySQL.

The error can also happen if you use HANDLER to open a table, but then try to open it again before closing it.

To fix this issue, be sure to use unique table aliases in your query. And if you’re using HANDLER, either close the table or continue working with it (without trying to open it again).

Example of Error

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

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

Output:

1066 (42000): Not unique table/alias: 'e'

In this case I assigned the same alias (e) to both tables, which caused the issue.

Here’s another example that produces the same error:

SELECT 
    employees.employee_id,
    employees.first_name AS employee_first_name,
    employees.last_name AS employee_last_name,
    employees.first_name AS manager_first_name,
    employees.last_name AS manager_last_name
FROM 
    employees
LEFT JOIN 
    employees ON employees.manager_id = employees.employee_id
ORDER BY 
    employees.employee_id;

Output:

1066 (42000): Not unique table/alias: 'e'

Slightly different scenario, same error.

In this case, I was doing a self-join but I didn’t even provide table aliases for the tables. This caused issues with the self-join.

As mentioned, we can also get the error when using the HANDLER statement:

HANDLER products OPEN;
HANDLER products OPEN;

Output:

1066 (42000): Not unique table/alias: 'products'

Here I tried to open a table that was already open.

Solution

To fix this issue, we must provide unique table aliases. In the first example, table aliases are actually optional (given each table is already unique in its own right), so we could alternatively swap the aliases for the actual table names. But aliases are good, and so we’ll keep them for this solution:

SELECT
    e.id,
    e.name,
    d.location
FROM employees e
JOIN departments d
    ON e.department_id = d.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. All I did was replace e with d (so that JOIN departments e became JOIN departments d).

As for the second query (the self-join query), we’ll need to give it aliases:

SELECT 
    e.employee_id,
    e.first_name AS employee_first_name,
    e.last_name AS employee_last_name,
    m.first_name AS manager_first_name,
    m.last_name AS manager_last_name
FROM 
    employees e
LEFT JOIN 
    employees m ON e.manager_id = m.employee_id
ORDER BY 
    e.employee_id;

Output:

+-------------+---------------------+--------------------+--------------------+-------------------+
| employee_id | employee_first_name | employee_last_name | manager_first_name | manager_last_name |
+-------------+---------------------+--------------------+--------------------+-------------------+
|           1 | Hank                | Blankfield         | NULL               | NULL              |
|           2 | Boz                 | Whittle            | Hank               | Blankfield        |
|           3 | Mike                | Johnson            | Hank               | Blankfield        |
|           4 | Emily               | Fields             | Boz                | Whittle           |
|           5 | David               | Pease              | Boz                | Whittle           |
|           6 | Sesh                | Lee                | Mike               | Johnson           |
|           7 | Fitch               | Davis              | Mike               | Johnson           |
+-------------+---------------------+--------------------+--------------------+-------------------+

No error.

In this query I reference the employees table twice in the FROM clause, and so I gave the first one an alias of e and the second one m. Then I used those aliases whenever I referred to columns from the tables. This fixed the error, and the query returned its result as intended.

As for the HANDLER situation, opening it once is sufficient:

HANDLER products OPEN;

Then we can continue working on the table until it’s time to close it.

To close the table:

HANDLER products CLOSE;

Once closed, we can open it again without error.