SQL practice › SQL errors › Ambiguous column name

SQL error: ambiguous column name, and how to fix it

"Ambiguous column name" means the query reads two tables that both have a column with that name, and the query uses the name without saying which table it belongs to. The fix is a prefix: write orders.id or customers.id instead of id, or give the tables short aliases and write o.id.

The error can come from any part of the query, not only the SELECT list: ON, WHERE, GROUP BY and ORDER BY raise it too. And the prefix you choose changes the answer, so it is worth a second of thought. Each case runs below, broken: fix it in the box.

Skip to the 4 exercises ↓

The two tables

Five customers and eight orders. Both tables have a column called id: a customer's id (1 to 5) and an order's id (101 to 108). An order points at its customer through customer_id.

customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Live queryA join has both id columns
Tables: customers, orders
customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Result · 8 rows
idnamecityidcustomer_idproductamountorder_date
1AvaDenver1011Desk2402026-01-05
2BenAustin1022Lamp352026-01-09
1AvaDenver1031Chair1202026-01-17
3ChloeDenver1043Monitor1802026-02-02
2BenAustin1052Desk2402026-02-11
4DanBoston1064Lamp352026-02-20
1AvaDenver1071Lamp352026-03-03
3ChloeDenver1083Chair1202026-03-14

In the SELECT list

The most common case: you join two tables and ask for a column by its plain name.

Live querySELECT id: which id?
Tables: customers, orders
customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run

SQLite says: ambiguous column name: id

In ON, WHERE, GROUP BY and ORDER BY

The rule is the same everywhere in the query. A name that exists in two of the tables needs its prefix every time it is used.

Live queryIn the ON condition
Tables: customers, orders
customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run

SQLite says: ambiguous column name: id

Live queryIn WHERE
Tables: customers, orders
customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run

SQLite says: ambiguous column name: id

Live queryIn ORDER BY, even when you selected only one id
Tables: customers, orders
customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run

SQLite says: ambiguous column name: id

Table name or alias, but not both

A prefix can be the table's full name (customers.name) or an alias you declare after the table (FROM customers c, then c.name). Aliases keep long queries readable, and in a self join, where the same table appears twice, they are the only way to tell the two copies apart.

One thing catches people right after they fix the first error: once a table has an alias, the alias is its only name in that query.

Live queryThe next error: the table has an alias now
Tables: customers, orders
customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run

SQLite says: no such column: customers.name

The same error in other databases

The words change, the cause and the fix don't.

A habit that avoids the error for good: in any query that reads more than one table, prefix every column, including the ones that are unique today. The query then keeps working when someone adds a name or a created_at column to the other table.

Practice: 4 exercises

Write the query in the box and press Run. Your result is compared with the right one, on these tables and again on changed data, so a query with the values typed in won't pass. Each of these needs at least one prefixed column to run at all.

Exercise 1Two ids in one result

For every order, return the customer's id, the customer's name and the order's id, in that order.

Tables: customers, orders
customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Show the answer
SELECT c.id, c.name, o.id
FROM customers c
JOIN orders o ON o.customer_id = c.id
Result · 8 rows
idnameid
1Ava101
2Ben102
1Ava103
3Chloe104
2Ben105
4Dan106
1Ava107
3Chloe108

Both tables have an id, so each one is written with its table's alias: c.id is the customer's, o.id the order's. o.customer_id in place of c.id gives the same numbers, and it needs no prefix at all, since only orders has that column.

Exercise 2One order, by its id

Return the customer's name and the product of the order whose id is 104.

Tables: customers, orders
customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Show the answer
SELECT c.name, o.product
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.id = 104
Result · 1 row
nameproduct
ChloeMonitor

104 is an order's id, so the condition is on o.id. With c.id = 104 the query would run and return nothing, because no customer has that id: picking the wrong prefix doesn't raise an error, it changes the result.

Exercise 3Newest order first

Return every order's id, the customer's name and the amount, sorted by the order's id, highest first.

Tables: customers, orders
customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Show the answer
SELECT o.id AS order_id, c.name, o.amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY order_id DESC
Result · 8 rows
order_idnameamount
108Chloe120
107Ava35
106Dan35
105Ben240
104Chloe180
103Ava120
102Ben35
101Ava240

ORDER BY id is ambiguous even though the SELECT list has only one id. Two fixes work: sort by the prefixed column, ORDER BY o.id DESC, or give the column a name of its own in the SELECT list and sort by that name, as here.

Exercise 4Orders per customer

For each customer who has placed at least one order, return the customer's id, their name and how many orders they have placed.

Tables: customers, orders
customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Show the answer
SELECT c.id, c.name, COUNT(o.id)
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
Result · 4 rows
idnameCOUNT(o.id)
1Ava3
2Ben2
3Chloe2
4Dan1

The prefix is needed three times here: in the SELECT list, inside COUNT and in GROUP BY. Grouping by c.id makes one row per customer; grouping by o.id would make one row per order, each with a count of 1.

Questions people ask

What does "ambiguous column name" mean in SQL?

The name you wrote exists in more than one of the tables the query reads, and nothing in the query says which table you mean. The database refuses to guess. Prefix the column with its table's name or alias: orders.id, or o.id after FROM orders o.

Why do I get it for id in a join?

Because almost every table has a column called id, so a join of two tables has two of them. The join condition is where it usually shows first: ON customer_id = id has to be ON orders.customer_id = customers.id.

Why does ORDER BY raise it when my SELECT list is unambiguous?

On SQLite, SELECT o.id … ORDER BY id fails, as the live query above shows: the id on the last line could be either table's, and SQLite doesn't take the SELECT list as a hint. Sort by o.id, or give the column an alias in the SELECT list and sort by the alias.

Should I use the table name or an alias?

Either works. An alias is shorter and is required when the same table appears twice, as in a self join. Once you declare one, use it everywhere in that query: the table's original name stops working, on SQLite and, by its manual, on PostgreSQL too.

Does JOIN … USING avoid the error?

For the join column, yes, when both tables call it the same. PostgreSQL's manual explains that JOIN … USING (customer_id) produces a single output column for the pair, and MySQL's error reference lists USING as a way to avoid the need for a prefix. The tables on this page can't use it: the column is id in one and customer_id in the other.

Keep going

Lesson 3 is seven JOIN drills on a plant shop's customers and orders, each with two tables to keep apart: writing the ON, the wrong-key trap, LEFT JOIN with counts, and counting after a join. It's free, like lessons 1, 2 and 4 and the level test.

Related

SQL JOIN: INNER and LEFT, with live queriesSQL self join: employees and their managersSQL JOIN practice exercisesSQL skills testSQL errors, with a query to fixSQL practice online

Written and tested by Nico, who builds SheetDrill (about). Every query on this page was run on SQLite, and every exercise checked against right and wrong answers, before the page was published. Other databases differ in places; where they do, the page says so. Checked against: PostgreSQL documentation: Table Expressions (aliases, JOIN USING), Oracle Database error messages: ORA-00918, SQL Server documentation: Database Engine events and errors (0 to 999), MySQL: Server Error Message Reference (error 1052). Last checked November 2026.