Ambiguous Column Name SQL Error (Fix + Explanation)

Why this happens

Two tables, both with an id column — completely normal in a relational schema:

SELECT id, order_date
FROM orders
JOIN customers ON customers.customer_id = orders.customer_id;
-- Error: column 'id' in field list is ambiguous

Both orders and customers have their own id column. The bare word id in the SELECT list could mean either one, and SQL has no rule for guessing which — so it raises an error rather than silently picking one and possibly returning the wrong data.

The fix: qualify every reference

Prefix the column with the table name (or, more commonly, a short alias) everywhere it's ambiguous:

SELECT o.id, o.order_date
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

Aliasing both tables (orders o, customers c) and using those aliases consistently is the standard convention — it's shorter to type than repeating the full table name and makes the query easier to scan.

It's not just SELECT

The single most common follow-up mistake: fixing the SELECT list and assuming the query is done, when the same bare column name is still ambiguous somewhere else in the statement.

SELECT o.id, o.order_date
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
ORDER BY id;   -- still ambiguous — which id?

SELECT o.id, o.order_date
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
ORDER BY o.id;   -- fixed

WHERE, GROUP BY, HAVING, and ORDER BY can each independently trigger this same error — every one of them needs the qualified column name if the column name exists in more than one joined table.

Does USING fix it?

JOIN ... USING (column) is a shorthand for joining on a column that exists with the same name in both tables, and it resolves ambiguity for that specific column by merging it into a single unqualified reference in the output:

SELECT customer_id, order_date  -- customer_id is fine — USING merged it
FROM orders
JOIN customers USING (customer_id);

But this only helps for the join column named in USING. If both tables also happen to share a different column name — say, both have created_at — that second column is still ambiguous and still needs to be qualified explicitly.

What about SELECT *?

SELECT * across a join where both tables share a column name usually doesn't raise this specific error — it just returns duplicate column names in the result set, which is a quieter, easier-to-miss problem: most client libraries and ORMs will silently keep only one of the two same-named columns (typically the last one), discarding the other without any warning.

Common mistakes

• Fixing only the SELECT list and missing the same bare column name in WHERE, ORDER BY, or GROUP BY.
• Assuming USING resolves every shared column name, when it only merges the one column explicitly named in the clause.
• Using SELECT * across joined tables with overlapping column names and not noticing a column silently disappeared from the result.
• Giving two tables in the same query the same alias by accident, which reintroduces the exact same ambiguity the alias was meant to resolve.