CODEWITHSUNDEEP
mysqljoinleft-join
Schemer
Schemer

Reputation: 1665

MySQL: Duplicate columns having the same name in join

Two questions on SO seem to be asking about different behaviour for equivalent MySQL queries. In both cases a join is being performed on tables having identical column names. This poster is asking how to eliminate duplicated columns having the same name from the result and this poster is asking how to achieve the duplication of columns having the same name in the result.

To test this I created toy tables:

mysql> describe table_1;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| col_name | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

mysql> describe table_2;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| col_name | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

I inserted the value 'value' into both tables and and executed these joins where JOIN_OP is either "join" or "left join":

mysql> select * from table_1 as t1 JOIN_OP table_2 as t2 on t1.col_name = t2.col_name;
+----------+----------+
| col_name | col_name |
+----------+----------+
| value    | value    |
+----------+----------+

This result conforms to the results in the first post. What is the difference between the two queries and the two results? Why is the second poster not seeing any duplication?

Upvotes: 0

Views: 1547

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562260

SQL:2003 rules say that if you use the USING() join condition, the redundant column is eliminated from the select-list, because it's totally clear that the columns have the same name and the same value. 

mysql> select * from table_1 as t1 JOIN table_2 as t2 USING (col_name);
+----------+
| col_name |
+----------+
| value    |
+----------+

Whereas if you use the more verbose JOIN ON syntax, and you use select *, the select-list retains a separate column from each of the joined tables, even though in this case we can tell that the value is bound to be identical.

But apparently SQL isn't smart enough to make that inference, because a condition in the ON clause could be something other than equality, e.g. it could be ON t1.col_name <> t2.col_name, therefore both columns should be retained in the select-list because they'll have different values.

MySQL 5.0.12 and later supports this standard behavior (several fixes were made to join semantics in MySQL 5.0.12, to be more standard-compliant).

You can see more discussion here: http://bugs.mysql.com/bug.php?id=6489

Upvotes: 1

Raghav
Raghav

Reputation: 11

By default MySQL will return all columns for all tables if you use *. Two columns are returned as one is for table_1 and other for table_2. You will need to explicitly enter column names in your query to retrieve them the way you want. Use the query as follows:

select t1.col_name from table_1 as t1 JOIN_OP table_2 as t2 on t1.col_name = t2.col_name;

Upvotes: 1

Related Questions