SQLite: ambiguous column name

What it means

The query joins tables that share a column name, such as id, name or created_at, and uses that name on its own. SQLite won’t guess which table you mean, so preparing the statement fails with SQLITE_ERROR (code 1) and ambiguous column name: <name>, and nothing runs.

It doesn’t matter where the bare name appears: the select list, WHERE, ORDER BY, a join condition, or the SET and WHERE of an UPDATE … FROM.

Common causes

Joined tables with the same column names. customers and orders both have id, so SELECT id … from the join is ambiguous.
A self-join. Joining customers to itself gives every column twice.
ORDER BY or WHERE with a bare name, even when the select list qualifies it. SELECT c.id … ORDER BY id still fails, because c.id isn’t an alias.
UPDATE … FROM (SQLite 3.33.0 and later), where the table you update and the one in FROM share a name used without a prefix.
A column added later. A query that worked fails after ALTER TABLE orders ADD COLUMN created_at, because customers already had one.

How to fix it

Qualify the name

Put the table name, or its alias, in front of every column that more than one table has:

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

Qualifying every column in a join is a good habit: the query keeps working when someone adds a column with a shared name later.

Give output columns distinct names

When you need both, alias them:

SELECT c.id AS customer_id, o.id AS order_id, o.total
FROM customers c JOIN orders o ON o.customer_id = c.id;

An alias also makes a bare name in ORDER BY work: SELECT c.id AS id … ORDER BY id resolves to the alias. Distinct names matter beyond this error, too: SELECT * from the join above returns two columns called id, and code that reads columns by name gets only one of them.

Join with USING when the columns are the same thing

If both tables use the same name for the join column, USING merges them into one, which you can then name without a prefix:

SELECT id, name FROM customers JOIN customer_notes USING (id);

Reproduce it

macOS /usr/bin/sqlite3, SQLite 3.51.0, with customers (id, name, …) and orders (id, customer_id, total, …):

sqlite3 shop.db "SELECT id, name, total FROM customers JOIN orders ON orders.customer_id = customers.id;"

Error: in prepare, ambiguous column name: id
  SELECT id, name, total FROM customers JOIN orders ON orders.customer_id = cust
         ^--- error here

The shell adds “Error: in prepare,” and points at the name; SQLite’s message is ambiguous column name: id. The other cases:

Error: in prepare, ambiguous column name: id
Error: in prepare, ambiguous column name: name
Error: in prepare, ambiguous column name: id
Error: in prepare, ambiguous column name: created_at

Those were SELECT c.id, name … ORDER BY id, a self-join selecting name, an UPDATE orders … FROM customers … AND id = 1, and SELECT name, created_at after orders gained a created_at column. With c.id AS id, the ORDER BY id query ran; so did SELECT id FROM customers JOIN orders USING (id). Through Python’s sqlite3 module (SQLite 3.53.4), the error was sqlite3.OperationalError: ambiguous column name: id.

In Inlet

When SQLite rejects the name, Inlet shows the error at the position SQLite reports, so you can see which reference to qualify. The query editor completes table and column names from the live schema.

ambiguous column name