Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A JOIN query fails with 'Column ... is ambiguous'

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

Short answer

Error 1052 means a column name exists in more than one table of the query and MySQL cannot tell which you mean. Put the table name or alias in front of the column (orders.user_id), or, when both tables share the join column name, join with USING so the column is merged.

  • Applies to MySQL 8.4, MySQL 8.0
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Read the message: it names the column and where it sits (field list, on clause). Then list which of your joined tables own a column with that name.

    SELECT table_name
    FROM information_schema.columns
    WHERE table_schema = DATABASE() AND column_name = 'user_id';
  2. 2Match what you see, then jump to its fix

    1. 1Column 'user_id' in field list is ambiguousAn unqualified column in the select listFix
    2. 2Column 'i' in on clause is ambiguousA self-join or ON clause that compares a column with itselfFix
    3. 3The join column is the same name in both tables and you wrote it bare in the queryBoth tables use the same name for the join columnFix

What does the error look like?

  • ERROR 1052 (23000): Column 'user_id' in field list is ambiguous
  • ERROR 1052 (23000): Column 'i' in on clause is ambiguous

Cause 1 of 3

An unqualified column in the select list

1Check

Two joined tables both have the column and the SELECT names it bare. The MySQL error reference shows the same case: SELECT i FROM t INNER JOIN t AS t2.

SELECT user_id FROM users u JOIN orders o ON o.user_id = u.id;

2Fix

Qualify the column with the table name or its alias, and pick the table whose value you want.

SELECT o.user_id, o.total
FROM users u
JOIN orders o ON o.user_id = u.id;

3Verify

Run the query again; the same statement now returns rows.

SELECT o.user_id FROM users u JOIN orders o ON o.user_id = u.id LIMIT 5;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

A self-join or ON clause that compares a column with itself

1Check

The same table appears twice, or both sides of the ON comparison use the same bare name, so MySQL cannot tell the two copies apart. The error reference gives SELECT * FROM t LEFT JOIN t AS t2 ON i = i as an example.

SELECT * FROM employees e JOIN employees m ON manager_id = id;

2Fix

Give each copy its own alias and qualify every column in the ON clause.

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

3Verify

Run it and check that employee and manager columns come back as expected.

SELECT COUNT(*) FROM employees e JOIN employees m ON e.manager_id = m.id;

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

Both tables use the same name for the join column

1Check

When the two join columns share one name, USING merges them into one column, so the bare name stops being ambiguous. ON keeps both columns in SELECT *.

SELECT * FROM orders JOIN order_items USING (order_id);

2Fix

Use USING (column) for the join and refer to the shared column without a prefix. Keep ON when the names differ or the condition is not a plain equality.

SELECT order_id, total, sku
FROM orders
JOIN order_items USING (order_id);

3Verify

Confirm that SELECT * now shows order_id once.

SELECT * FROM orders JOIN order_items USING (order_id) LIMIT 1;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Give every table in a multi-table query a short alias and prefix every column with it, even the ones that are not ambiguous today.
  • Name join columns consistently across tables so USING is possible.
  • When a query grows a new JOIN, re-read the select list: a column that was unique before may now be shared.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.