多用才可以体会各个关键字的用法啊。。。
在用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 asowner, pets.name aspet, 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 asowner, pets.name aspet, 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了哦~
原文:http://www.cnblogs.com/ishenghuo/p/3889127.html