• 周一. 4月 22nd, 2024

5G编程聚合网

5G时代下一个聚合的编程学习网

热门标签

3、mysql 的join原理是什么?

admin

11月 28, 2021

join分为:join,left join、right join、full join(mysql中不支持full join,需要用 left join与 reight来合并处理)

接下来我们主要关注下| left join

先用一个案例看下连接条件写在 where 和 on 中的区别

创建表:classes 班级表

CREATE TABLE `classes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

创建表:students 学生表

CREATE TABLE `students` (
  `id` int NOT NULL AUTO_INCREMENT,
  `class_id` int NOT NULL,
  `name` varchar(45) NOT NULL,
  `gender` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

上述【班级表】与【学生表】为 1对多的关系。

插入数据:

insert into classes values(1,"一班"),(2,"二班"),(3,"三班"),(4,"四班");

insert into students values(1,1,"小明","M"),(2,1,"小红","F"),(3,1,"小军","M"),(4,1,"小米","F"),
(5,2,"小白","F"),(6,2,"小兵","M"),(7,2,"小林","M"),(8,3,"小新","F"),(9,3,"小王","M"),(10,3,"小丽","F");

那么现在有两个需求:

(1)找出每个班级的名称及其对应的女同学数量

(2)找出一班的同学总数

 对于需求(1),大多数人会想象出两种sql写法

写法1:s.gender=’F’  的条件放在 on 中

select c.name,count(s.name) as num from classes c left join students s on c.id = s.class_id and s.gender='F' group by c.name;

 写法2:s.gender=’F’  的条件放在 where 里

select c.name,count(s.name) as num from classes c left join students s on c.id = s.class_id where s.gender='F' group by c.name;

对于需求(2),大多数人也会给出两种写法

写法1:c.name=’一班’ 的条件放在 on 中

select c.name,count(s.name) as num from classes c left join students s on c.id = s.class_id and c.name='一班' group by c.name;

写法2:c.name=’一班’ 的条件放在 where 里

select c.name,count(s.name) as num from classes c left join students s on c.id = s.class_id where  c.name='一班' group by c.name;

由上述两个案例可见,输出是不一样的,接下来我们来看一下  left join 的执行原理

mysql 对于 left join是采用的嵌套循环的方式来进行处理。以下面的语句为例:

SELECT * FROM LT LEFT JOIN RT ON P1(LT,RT)) WHERE P2(LT,RT)

其中,P!是 on 过滤条件,P2是 where 过滤条件。缺失都认为是 true;

该语句的执行逻辑可用以下伪代码描述:

FOR each row lt in LT {// 遍历左表的每一行
  BOOL b = FALSE;
  FOR each row rt in RT such that P1(lt, rt) {// 遍历右表每一行,找到满足join条件的行
    IF P2(lt, rt) {//满足 where 过滤条件
      t:=lt||rt;//合并行,输出该行
    }
    b=TRUE;// lt在RT中有对应的行
  }
  IF (!b) { // 遍历完RT,发现lt在RT中没有有对应的行,则尝试用null补一行
    IF P2(lt,NULL) {// 补上null后满足 where 过滤条件
      t:=lt||NULL; // 输出lt和null补上的行
    }         
  }
}

当然,实际情况中MySQL会使用buffer的方式进行优化,减少行比较次数,不过这不影响关键的执行流程

从这个伪代码我们能看出两点

(1)如果想对右表进行限制,则一定要在on条件中进行,若在where中进行则可能导致数据缺失,导致左表在右表中无匹配行的行在最终结果中不出现,违背了我们对left join的理解。

因为对左表无右表匹配行的行而言,遍历右表后b=FALSE,所以会尝试用NULL补齐右表,但是此时我们的P2对右表行进行了限制,NULL若不满足P2(NULL一般都不会满足限制条件,除非IS NULL这种),则不会加入最终的结果中,导致结果缺失。

(2)如果没有where条件,无论on条件对左表进行怎样的限制,左表的每一行都至少会有一行的合成结果,对左表行而言,若右表若没有对应的行,则右表遍历结束后b=FALSE,会用一行NULL来生成数据,而这个数据是多余的。所以对左表进行过滤必须用where。

 

总结::在left join语句中,左表过滤必须放where条件中,右表过滤必须放on条件中,这样结果才能不多不少,刚刚好。

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注