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.
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.
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Tables: customers, orders
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| id | name | city | id | customer_id | product | amount | order_date |
|---|---|---|---|---|---|---|---|
| 1 | Ava | Denver | 101 | 1 | Desk | 240 | 2026-01-05 |
| 2 | Ben | Austin | 102 | 2 | Lamp | 35 | 2026-01-09 |
| 1 | Ava | Denver | 103 | 1 | Chair | 120 | 2026-01-17 |
| 3 | Chloe | Denver | 104 | 3 | Monitor | 180 | 2026-02-02 |
| 2 | Ben | Austin | 105 | 2 | Desk | 240 | 2026-02-11 |
| 4 | Dan | Boston | 106 | 4 | Lamp | 35 | 2026-02-20 |
| 1 | Ava | Denver | 107 | 1 | Lamp | 35 | 2026-03-03 |
| 3 | Chloe | Denver | 108 | 3 | Chair | 120 | 2026-03-14 |
id: the first is the customer's, the fourth is the order's. Having both is fine. What the database can't do is guess which of the two you mean when you write the bare word id.In the SELECT list
The most common case: you join two tables and ask for a column by its plain name.
Tables: customers, orders
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
SQLite says: ambiguous column name: id
name and product raise nothing: each exists in only one of the two tables. id exists in both. Write orders.id for the order's number or customers.id for the customer's, and the query runs.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.
Tables: customers, orders
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
SQLite says: ambiguous column name: id
customer_id needs no prefix, because only orders has it. id does: write customers.id. Careful with the other choice. orders.id would also make the error go away, and the join would then compare each order's customer number with its own order number: no error, and not the rows you wanted.Tables: customers, orders
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
SQLite says: ambiguous column name: id
c and o, and the prefix is the alias. c.id = 1 asks for customer number 1, Ava. o.id = 1 asks for order number 1, and there isn't one: orders start at 101. The two prefixes are two different questions.Tables: customers, orders
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
SQLite says: ambiguous column name: id
o.id. The error comes from the last line, where SQLite doesn't assume that id is the one you selected. Write ORDER BY o.id DESC. Another way out is to name the column in the SELECT list, o.id AS order_id, and sort by order_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.
Tables: customers, orders
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
SQLite says: no such column: customers.name
customers was renamed to c for this query, so customers.name no longer exists. Write c.name.The same error in other databases
The words change, the cause and the fix don't.
- SQLite:
ambiguous column name: id - SQL Server: error 209,
Ambiguous column name 'id'. - MySQL: error 1052,
Column 'id' in field list is ambiguous. The middle part names the place:in on clausewhen it happens in the ON. - Oracle:
ORA-00918: column ambiguously definedin 19c and 21c. Oracle Database 26ai names the column and the two tables:column ambiguously specified - appears inone tableandthe other.
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.
For every order, return the customer's id, the customer's name and the order's id, in that order.
Tables: customers, orders
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT c.id, c.name, o.id
FROM customers c
JOIN orders o ON o.customer_id = c.id| id | name | id |
|---|---|---|
| 1 | Ava | 101 |
| 2 | Ben | 102 |
| 1 | Ava | 103 |
| 3 | Chloe | 104 |
| 2 | Ben | 105 |
| 4 | Dan | 106 |
| 1 | Ava | 107 |
| 3 | Chloe | 108 |
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.
Return the customer's name and the product of the order whose id is 104.
Tables: customers, orders
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT c.name, o.product
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.id = 104| name | product |
|---|---|
| Chloe | Monitor |
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.
Return every order's id, the customer's name and the amount, sorted by the order's id, highest first.
Tables: customers, orders
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
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| order_id | name | amount |
|---|---|---|
| 108 | Chloe | 120 |
| 107 | Ava | 35 |
| 106 | Dan | 35 |
| 105 | Ben | 240 |
| 104 | Chloe | 180 |
| 103 | Ava | 120 |
| 102 | Ben | 35 |
| 101 | Ava | 240 |
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.
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
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
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| id | name | COUNT(o.id) |
|---|---|---|
| 1 | Ava | 3 |
| 2 | Ben | 2 |
| 3 | Chloe | 2 |
| 4 | Dan | 1 |
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.