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.

Answer 2

see MySQL Join Syntax, especially in section 'Join Processing Changes in MySQL 5.0.12'. USING or NATURAL JOIN treat the join attribute always as the same such that one of both is redundant. Different from previous behaviour, since 5.0.12 'the redundant column is eliminated and the column order is correct according to standard SQL'.

Answer 3

In this query

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

there are two adresses in the results, namely people.address and homes.address. In case of an inner join the two have the same value. In case of another join type (outer or cross join) they wouldn't.

In the other query

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

you merge the two to a single unqualified address. What you gain is that the join criteria is simpler to write (but with more complex queries there come situations when you need a qualifier and then you go back to ON again).

USING is especially helpful with multiple full outer joins*:

select col, a.colx, b.coly, c.colz
from a
full outer join b using (col)
full outer join c using (col);

versus

select coalesce(a.col,b.col,c.col) as col, a.colx, b.coly, c.colz
from a
full outer join b on b.col = a.col
full outer join c on c.col = coalesce(a.col,b.col);

*) Full outer joins are not supported in MySQL though.

Answer 4

in your controller $data['address'] = $this->yourmodel->getAdd();

Try to use this in your model.

public function getAdd() { $this->db-> select ( 'p.first_name' , 'p.last_name', a.address as add'); $this->db->join( 'homes a' , a.address = p.address'); $query = $this->db-> get ('people p'); $query-> return ->result_array(); }