SQL Server Error 209

Error number

209

Severity

16 (user-correctable error)

State

1

Product

Microsoft SQL Server Database Engine

Versions

All supported SQL Server versions (2016 to 2025), Azure SQL Database, Azure SQL Managed Instance

Category

Syntax & Object Resolution

Last reviewed

23 October 2026

What it means

Error 209 means a column name without a prefix exists in more than one table (or output column) of the query, so SQL Server cannot tell which one you mean.

When a query joins two or more tables, SQL Server binds every unqualified column name by searching all tables in the FROM clause. If the name is found in exactly one table, it binds. If it is found in two or more, the batch fails at compile time with error 209 at severity 16 and nothing runs. The name in quotes is the column that matched more than once. The reference can be in the SELECT list, WHERE, ON, GROUP BY or ORDER BY clause, and the line number often points at the start of the statement rather than at the reference itself, so search the whole statement for the name.

The error often appears in a query that used to work: someone added a column such as city or status to a second table, and every unqualified reference to that name in a join became ambiguous. ORDER BY has its own variant: if two output columns share a name (for example c.id and o.id), ORDER BY id is ambiguous even though each select item is qualified.

Common causes

• A join where both tables have a column with the same name (id, name, status, created_at) and the query uses it without a table alias.
• A column added later to one of the joined tables, which makes an existing unqualified reference ambiguous.
• ORDER BY naming a column that appears twice in the select list, for example two columns both called id.
• An UPDATE or DELETE with a joined FROM clause whose SET or WHERE uses an unqualified column present in both tables.
• A self join (the same table twice) where any column is referenced without the alias.

Diagnose it

Read-only These queries only read metadata and dynamic management views. They do not change data, permissions or configuration, so they are safe to run first.

SELECT c.name AS column_name,
       STRING_AGG(OBJECT_NAME(c.object_id), ', ') AS found_in
FROM sys.columns AS c
WHERE c.object_id IN (OBJECT_ID(N'dbo.Customers'), OBJECT_ID(N'dbo.Orders'))
GROUP BY c.name
HAVING COUNT(*) > 1;

EXEC sp_describe_first_result_set
     N'SELECT c.id, c.name, o.total
       FROM dbo.Customers AS c
       JOIN dbo.Orders AS o ON o.customer_id = c.id
       ORDER BY c.id;';

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

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.city = N'Leeds';

SELECT c.id AS customer_id, o.id AS order_id
FROM dbo.Customers AS c
JOIN dbo.Orders AS o ON o.customer_id = c.id
ORDER BY customer_id;

UPDATE c
SET c.city = N'Leeds'
FROM dbo.Customers AS c
JOIN dbo.Orders AS o ON o.customer_id = c.id
WHERE c.city IS NULL;

Examples

An unqualified id in a two-table join

CREATE TABLE dbo.Customers (id INT PRIMARY KEY, name NVARCHAR(100));
CREATE TABLE dbo.Orders (id INT PRIMARY KEY, customer_id INT, total DECIMAL(10,2));

SELECT id, name, total  -- id exists in both tables
FROM dbo.Customers AS c
JOIN dbo.Orders AS o ON o.customer_id = c.id;

Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'id'.

A column added to the second table breaks the WHERE clause

CREATE TABLE dbo.Customers (id INT PRIMARY KEY, name NVARCHAR(100), city NVARCHAR(50));
CREATE TABLE dbo.Orders (id INT PRIMARY KEY, customer_id INT, total DECIMAL(10,2),
                         city NVARCHAR(50));  -- delivery city, added later

SELECT c.name, o.total
FROM dbo.Customers AS c
JOIN dbo.Orders AS o ON o.customer_id = c.id
WHERE city = N'Leeds';

Msg 209, Level 16, State 1, Line 5
Ambiguous column name 'city'.

ORDER BY a name that appears twice in the select list

CREATE TABLE dbo.Customers (id INT PRIMARY KEY, name NVARCHAR(100));
CREATE TABLE dbo.Orders (id INT PRIMARY KEY, customer_id INT, total DECIMAL(10,2));

SELECT c.id, o.id
FROM dbo.Customers AS c
JOIN dbo.Orders AS o ON o.customer_id = c.id
ORDER BY id;  -- two output columns are called id

Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'id'.

How to prevent it

In any query with more than one table, alias every table and prefix every column, including those in WHERE, GROUP BY and ORDER BY. Queries written that way keep compiling when a table later gains a column with a common name.

Give output columns unique, descriptive aliases (customer_id, order_id) rather than leaving several columns called id. It also makes the result easier to read in application code. See the SQL joins guide for alias conventions.

Common questions

Learn more on CodeWithSQL

• SQL joins guide
• SQL joins lesson
• Inner, left, right and full joins compared

Official documentation

• FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL)
• ORDER BY clause (Transact-SQL)
• Database engine events and errors (0 to 999)

Last technically reviewed 23 October 2026; examples and diagnostics run on SQL Server 2025 (17.0.1145.4). Message text checked against the engine's sys.messages catalogue.