SQL "column reference is ambiguous" (tested)

column reference "id" is ambiguous (PostgreSQL) or a similar "ambiguous column name" error (MySQL, SQL Server) means two or more tables in your query have a column with the same name, and you referred to it without saying which table you meant. The database can't guess, so it refuses to run the query at all — even if the two columns happen to hold the same value.

1. Reading the error

The message names the column, not the table: column reference "id" is ambiguous. It tells you which name collided but not which tables are colliding — you have to look at the query to see that. This only happens when a query touches more than one table (a JOIN, or more than one table listed after FROM) and at least two of them have a column with the identical name.

2. Four reproduced cases (PostgreSQL 16.15)

Both orders and users have a column named id in this schema (every table has its own primary key called id). Postgres raises the error as soon as the unqualified name id appears anywhere — SELECT list, WHERE, or GROUP BY — it doesn't matter which clause.

3. The fix: qualify the column with a table name or alias

Prefix the column with the table (or a short alias) everywhere it's used:

-- fails: ambiguous
SELECT id, name FROM users JOIN orders ON users.id = orders.user_id WHERE id = 1;

-- works: every "id" is qualified
SELECT u.id, u.name, o.amount
FROM users u JOIN orders o ON u.id = o.user_id
WHERE u.id = 1;

Real output after qualifying:

psql> SELECT u.id, u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id WHERE u.id = 1;
 id | name  | amount
----+-------+--------
  1 | Alice |     50
(1 row)

Same fix for GROUP BY:

psql> SELECT u.id, count(*) FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.id;
 id | count
----+-------
  2 |     1
  1 |     1
(2 rows)

4. A second fix for natural joins: USING

If the shared column has the exact same name and meaning in both tables (a true natural join key, not two unrelated id columns), USING lets you reference it unqualified afterwards — Postgres merges the two columns into one output column instead of keeping them ambiguous:

psql> SELECT id, val, note FROM a_tbl JOIN b_tbl USING (id);
 id | val | note
----+-----+------
  1 | x   | y
(1 row)

USING (id) only works when both sides are actually named id and you want them merged. It will not resolve our users/orders example above, because there the matching columns are named differently (users.id vs orders.user_id) — USING requires identical names on both sides, so that case needs the alias fix in section 3 instead.

5. Why SELECT * doesn't always catch this

SELECT * across a join with duplicate column names doesn't error — it just returns both columns, often with no way to tell them apart in the result set. The ambiguity error only fires when you write the bare column name somewhere Postgres has to resolve it (a specific column in the SELECT list, a WHERE, a GROUP BY, an ORDER BY). That's why this error tends to show up only after someone narrows a SELECT * query down to specific columns.

6. Other databases

Per the MySQL and SQL Server docs (not measured on this box): MySQL raises ERROR 1052 (23000): Column 'id' in field list is ambiguous; SQL Server raises Msg 209: Ambiguous column name 'id'. The cause and fix are identical — qualify the column with its table name or alias.

7. Checklist

• Alias every table in a multi-table query (FROM users u JOIN orders o ON ...), even if you think you won't need it.
• Qualify every column that could exist in more than one of the joined tables — not just the one the error names, since fixing one occurrence often reveals the next.
• Use USING (col) only when the join key is genuinely the same column in both tables and you want Postgres to merge it into one output column.
• Don't rely on SELECT * to catch this during development — it hides the collision instead of erroring.

Written with AI assistance. Measured on 2026-10-03 with PostgreSQL 16.15 (official Docker image), using psql. Every error message below is copy-pasted output, not paraphrased. MySQL and SQL Server use the same join/alias rule but phrase the error differently (per their docs, not measured here). PlainHeaders is our extension; PostgreSQL and Docker are free.