Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.
A SQL "ambiguous column name" error means a JOIN produced same-named columns and your query didn't say which one it meant. Here's the fix, dialect by dialect.
Quick answer
The "ambiguous column name" error (Postgres: column reference "id" is ambiguous; MySQL: Column 'id' in field list is ambiguous; SQL Server: Msg 209) fires when a query joins two or more tables that share a column name and references that column without saying which table it comes from. Fix it by qualifying the column with its table name or alias, such as orders.id instead of id, or by using a USING(column) clause when the join key is named identically on both sides (not supported in SQL Server). It is a name-resolution error caught before the query runs, not a data problem.
FAQs
Does SELECT * cause an ambiguous column name error?
Not by itself — SELECT * just expands to every column from every joined table, duplicates included, and most engines return both copies rather than erroring. The error shows up one step later, when you or a downstream query reference one of those duplicated column names by hand. Expand columns explicitly in joined production queries so duplicates never reach the result set in the first place.
Can an ambiguous column error happen in a single-table query?
Yes, in two situations: a CTE or subquery whose output columns collide with an outer query's table, or a PL/pgSQL function or stored procedure where a variable or parameter shares a name with a column. Both fix the same way as a join — qualify the reference or rename the colliding variable so only one name remains.
Why does the error point at a column instead of the join that caused it?
The engine resolves each column reference independently while binding the query, and it reports the ambiguity at the first unqualified reference it finds — typically in the SELECT list — rather than at the JOIN clause that introduced the second copy of the column. The join is the root cause; the column is just where the resolver gave up.
Does the USING clause work the same way in every SQL dialect?
PostgreSQL, MySQL, SQLite, and BigQuery all support USING(column) and collapse the joined column into a single, unambiguous output column. SQL Server's T-SQL has no USING clause at all, so an explicit ON with alias-qualified columns is the only option there.
Is 'ambiguous column name' a syntax error?
Not technically — most engines catch it during query binding and name resolution, a step that runs after parsing confirms the SQL is well-formed but before any rows are read. That's why the message names a specific column rather than a token position the way a true syntax error, like PostgreSQL's 'syntax error at or near', does.
Try it now
Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.
The SQL "ambiguous column name" error means your query joined two or more tables that share a column name, and you referenced that column without saying which table it comes from. Postgres calls it column reference "id" is ambiguous, MySQL says Column 'id' in field list is ambiguous, and SQL Server throws Msg 209, Ambiguous column name 'id'. Different wording, identical cause, identical fix: qualify the column with a table name or alias.
This error is one of the most common ones developers hit the moment a query grows past a single table, because the columns most likely to collide — id, name, status, created_at — are exactly the ones every table tends to have. It's a different failure mode than Postgres's column must appear in the GROUP BY clause error, which is about aggregation rules rather than name resolution, but the two get confused often enough that it's worth knowing which is which.
This post covers exactly what causes the error, how to read and fix it in PostgreSQL, MySQL, SQL Server, SQLite, and BigQuery, why USING and NATURAL JOIN change the picture, and a short checklist to stop it from happening in the first place.
What actually causes an "ambiguous column name" error?
Every SQL engine resolves each unqualified column name — one written as id instead of orders.id — by checking it against every table and alias currently in scope: everything listed in FROM and every JOIN. If exactly one table in scope has that column, the engine uses it. If more than one does, the engine can't guess which one you meant, so it refuses to run the query at all.
That refusal happens during query binding — after the parser has confirmed the SQL is syntactically valid, but before a single row is read. That's why the message names a specific column rather than pointing at a stray comma or keyword the way a true syntax error does: the query is well-formed, just under-specified.
The ambiguity can surface in the SELECT list, a WHERE condition, a GROUP BY, or an ORDER BY — anywhere a bare column name appears once two joined tables share it. Here's the baseline case that every dialect agrees on:
Before: unqualified column across a join
SELECT id, name, totalFROM ordersJOIN customers ON orders.customer_id = customers.id;-- fails: both "orders" and "customers" have an "id" column
After: qualify the column and give it a clear alias
SELECT orders.id AS order_id, customers.name, orders.totalFROM ordersJOIN customers ON orders.customer_id = customers.id;
How do I fix "column reference ... is ambiguous" in PostgreSQL?
PostgreSQL's version reads like ERROR: column reference "id" is ambiguous, usually followed by a LINE pointer at the offending reference. The fix is always to qualify — either the table name directly or, more commonly, a short alias assigned in the FROM/JOIN clause.
PL/pgSQL functions add a second, less obvious version of the same problem: a variable or parameter name that collides with a column name in a query the function runs. Postgres resolves that collision using its #variable_conflict setting, but the simplest fix is just to avoid the collision — prefix parameters (p_customer_id) or qualify the column with the table name so there's nothing left to guess. See PostgreSQL's documentation on joined tables and the USING clause for the full name-resolution rules.
How do I fix "Column ... in field list is ambiguous" in MySQL?
MySQL raises ERROR 1052 (23000): Column 'id' in field list is ambiguous for the same underlying reason — the column exists on more than one side of the join. MySQL's error text is a little more specific than Postgres's or SQL Server's in that it names the exact clause ("field list") where the collision was found, but the fix doesn't change: alias every joined table and qualify every column that could plausibly exist twice.
It's worth aliasing consistently even for columns that aren't currently ambiguous. Someone adds a status column to a second table six months later, and an unqualified status reference that worked fine yesterday breaks today with no code change on your end. See MySQL's JOIN clause reference for the full join syntax, including which forms support USING.
How do I fix "Ambiguous column name" (Error 209) in SQL Server?
SQL Server's version is Msg 209, Level 16, State 1: Ambiguous column name 'id'. Same cause, same fix — but T-SQL has one extra wrinkle worth knowing: it has no USING clause at all, so there's no shortcut that collapses a shared join-key column into one output field the way there is in the other four dialects. Every ambiguous column, in every clause, needs an explicit alias.
That includes ORDER BY, which is easy to forget: you fix the SELECT list and the query still fails because the sort column at the bottom is still bare. Qualify it in every clause, not just the first one the error message happens to point at. See Microsoft's FROM clause reference for alias syntax and scoping rules.
Does the same error show up in SQLite and BigQuery?
Yes — SQLite reports ambiguous column name: id, and BigQuery's standard SQL reports Column name id is ambiguous. Both resolve unqualified names against every table in scope exactly like the three dialects above, and both fix the same way: alias each table, qualify each column that exists on more than one side of the join.
BigQuery's syntax adds backtick-quoted, project-qualified table names, which makes an unaliased join especially unreadable — aliasing isn't just the fix for the error, it's what keeps the rest of the query legible. See BigQuery's join types reference for the supported join forms.
Does USING or NATURAL JOIN avoid the problem?
When the join key has the exact same name on both sides — customer_id on both orders and customers, say — PostgreSQL, MySQL, SQLite, and BigQuery all support a USING (customer_id) clause that collapses the two columns into a single unambiguous output column. You get to reference customer_id bare afterward with no error, because there's only one of it in the result. SQL Server has no equivalent — ON with alias-qualified columns is the only option there.
NATURAL JOIN goes further and joins automatically on every column name the two tables share — no ON or USING needed. It genuinely eliminates ambiguous-column errors on the join keys, but it does so by hiding which columns drive the join inside the schema instead of the query text. Add an unrelated same-named column to either table later — a created_at on both, for instance — and the join silently changes behavior with no edit to the SQL at all. Prefer explicit ON or USING in anything beyond a one-off query, and see the notes on formatting JOINs and their ON conditions consistently for how to keep multi-join queries readable as they grow.
Checklist: catch ambiguous column errors before you hit run
Alias every table the moment a query has more than one — orders o, not just orders — even if nothing is ambiguous yet.
Qualify every column in SELECT, WHERE, GROUP BY, and ORDER BY once you have two or more tables in scope — the error can hide in any of them, not just the SELECT list.
Avoid SELECT * in joined production queries. It won't throw an ambiguous-column error itself, but it silently ships duplicate column names downstream, and the next query built on top of that result set will hit the error instead.
Use USING (col) when the join key is named identically on both sides — PostgreSQL, MySQL, SQLite, and BigQuery all collapse it to one unambiguous column for free.
Avoid NATURAL JOIN outside of throwaway queries — it trades ambiguous-column errors today for silent join-key changes later.
In PL/pgSQL or stored procedures, watch for a parameter or local variable name that collides with a column name; rename the parameter rather than relying on conflict-resolution settings.
Adopt one aliasing convention (first letter of the table, or a short abbreviation) and apply it consistently — that consistency is exactly what a formatter like sqlfmt normalizes and what makes team SQL style guides actually stick during review.
Wrapping up
An "ambiguous column name" error is never about your data — it's the engine telling you it found the same column name on both sides of a join and refusing to guess which one you meant. Qualify the column, alias the table, and the error disappears in every dialect from Postgres to BigQuery.
If you're moving queries between engines, it helps to know which behaviors are shared and which aren't — see the syntax differences between MySQL and PostgreSQL for more of these small-but-sharp-edged gaps. And if you want a fast way to check a query for syntax errors, typos, and style issues before you run it, sqlfmt formats and validates SQL across all five dialects covered here right in your browser — the free tier covers a quick one-off check, and the Pro plan adds live formatting as you type.
-- ERROR: column reference "id" is ambiguous-- LINE 1: SELECT id, name, totalSELECT id, name, totalFROM ordersJOIN customers ON orders.customer_id = customers.id;-- FixedSELECT o.id AS order_id, c.name, o.totalFROM orders oJOIN customers c ON o.customer_id = c.id;
MySQL: alias every table, qualify every column
-- ERROR 1052 (23000): Column 'id' in field list is ambiguousSELECT id, name, totalFROM ordersINNER JOIN customers ON orders.customer_id = customers.id;-- FixedSELECT o.id AS order_id, c.name, o.totalFROM orders oINNER JOIN customers c ON o.customer_id = c.id;
SQL Server: qualify in SELECT and ORDER BY alike
-- Msg 209, Level 16, State 1: Ambiguous column name 'id'.SELECT id, name, totalFROM ordersJOIN customers ON orders.customer_id = customers.idORDER BY id;-- FixedSELECT o.id AS order_id, c.name, o.totalFROM orders oJOIN customers c ON o.customer_id = c.idORDER BY o.id;
SQLite: same fix as everywhere else
-- Error: ambiguous column name: idSELECT id, name, totalFROM ordersJOIN customers ON orders.customer_id = customers.id;-- FixedSELECT orders.id AS order_id, customers.name, orders.totalFROM ordersJOIN customers ON orders.customer_id = customers.id;
BigQuery: alias the fully qualified table names
-- Error: Column name id is ambiguousSELECT id, name, totalFROM `project.dataset.orders`JOIN `project.dataset.customers` ON orders.customer_id = customers.id;-- FixedSELECT o.id AS order_id, c.name, o.totalFROM `project.dataset.orders` AS oJOIN `project.dataset.customers` AS c ON o.customer_id = c.id;
PostgreSQL/MySQL/SQLite/BigQuery: USING collapses the shared column
SELECT customer_id, name, totalFROM ordersJOIN customers USING (customer_id);-- customer_id appears once in the output, so it's no longer ambiguous