Docs/Error messages/Column name is ambiguous
Error messagesNames

Column name X is ambiguous

Two tables in the FROM clause, or two columns with one alias, have the same name, and BigQuery will not choose between them. Put the table name or alias in front of the column, join with USING, or give the columns different names.

Practice this in free lessons on learn.querylab.io: INNER JOIN and 1 more.

Column name customer_id is ambiguous at [3:8]

What BigQuery means

A name in your query matches more than one column, and BigQuery will not pick one. The usual cause is a join: both tables have a column called customer_id, and a bare customer_id could be either. It is not a problem for BigQuery to have two such columns. It only refuses to guess when you refer to the name without saying which one.

The position, at [3:8], is the line and column of the name that BigQuery could not resolve. It is an error from the compile step, so a dry run reports it before anything is read or billed.

The smallest query that reproduces it

WITH orders AS (SELECT 1 AS order_id, 10 AS customer_id),
customers AS (SELECT 10 AS customer_id, 'Ann' AS name)
SELECT customer_id, name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
Column name customer_id is ambiguous at [3:8]

The ON clause is fine, because it names the table in front of each column. The customer_id in the SELECT list is the one without a table.

The fixes

Put the table or the alias in front of the name.

SELECT orders.customer_id, name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id

The name column needs nothing in front, because only customers has it. With aliases the same query reads:

SELECT o.customer_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id

Join with USING. When the two columns are the join column, USING merges them into one column, and the name stops being ambiguous:

SELECT customer_id, name
FROM orders
JOIN customers USING (customer_id)

This returns 10, Ann. A bare customer_id also works in WHERE after a USING join. USING fits when the join column has the same name on both sides. When it does not, qualify the name.

Give the columns different names. If you give two select-list items the same alias, a later reference to that alias is ambiguous. Use two aliases.

The variants of the message

The name can be ambiguous in any clause that reads it. These were all produced by BigQuery, with a join between aliased tables o and c:

Column name customer_id is ambiguous at [6:7]

That is WHERE customer_id = 10. ORDER BY customer_id and GROUP BY customer_id give the same message, at [6:10]. PARTITION BY customer_id inside a QUALIFY gives it at the position of the name.

Two select-list items with one alias. The select list itself runs, because BigQuery renames the second id to id_1. Reading the alias later is what fails:

SELECT o.customer_id AS id, c.customer_id AS id
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
ORDER BY id
Column name id is ambiguous at [6:10]

GROUP BY id for the same select list gives a longer message:

Name id in GROUP BY clause is ambiguous; it may refer to multiple columns in the SELECT-list at [6:10]

Give each item its own alias, such as order_customer and customer_customer, and ORDER BY order_customer runs.

A subquery with two columns of one name. The inner query runs, and the outer query cannot choose:

SELECT *
FROM (SELECT o.customer_id, c.customer_id FROM orders o JOIN customers c ON o.customer_id = c.customer_id)
WHERE customer_id = 10
Column name customer_id is ambiguous at [5:7]

Alias the columns inside the subquery.

The same table twice. With FROM orders a, orders b, a bare customer_id matches a column of each. SELECT customer_id FROM orders a, orders b gives Column name customer_id is ambiguous at [2:8]. Write a.customer_id.

Queries that look wrong but run

A SELECT * over a join. It returns both customer_id columns, as customer_id and customer_id_1. The error only appears when you name the column:

SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id

If you want one copy of the column, SELECT * EXCEPT (customer_id), o.customer_id returns order_id, name and customer_id. EXCEPT (customer_id) removes both copies, and the last item adds one back.

A column that only one table has. name above needs no table. Adding a column of the same name to the other table later makes the old query fail, because name is then ambiguous. Qualifying columns in a join avoids that.

Different errors that look like this one

Unrecognized name: customer_id is the opposite problem: no table in the FROM has the column. See Unrecognized name.

In Querylab.io

Querylab.io marks an ambiguous column as you type, before BigQuery is asked. The marker is a warning on the column, with the text Column 'customer_id' is ambiguous - found in tables: orders, customers. It appears for the select list, WHERE, GROUP BY and ORDER BY, and it offers a fix for each table in the FROM, such as Qualify with 'orders.customer_id'. A USING join is not marked.

The editor does not mark the aliased case, two select items with one alias, as ambiguous. It marks the second alias as a duplicate. BigQuery’s own error comes back from the dry run or a failed run.

After a failed run or a dry run, the status bar offers the same Qualify with buttons when the tables in the FROM have aliases, as in FROM orders o JOIN customers c. For tables without an alias, such as FROM orders JOIN customers, the status bar has no button. The lightbulb on the editor’s warning has the fixes.

There is no button when the name is an alias that two select items are written AS, and it is read in GROUP BY, HAVING, QUALIFY or ORDER BY, because o.id is not a column of o (BigQuery says Name id not found inside o). Two columns that only share a name, such as o.customer_id and c.customer_id in the select list, are not aliases: ORDER BY o.customer_id resolves, so the buttons stay. There is none for a subquery in FROM that has the name twice, because s.customer_id is as ambiguous as customer_id. The other sources of the FROM still get theirs.