MySQL中left join on后面的条件与where后面的条件的区别

悠悠 2022-05-28 04:14 243阅读 0赞

表：A、B  
A 字段：id，name  
B 字段：id，a\_id  
关键名词：主表、关联表、关联条件、筛选条件  
例子：

# 主表：A、关联表：B、关联条件：A.od=B.a_id、筛选条件：B.id=1
    A left join B on A.id=B.a_id and B.id=1

结论：

1.  表 A 和表 B 的连接依靠**关联条件**
2.  **主表的筛选条件，应该放置在 where 条件后**
3.  on 后面的**筛选条件**是针对于**关联表**
4.  **关联表的筛选条件**，**如果放置在 on 后面**，那么 A 和 B 的连接顺序为：**B 表先按条件查询，再与 A 表连接**，即先筛选再连接；**如果放置在 where 后面**，那么 A 和 B 的连接顺序为：**A 与 B 连接后，再从连接表中筛选**，即先连接再筛选
5.  where 后面的条件是对连接后的数据进行筛选
6.  **如果存在多个left join on，请注意on后面的条件与哪个表关联。这一条统计的SQL很重要！例如表A，B，C，A left join B on A.x = B.x left join C on A.x = C.x，B和C的都要和A建立关联，B和C之间是没有任何数据上的关系。但是 如果把A.x = C.x改成B.x = C.x，那么B和C的表数据先建立关联并过滤数据，再与A表数据进行关联，这样可能会出现数据丢失！**[详细案例，点这里查看][Link 1]

验证结论：

*  主表的筛选条件应该放置在 where 条件后，如果放在 on 后面，对主表的查询没有意义

# 主表的筛选条件在 on 后
    SELECT * from 
    	edu_student est
    left JOIN edu_score ese ON est.stu_id = ese.stu_id and est.stu_name='盲僧'
    # 区别于
    # 主表的筛选条件在 where 后
    SELECT * from 
    	edu_student est
    left JOIN edu_score ese ON est.stu_id = ese.stu_id where est.stu_name='盲僧'

![这里写图片描述][70]  
![这里写图片描述][70 1]

# 关联表的筛选条件放在 on 后面
    # 关联表先筛选再连接
    SELECT * from 
    	edu_student est
    left JOIN edu_score ese ON est.stu_id = ese.stu_id and ese.scores > 90
    # 等价于
    SELECT * FROM edu_student est LEFT JOIN ( SELECT * FROM edu_score ese WHERE ese.scores > 90 ) a ON est.stu_id = a.stu_id

![这里写图片描述][70 2]

# 关联表的筛选条件放在 where 后
    # 主表、关联表先连接，再筛选
    SELECT * from 
    	edu_student est
    left JOIN edu_score ese ON est.stu_id = ese.stu_id where ese.scores > 90

![这里写图片描述][70 3]

CREATE TABLE `edu_student` (
      `stu_id` varchar(16) NOT NULL COMMENT '学号',
      `stu_name` varchar(20) NOT NULL COMMENT '学生姓名',
      PRIMARY KEY (`stu_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';
    
    -- ----------------------------
    -- Records of edu_student
    -- ----------------------------
    INSERT INTO `edu_student` VALUES ('1001', '盲僧');
    INSERT INTO `edu_student` VALUES ('1002', '赵信');
    INSERT INTO `edu_student` VALUES ('1003', '皇子');
    INSERT INTO `edu_student` VALUES ('1004', '寒冰');
    INSERT INTO `edu_student` VALUES ('1005', '蛮王');
    INSERT INTO `edu_student` VALUES ('1006', '狐狸');

CREATE TABLE `edu_score` (
      `stu_id` varchar(16) NOT NULL COMMENT '学号',
      `course_no` varchar(20) NOT NULL COMMENT '课程编号',
      `scores` float DEFAULT NULL COMMENT '得分',
      PRIMARY KEY (`stu_id`,`course_no`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='成绩表';
    
    -- ----------------------------
    -- Records of edu_score
    -- ----------------------------
    INSERT INTO `edu_score` VALUES ('1001', 'C001', '67');
    INSERT INTO `edu_score` VALUES ('1001', 'C002', '87');
    INSERT INTO `edu_score` VALUES ('1001', 'C003', '83');
    INSERT INTO `edu_score` VALUES ('1001', 'C004', '88');
    INSERT INTO `edu_score` VALUES ('1001', 'C005', '77');
    INSERT INTO `edu_score` VALUES ('1002', 'C001', '68');
    INSERT INTO `edu_score` VALUES ('1002', 'C002', '88');
    INSERT INTO `edu_score` VALUES ('1002', 'C003', '84');
    INSERT INTO `edu_score` VALUES ('1002', 'C004', '89');
    INSERT INTO `edu_score` VALUES ('1002', 'C005', '78');
    INSERT INTO `edu_score` VALUES ('1003', 'C001', '69');
    INSERT INTO `edu_score` VALUES ('1003', 'C002', '89');
    INSERT INTO `edu_score` VALUES ('1003', 'C003', '85');
    INSERT INTO `edu_score` VALUES ('1003', 'C004', '90');
    INSERT INTO `edu_score` VALUES ('1003', 'C005', '79');
    INSERT INTO `edu_score` VALUES ('1004', 'C001', '70');
    INSERT INTO `edu_score` VALUES ('1004', 'C002', '90');
    INSERT INTO `edu_score` VALUES ('1004', 'C003', '86');
    INSERT INTO `edu_score` VALUES ('1004', 'C004', '91');
    INSERT INTO `edu_score` VALUES ('1005', 'C001', '71');
    INSERT INTO `edu_score` VALUES ('1005', 'C002', '91');
    INSERT INTO `edu_score` VALUES ('1005', 'C003', '87');
    INSERT INTO `edu_score` VALUES ('1005', 'C004', '92');
    INSERT INTO `edu_score` VALUES ('1006', 'C001', '72');
    INSERT INTO `edu_score` VALUES ('1006', 'C002', '92');
    INSERT INTO `edu_score` VALUES ('1006', 'C003', '88');
    INSERT INTO `edu_score` VALUES ('1006', 'C004', '93');

[Link 1]: https://blog.csdn.net/qq_30038111/article/details/103594735
[70]: /images/20220528/834f77dc674f46c6b6bbc36201e20067.png
[70 1]: /images/20220528/43173826ee84445dadfa3d1f3aa11a84.png
[70 2]: /images/20220528/d538bf125ece49769a63d0d86fe4afdb.png
[70 3]: /images/20220528/0931434874e54065a314d9cbfac03c31.png