sql inner join的本质并非是取交集， 而是(可带条件地)膨胀/扩张

       先看看两张表：

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>