how is "USING" and "ON" keywords are useful in this code

SELECT table1.PrimaryKey(Some ID), table2.nameOfSomething
FROM table1
INNER JOIN table2

Here is the part i don't get :

USING(id)

this ID is table1 foreign key, and table2 primary key

i dont really get it..

table1.ID values:

25 Rows:

row 1-5 = 1 , row 6-10 = 2 , row 11-15  = 3 , row 16-20 = 4 , row 21-25 = 5

table2.ID values :

5 Rows:

row 1 = 1 , row 2 = 2 , row 3 = 3 , row 4 = 4 , row 5 = 5

i test it and i get different result without it, how comes?

Note : Table1 contains interests, Table2 contains categories for these interests

feel free to ask for more infor开发者_开发技巧mation

---

USING specifies that a join should be performed by joining on the listed columns in both tables. That is

SELECT      t1.col1, 
            t1.col2, 
            t2.col1
FROM        table1 AS t1
INNER JOIN  table2 AS t2
USING       (col1)

is the same as

SELECT      t1.col1, 
            t1.col2, 
            t2.col1
FROM        table1 AS t1
INNER JOIN  table2 AS t2
ON          t1.col1 = t2.col1

For reference, see the MySql homepage.

---

USING is a equi-join and relies on attribute names for the same data element remaining the same between tables.

ON is more flexible: because it requires you to explicitly specify the attribute name in both tables, attribute names for the same data element can be the same or they can be different between the tables. Also, it is a theta-join, meaning that the join type can be any condition, including equality. As a result of this flexibility, ON is more verbose.

---

|  Table1:   |   Table2:           |
|  id        |   id  | table1_id   |
|   1        |    1      3         |
|   2        |    2      2         |
|   3        |    3      1         |

If you join the above two tables together with USING(id) it will match rows where the id value in Table1 are the same as the id value in Table2...

SELECT * FROM table1 JOIN table2 USING(id)

|  id   | id   table1_id  |
|   1   |  1      3       |
|   2   |  2      2       |
|   3   |  3      1       |

But, the id in Table2 might have nothing to do with the id in Table1. If that's the case, you can use ON to be specific about how you match records together...

SELECT * FROM table1 JOIN table2 ON table1.id = table2.table1_id

|  id   | id   table1_id  |
|   1   |  3      1       |
|   2   |  2      2       |
|   3   |  1      3       |

If you specify nothing at all, you match every record in one table, against every record in the other table...

SELECT * FROM table1 CROSS JOIN table2

|  id   | id   table1_id  |
|   1   |  1      3       |
|   1   |  2      2       |
|   1   |  3      1       |
|   2   |  1      3       |
|   2   |  2      2       |
|   2   |  3      1       |
|   3   |  1      3       |
|   3   |  2      2       |
|   3   |  3      1       |