Ambiguous column name (SQL Server error 209)

What it means

Your query joins tables that each have a column of the same name (id, name, created_at, status), and somewhere you wrote that name without a table in front of it. SQL Server won’t guess which one you meant, so it rejects the whole batch before running any of it.

Msg 209, Level 16, State 1, Server 7732422b7f56, Line 1
Ambiguous column name 'id'.

The message names the column but not where it appears: look in SELECT, WHERE, GROUP BY, HAVING and ORDER BY for every unqualified use of it, including columns you only sort or filter by.

Common causes

1. A join where both tables have id, and the query says SELECT id or WHERE id = 1.
2. A column you didn’t select, such as ORDER BY created_at when both tables have created_at.
3. Two selected columns with the same name (SELECT c.id, o.id … ORDER BY id). ORDER BY looks at the select list first and finds two columns called id.
4. A query that worked until someone added a column. SELECT name FROM orders JOIN customers … breaks the day orders gets its own name column, so qualify columns in code you keep.

A close relative: when two selected columns share a name and the query becomes a derived table, CTE, view or SELECT … INTO, the result needs unique column names. That fails with a different number, listed under Reproduce it.

How to fix it

Qualify the column with its table’s alias

Give every table an alias and put it in front of every column. It costs a few characters and makes the query safe against new columns later:

SELECT c.id, c.name, o.total
FROM dbo.customers AS c
JOIN dbo.orders AS o ON o.customer_id = c.id
WHERE c.id = 1
ORDER BY o.created_at;

Use the alias, not the table’s full name: after FROM dbo.customers AS c, writing customers.id is error 4104.

Give same-named output columns their own names

When you need both id columns, alias them. That also lets ORDER BY use the output names:

SELECT c.id AS customer_id, o.id AS order_id, c.name
FROM dbo.customers AS c
JOIN dbo.orders AS o ON o.customer_id = c.id
ORDER BY order_id;

It’s also what a view, CTE, derived table or SELECT … INTO over that query needs, since each of them requires unique column names.

Don’t rely on SELECT *

SELECT c.*, o.* returns both id columns, and any ORDER BY id on it is ambiguous. List the columns you need, qualified.

Reproduce it

On SQL Server 2022 (16.0.4295.3) with sqlcmd, customers and orders tables that both have id and created_at, each statement in its own batch:

SELECT id, name, total FROM seo_err_mssql.customers AS c
  JOIN seo_err_mssql.orders AS o ON o.customer_id = c.id;
SELECT c.name, o.total FROM seo_err_mssql.customers AS c
  JOIN seo_err_mssql.orders AS o ON o.customer_id = c.id ORDER BY created_at;

Msg 209, Level 16, State 1, Server 7732422b7f56, Line 1
Ambiguous column name 'id'.
Msg 209, Level 16, State 1, Server 7732422b7f56, Line 1
Ambiguous column name 'created_at'.

WHERE id = 1, GROUP BY id, SELECT c.id, o.id … ORDER BY id and SELECT c.*, o.* … ORDER BY id all gave the same 209 for 'id'. SELECT c.id, o.id AS id2 … ORDER BY id worked, because the ORDER BY then found one id in the select list. Selecting both id columns into a derived table, a CTE, a temp table and a view:

Msg 8156, Level 16, State 1, Server 7732422b7f56, Line 1
The column 'id' was specified multiple times for 't'.
Msg 8156, Level 16, State 1, Server 7732422b7f56, Line 1
The column 'id' was specified multiple times for 't'.
Msg 2705, Level 16, State 3, Server 7732422b7f56, Line 1
Column names in each table must be unique. Column name 'id' in table '#t' is specified more than once.
Msg 4506, Level 16, State 1, Server 7732422b7f56, Procedure v, Line 1
Column names in each view or function must be unique. Column name 'id' in view or function 'v' is specified more than once.

With c.id AS customer_id, o.id AS order_id and ORDER BY o.created_at, o.id, the query returned all three orders. SQL Server 2019 and 2025 printed the same messages.

In Inlet

The query editor completes table and column names from the live schema. When SQL Server rejects the query, Inlet shows its message with SQL Server error 209, state 1, severity 16 and links to this page. With your own Anthropic API key, Ask Claude (Pro, ⌘L) can fix a failed statement; it sends the schema and the SQL, never rows.