Mysql—深入了解连接查询及原理（十一）

前面的例子基本上都是单表查询，那当我们查询的数据来源于多张表的时候，我们怎么办呢？这个时候我们需要用到连接查询，连接查询使用率非常高，下面将会比较深入和详细的讲解连接查询，希望大家务必掌握。

首先，我们准备一些数据：

2张表：
t_team：组表
t_employee：员工表，内部有个team_id引用组表的id。

t_team 表4条记录，如下：

t_employee 表5条记录，如下：

一、笛卡尔积

介绍连接查询之前，我们需要先了解一下笛卡尔积。

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

假如A中有m个元素，B中有n个元素，A、B笛卡尔积产生的结果有 m * n 个结果，相当于循环遍历两个集合中的元素，任意组合。

java伪代码表示如下：

for(Object eleA : A){
	for(Object eleB : B){
		System.out.print(eleA + "," + eleB);
	}
}

过程：拿A集合中的第1行，去匹配集合B中所有行，然后再那集合A中的第2行，去匹配集合B中所有的行，最后结果数量为 m * n。

SQL中笛卡尔积语法：

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

示例：


t_team表4条记录，t_employee表5条记录，笛卡尔积结果输出4*5=20条记录。

二、内连接

语法：

select 字段 from 表1 inner join 表2 on 连接条件;
或
select 字段 from 表1 join 表2 on 连接条件;
或
select 字段 from 表1,表2 [where 关联条件];

内连接相当于在笛卡尔积的基础上加上了连接条件。当没有连接条件时，内连接上升为笛卡尔积。

用Java伪代码表示：

for(Object eleA : A){
	for(Object eleB : B){
		if(连接条件是否为true){
			System.out.print(eleA + "," + eleB);
		}
	}
}

示例 1：有连接条件

需求：查询员工及所属部门

上面相当于获取了2个表的交集，查询出了两个表都有的数据。

示例 2：无条件查询

无条件内连接查询，上升为笛卡尔积，如下：

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

查询架构组的员工，3种写法

上面3种方式解说：

• 方式1：on中使用了组合条件；
• 方式2：在连接的结果之后再进行过滤，相当于先获取连接的结果，然后使用where中的条件再对连接结果进行过滤；
• 方式3：直接在where后面进行过滤。

总结：内连接建议使用第3种语法，简姐：

select 字段 from 表1,表2 [where 关联条件];

三、外连接

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

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

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

外连接分为2种：

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

1、左外连接

语法：

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

示例 1：

需求：查询所有员工信息，并显示员工所在组，如下：

上面查询出了所有员工，员工team_id = 0的，team_name为null。

示例 2：

需求：查询员工姓名、组名，返回组名不为空的记录，如下：

上面先使用内连接获取连接结果，然后再使用where对连接结果进行过滤。

---

2、右外连接

语法：

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

示例：

我们使用右外连接来实现上面左外连接实现的功能，如下：

四、表连接原理

1、准备数据


我们来写几个连接，看看效果。

示例 1：内连接

9条数据正常。

示例 2：左连接

上面的左连接第一个好理解。

第2个SQL连接条件 t1.a > 10 ，这个条件只关联了test1表，再看看结果，是否可以理解？不理解的继续向下看，我们1用java代码来实现连接查询。

第3个SQL中的连接条件 1=1 值为true，返回结果为笛卡尔积。

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

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

扩展：

表连接中还可以使用前面学习过的 group by、having、order by、limit。