表: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
结论:
先放出结论,不理解往下看:
- 表 A 和表 B 的连接依靠关联条件
- 主表的筛选条件,应该放置在 where 条件后
- on 后面的筛选条件是针对于关联表
- 关联表的筛选条件,如果放置在 on 后面,那么 A 和 B 的连接顺序为:B 表先按条件查询,再与 A 表连接,即先筛选再连接;如果放置在 where 后面,那么 A 和 B 的连接顺序为:A 与 B 连接后,再从连接表中筛选,即先连接再筛选
- where 后面的条件是对连接后的数据进行筛选。
验证结论:
- 主表的筛选条件应该放置在 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='盲僧'
- 关联表的筛选条件,如果放置在 on 后面,先筛选在连接
# 关联表的筛选条件放在 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
- 关联表的筛选条件,如果放置在 where 后面,先连接在筛选
# 关联表的筛选条件放在 where 后
# 主表、关联表先连接,再筛选
SELECT * from
edu_student est
left JOIN edu_score ese ON est.stu_id = ese.stu_id where ese.scores > 90
建表脚本
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');