How to Fix Ambiguous Column Names in MySQL

MySQL is a powerful database system that allows you to retrieve data from multiple tables using a single SQL query. While doing so, often you may get an error saying ‘ambiguous column names’. This happens if there are columns with same name in your tables used in your SQL query. In this article, we will learn how to fix ambiguous column names in MySQL.

How to Fix Ambiguous Column Names in MySQL

Let us say you have the following two tables sales(id, product, amount) and orders(id,product,amount).

If you try to select their data using the following query you will get ‘ambiguous column name’ error.

select product, amount from sales,orders where id=id;

We get the error because, when MySQL reads product and amount column names in SELECT query it does not understand which column we are talking about – are they from sales table, or are they from orders table?

There are a couple of simple ways to fix this issue. We will look at each of them one by one.

1. Using Alias

One of the simplest ways to fix this problem is to assign an alias to columns you are selecting as shown below. An alias is another name for the same column/table to avoid confusion as well as provide easier reference. You can specify an alias for a column name by mentioning it immediately after the column name after a space, or after AS keyword.

select sales.product sales_product, orders.amount orders_amount from sales, orders where sales.id=orders.id

OR

select sales.product AS sales_product, orders.amount AS orders_amount from sales, orders where sales.id=orders.id

In the above query, we use sales_product alias for sales.product column and orders_amount alias for orders.amount column. Once you have defined an alias in SELECT query, you can use it in other parts of your SQL query as well. For example, you can also use the same alias in WHERE clause of your SQL query.

select sales.product sales_product, orders.amount orders_amount from sales, orders where sales.id=orders.id and orders_amount>500

2. Using table name

Another easy way to fix the problem of ambiguous column is to specify table name before every column followed by a dot(.) and then followed by your column.

select sales.product, sales.id, orders.id, orders.amount from sales, orders where sales.id=orders.id

In the above query, we have prefixed each column name with its table name, followed by a dot(.) operator.

In this article, we have learnt how to fix ambiguous column names in MySQL.

How to Fix Ambiguous Column Names in MySQL