MySQL: Duplicate columns having the same name in join

Question

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?

I just recreated both tables to include the "unique" constraint for values of col_name and I get the same result for both join and left join. Could you clarify, please? — Schemer
– Schemer, Commented Sep 1, 2014 at 21:27

Raghav · Accepted Answer · 2014-09-01 21:28:55Z

1

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;

answered Sep 1, 2014 at 21:28

Raghav

113 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Schemer Over a year ago

This also removes the duplication. But it doesn't explain why the second poster did not get any duplication.

Bill Karwin · Accepted Answer · 2014-09-01 21:49:32Z

1

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

answered Sep 1, 2014 at 21:49

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

2 Comments

Schemer Over a year ago

That certainly removes the duplication. But, since the second poster was not using this syntax, why did s/he not see any duplication?

Bill Karwin Over a year ago

The OP in that question said, "when I fetch the result of the query" -- I would guess they are fetching into an associative array in their app, and that naturally forces columns of the same name to merge into one array element, one overwriting the other. SQL allows result sets to have more than one column with the same column name, but associative arrays don't.

Collectives™ on Stack Overflow

MySQL: Duplicate columns having the same name in join

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related