十一、深入了解连接查询及原理

当我们查询的数据来源于多张表的时候，我们需要用到连接查询，连接查询使用率非常高。

本文内容

1. 笛卡尔积

2. 内连接

3. 外连接

4. 左连接

5. 右连接

6. 表连接的原理

7. 使用java实现连接查询，加深理解

一、笛卡尔积

　　笛卡尔积简单点理解：有两个集合A和B，笛卡尔积表示A集合中的元素和B集合中的元素任意相互关联产生的所有可能的结果。　　

　　sql中笛卡尔积语法

　　select 字段 from 表1,表2[,表N];
　　或者
　　select 字段 from 表1 join 表2 [join 表N];　　

二、内连接

　　示例1：有连接条件

　　　　select t1.emp_name,t2.team_name from t_employee t1 join t_team t2 on t1.team_id = t2.id;

　　　　select t1.emp_name,t2.team_name from t_employee t1, t_team t2 where t1.team_id = t2.id;　　

　　示例2：无连接条件

　　　　无条件内连接，上升为笛卡尔积

　　　　select t1.emp_name,t2.team_name from t_employee t1 inner join t_team t2;　　

　　示例3：组合条件进行查询

　　　　select t1.emp_name,t2.team_name from t_employee t1 inner join t_team t2 on t1.team_id = t2.id and t2.team_name = '架构组';

　　　　select t1.emp_name,t2.team_name from t_employee t1 inner join t_team t2 on t1.team_id = t2.id where t2.team_name = '架构组';

　　　　select t1.emp_name,t2.team_name from t_employee t1, t_team t2 where t1.team_id = t2.id and t2.team_name = '架构组';　　

　　方式1：on中使用了组合条件。

　　方式2：在连接的结果之后再进行过滤，相当于先获取连接的结果，然后使用where中的条件再对连接结果进行过滤。

　　方式3：直接在where后面进行过滤。

　　总结

　　　　内连接建议使用第3种语法，简洁：select 字段 from 表1, 表2 [where 关联条件];

二、外连接　　

　　外连接涉及到2个表，分为：主表和从表，要查询的信息主要来自于哪个表，谁就是主表。

　　外连接查询结果为主表中所有记录。如果从表中有和它匹配的，则显示匹配的值，这部分相当于内连接查询出来的结果；如果从表中没有和它匹配的，则显示null。

　　最终：外连接查询结果 = 内连接的结果 + 主表中有的而内连接结果中没有的记录。

　　外连接分为2种：

　　　　左外链接：使用left join关键字，left join左边的是主表。

　　　　右外连接：使用right join关键字，right join右边的是主表。

三、左连接

　　select 列 from 主表 left join 从表 on 连接条件;

四、右连接

　　select 列 from 从表 right join 主表 on 连接条件;

五、理解表连接原理

　　现在我们来讨论java输出的顺序为何和sql不一致？

上面java代码中两个表的连接查询使用了嵌套循环，外循环每执行一次，内循环的表都会全部遍历一次，如果放到mysql中，就相当于内标全部扫描了一次（一次全表io读取操作），主表（外循环）如果有n条数据，那么从表就需要全表扫描n次，表的数据是存储在磁盘中，每次全表扫描都需要做io操作，io操作是最耗时间的，如果mysql按照上面的java方式实现，那效率肯定很低。

　　那mysql是如何优化的呢？

msql内部使用了一个内存缓存空间，就叫他join_buffer吧，先把外循环的数据放到join_buffer中，然后对从表进行遍历，从表中取一条数据和join_buffer的数据进行比较，然后从表中再取第2条和join_buffer数据进行比较，直到从表遍历完成，使用这方方式来减少从表的io扫描次数，当join_buffer足够大的时候，大到可以存放主表所有数据，那么从表只需要全表扫描一次（即只需要一次全表io读取操作）。

mysql中这种方式叫做Block Nested Loop。