What causes this error?
The best place to start is to understand what causes a problem. The error is mainly caused when a column with the same name appears in more than one table when performing a join.
To illustrate how this error occurs, assume we have two tables as shown in the queries below:
id int auto increment primary key,
full_name varchar(255),
lang varchar(100)
);
create table my_table_1(
id int auto increment,
full_name varchar(255),
salary money
);
Here we have two tables: My_table and my_table_1. Note that both tables have a full_name column. Ignore the id column as this is an auto-increment column.
If we attempt to run a join in the two tables:
The above query will return an “ambiguous column name” error. Although the error message may vary depending on the database engine, the output is closely similar.
The following shows the same error printed on the PostgreSQL database engine.
Doing so leads to SQL being confused with which full_name you are referring.
SQL Solve Ambiguous column name error
To solve this error, we can add aliases for the columns causing ambiguity. For example, we can run the query as:
Here, we add aliases for the columns with x representing my_table and y representing my_table_1.
The above should resolve the error and allow you to perform the join without changing the column names.
Closing
This tutorial covered how to solve the “ambiguous column name” error in SQL.
