多用才可以体会各个关键字的用法啊。。。
原文来自【http://bbs.php100.com/read-htm-tid-148469.html】
在用Join进行多表联合查询时，我们通常使用On来建立两个表的关系。其实还有一个更方便的关键字，那就是Using。那么这两个关键字在使用上有啥区别呢？往下看。
假设有如下两张表：
| 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) |
现在要找出这些宠物的主人是谁，我们会这么写：
| mysql> select owners.name as owner, pets.name as pet, pets.animal |
| -> from owners join 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) |
这是完全正确的。不过我们仔细看一下，pets表与owners表的关联字段名都是owners_id，这时就可以用Using来建立表之间关系了。
| 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) |
结果是完全一样的，但是写法却更简洁了。
也就是说，如果两个表的关联字段名是一样的，就可以使用Using来建立关系，简洁明了。如果不一样，只能用On了哦~