mysql - Why does SELECT * FROM table INNER JOIN..ON show the intersecting column twice?

Question

I'm using MySQL to join two different tables together. people and homes.

When I try to inner join the two together with the USING keyword, it gives back one column for the intersecting column (address) I want joined:

SELECT * FROM people INNER JOIN homes USING(address);

+---------------------+------------+-----------+----------+
| address             | first_name | last_name | city     |
+---------------------+------------+-----------+----------+
| 533 Dufferin Street | Joe        | Smith     | Toronto  |
| 421 Yonge Street    | John       | Schmidt   | New York |
| 90 Bayview Avenue   | Mary       | Poppins   | Chicago  |
| 800 Keele Street    | Joe        | Dirt      | L.A      |
+---------------------+------------+-----------+----------+

Whereas, when you inner join the two tables using ON keyword, it gives back two columns for address (intersecting column).

SELECT * from people INNER JOIN homes ON(people.address = homes.address);

+------------+-----------+---------------------+---------------------+----------+
| first_name | last_name | address             | address             | city     |
+------------+-----------+---------------------+---------------------+----------+
| Joe        | Smith     | 533 Dufferin Street | 533 Dufferin Street | Toronto  |
| John       | Schmidt   | 421 Yonge Street    | 421 Yonge Street    | New York |
| Mary       | Poppins   | 90 Bayview Avenue   | 90 Bayview Avenue   | Chicago  |
| Joe        | Dirt      | 800 Keele Street    | 800 Keele Street    | L.A      |
+------------+-----------+---------------------+---------------------+----------+

So I guess to sum up, why does USING result the column address being displayed once, vs. ON resulting in address being shown twice?

Answer 1

When you use ON people.address = home.address, it's just a coincidence that the column names are the same in both tables -- often this type of ON condition matches columns with different names. The duplicate columns are not filtered out of the result when you do this.

But when you use USING (address), the column names are required to be the same in both tables (since USING doesn't allow you to relate columns with different names). Since it's obviously redundant to have both of them, the duplicates are filtered out.