0

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?

the12
  • 2,395
  • 5
  • 19
  • 37
  • Possible duplicate of [MySQL ON vs USING?](http://stackoverflow.com/questions/11366006/mysql-on-vs-using) ...It doesn't have a deep explanation of WHY, but I think its just to make things simpler since USING is syntactic sugar anyway – Dan Dec 16 '16 at 22:32
  • It is not a duplicate. This asks the generic differences between MySQL ON vs Using. Mine asks a specific instance of why two output a different result. Although the first answer states that select * FROM INNER JOIN ON causes to be listed twice, does not explain why. – the12 Dec 16 '16 at 22:35
  • Really? You read Robert Rocha's answer and that's not satisfactory to you? – Dan Dec 16 '16 at 22:38
  • 1
    "Furthermore, the output of JOIN USING suppresses redundant columns: there is no need to print both of the matched columns, since they must have equal values. While JOIN ON produces all columns from T1 followed by all columns from T2" – Dan Dec 16 '16 at 22:40
  • Only thing is that that is for Postgres. Would that also apply for MySQL? – the12 Dec 16 '16 at 22:44

4 Answers4

4

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.

Barmar
  • 741,623
  • 53
  • 500
  • 612
1

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'.

Stephan Lechner
  • 34,891
  • 4
  • 35
  • 58
1

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.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
-5

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(); }

guessguess hoo
  • 45
  • 9