Column name is ambiguous error in Google Bigquery

When you are joining multiple tables in a SQL query and if a column with the same name is present in both the tables, Bigquery doesn't know which one to use (unless you explicitly tell so), so it throws the ambiguous column name error.

Bigquery WebUI editor is intelligent enough to highlight the exact row in which the ambiguous column is present (look for the red exclamation on the margin of the sql editor). And resolve it using one of the below methods:

Make sure the SELECT list doesn't have ambiguous column names:

select Id, Name, Description
from table1 t1 
join table2 t2
     on t2.Id = t1.Id;

In this example, The Column name Id is present in both the tables, t1 and t2. You can make it clear, by aliasing the columns, like shown below,

select t1.Id, t1.Name, t2.Description
from table1 t1 
join table2 t2
     on t2.Id = t1.Id;

Make sure the Where clause / Joins doesn't have ambiguous column names:

select t1.Id, t1.Name, t2.Description
from table1 t1 
join table2 t2
     on t2.Id = t1.Id
Where Id = 100;

In this example, Since Id is present in both the tables, alias the column, like shown below:

select t1.Id, t1.Name, t2.Description
from table1 t1 
join table2 t2
     on t2.Id = t1.Id
Where t1.Id = 100;

Never use commas in the FROM clause. Always use proper, explicit JOIN syntax:

select table1.* 
from table1,
     table2,
     table3
where table1.Id = table2.Id;

Using commas for joins can be confusing, error prone and has less readability. Always try to use explicit Join clause and the join columns. This will greatly reduce the number of errors as well as simplify your troubleshooting efforts

nVector

posted on 05 Sep 20