A detailed explanation of using and on usage in MySQL

Multi-use to understand the use of various keywords ah ...

Original from "Http://bbs.php100.com/read-htm-tid-148469.html"

When using joins for multi-table union queries, we typically use on to establish a relationship of two tables. In fact, there is a more convenient keyword, that is using. So what's the difference between these two keywords in use? Look down.
Suppose you have the following two tables:

Mysql> select * FROM pets;

+---------+---------+--------+-----------+

| pets_id | Animal | name | owners_id |

+---------+---------+--------+-----------+

| 1 | Fox |         Rusty | 2 |

| 2 | Cat |         Fluffy | 2 |

| 3 | Cat |         Smudge | 3 |

| 4 | Cat |         Toffee | 3 |

| 5 | Dog |         Pig | 3 |

| 6 | Hamster |         Henry | 1 |

| 7 | Dog |         Honey | 1 |

+---------+---------+--------+-----------+

7 Rows in Set (0.00 sec)

Mysql> select * from owners;

+-----------+-------+

| owners_id | name |

+-----------+-------+

| 1 | Susie |

| 2 | Sally |

| 3 | Sarah |

+-----------+-------+

3 Rows in Set (0.00 sec)

Now to find out who the owners of these pets are, we will write:

mysql> Select Owners.name as owner, Pets.name as Pet, pets.animal

-From owners joins pets on (pets.owners_id = owners.owners_id);

+-------+--------+---------+

| Owner | Pet | Animal |

+-------+--------+---------+

| Sally | Rusty | Fox |

| Sally | Fluffy | Cat |

| Sarah | Smudge | Cat |

| Sarah | Toffee | Cat |

| Sarah | Pig | Dog |

| Susie | Henry | Hamster |

| Susie | Honey | Dog |

+-------+--------+---------+

7 Rows in Set (0.00 sec)

That's exactly right. But let's take a closer look at the pets table and the owners table's associated field names are owners_id, then you can use using to establish the relationship between the table.

mysql> Select Owners.name as owner, Pets.name as Pet, pets.animal

From owners join pets using (owners_id);

+-------+--------+---------+

| Owner | Pet | Animal |

+-------+--------+---------+

| Sally | Rusty | Fox |

| Sally | Fluffy | Cat |

| Sarah | Smudge | Cat |

| Sarah | Toffee | Cat |

| Sarah | Pig | Dog |

| Susie | Henry | Hamster |

| Susie | Honey | Dog |

+-------+--------+---------+

7 Rows in Set (0.00 sec)

The result is exactly the same, but the wording is more concise.
That is, if the associated field names of two tables are the same, you can use using to establish a relationship that is concise and straightforward. If not the same, you can only use on the OH ~