One common error when performing SQL joins is the “ambiguous column name” error. This type of error occurs when you attempt to join in two or more tables and more than one column of the same name appears in more than one of the tables. This short article will discuss how to resolve this error when performing SQL joins.
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:
CREATE TABLE my_table(
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:
SELECT full_name, lang, full_name, salary FROM my_table x INNER JOIN my_table_1 y ON x.id = y.id;
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.
ERROR: COLUMN reference “full_name” IS ambiguous
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:
SELECT x.full_name, lang, y.full_name, salary FROM my_table x INNER JOIN my_table_1 y ON x.id = y.id;
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.
About the author
<img data-del="avatar" data-lazy-src="https://kirelos.com/wp-content/uploads/2022/04/echo/john-150×150.png" height="112" src="data:image/svg xml,” width=”112″>
John Otieno
My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list
