先看看两张表:
mysql> select * from black1;
+----------+
| id |
+----------+
| zhangsan |
| lisi |
| wangwu |
+----------+
3 rows in set (0.00 sec)
mysql> select * from black2;
+----------+
| id |
+----------+
| zhangsan |
| lisi |
| wangwu |
| zhaoliu |
+----------+
4 rows in set (0.00 sec)
看下inner join的结果:
mysql> select * from (black1 inner join black2);
+----------+----------+
| id | id |
+----------+----------+
| zhangsan | zhangsan |
| lisi | zhangsan |
| wangwu | zhangsan |
| zhangsan | lisi |
| lisi | lisi |
| wangwu | lisi |
| zhangsan | wangwu |
| lisi | wangwu |
| wangwu | wangwu |
| zhangsan | zhaoliu |
| lisi | zhaoliu |
| wangwu | zhaoliu |
+----------+----------+
12 rows in set (0.00 sec)
其实, 上述括号是可以去掉的, 因为优先级本来就如此:
mysql> select * from black1 inner join black2;
+----------+----------+
| id | id |
+----------+----------+
| zhangsan | zhangsan |
| lisi | zhangsan |
| wangwu | zhangsan |
| zhangsan | lisi |
| lisi | lisi |
| wangwu | lisi |
| zhangsan | wangwu |
| lisi | wangwu |
| wangwu | wangwu |
| zhangsan | zhaoliu |
| lisi | zhaoliu |
| wangwu | zhaoliu |
+----------+----------+
12 rows in set (0.00 sec)
可见, inner join就是表的膨胀/扩张。
但是, 很多资料描述, inner join是两个圆的交集, 这是怎么回事呢? 来看看(如下括号也可以去掉):
mysql> select * from (black1 inner join black2 on black1.id=black2.id);
+----------+----------+
| id | id |
+----------+----------+
| zhangsan | zhangsan |
| lisi | lisi |
| wangwu | wangwu |
+----------+----------+
3 rows in set (0.00 sec)
可见, 通过增加条件, 可以限制膨胀/扩张。
为了方便起见, 可以对表进行临时重命名, 如下:
mysql> select * from black1 b1 inner join black2 b2 on b1.id=b2.id;
+----------+----------+
| id | id |
+----------+----------+
| zhangsan | zhangsan |
| lisi | lisi |
| wangwu | wangwu |
+----------+----------+
3 rows in set (0.00 sec)
如果只想展示black1的列, 就这样来:
mysql> select id from black1 b1 inner join black2 b2 on b1.id=b2.id;
ERROR 1052 (23000): Column 'id' in field list is ambiguous
mysql>
为什么错误呢? 因为需要指明是哪个表的id, 如下:
mysql> select b1.id from black1 b1 inner join black2 b2 on b1.id=b2.id;
+----------+
| id |
+----------+
| zhangsan |
| lisi |
| wangwu |
+----------+
3 rows in set (0.00 sec)
可见, inner join的本质并不是取交集, 而是膨胀/扩张。 只不过, 通过有条件的膨胀/扩张, 间接可以实现取交集的目的。在实际应用中, 往往是用inner join来实现取交集, 很常用。
最后, 我们再来强调, inner join是膨胀/扩张, 是可带条件的膨胀/扩张(实现交集):
mysql> select * from black1;
+----------+
| id |
+----------+
| zhangsan |
| zhangsan |
| zhangsan |
+----------+
3 rows in set (0.00 sec)
mysql> select * from black2;
+----------+
| id |
+----------+
| zhangsan |
| zhangsan |
| zhangsan |
| zhangsan |
+----------+
4 rows in set (0.00 sec)
mysql> select * from black1 b1 inner join black2 b2 on b1.id=b2.id;
+----------+----------+
| id | id |
+----------+----------+
| zhangsan | zhangsan |
| zhangsan | zhangsan |
| zhangsan | zhangsan |
| zhangsan | zhangsan |
| zhangsan | zhangsan |
| zhangsan | zhangsan |
| zhangsan | zhangsan |
| zhangsan | zhangsan |
| zhangsan | zhangsan |
| zhangsan | zhangsan |
| zhangsan | zhangsan |
| zhangsan | zhangsan |
+----------+----------+
12 rows in set (0.00 sec)
mysql>