MySQL一个关于Left Join 的大坑

现有两张表 classes 和 student 两张表

DROP TABLE IF EXISTS `classes`;
CREATE TABLE `classes`  (
  `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'id',
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '班级名称',
  `year` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '年级',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
  `is_deleted` int(2) NULL DEFAULT NULL COMMENT '是否删除(0未删、1已删)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of classes
-- ----------------------------
INSERT INTO `classes` VALUES ('1', '一班', '2020级', '2021-03-08 17:46:06', NULL, 0);
INSERT INTO `classes` VALUES ('2', '二班', '2020级', '2021-08-11 09:26:46', NULL, 0);
INSERT INTO `classes` VALUES ('3', '三班', '2020级', '2021-08-11 09:26:46', NULL, 0);
INSERT INTO `classes` VALUES ('4', '四班', '2020级', '2021-08-11 09:26:46', NULL, 0);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'id',
  `account` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '账号',
  `password` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '密码',
  `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `sex` tinyint(2) NULL DEFAULT NULL COMMENT '性别',
  `id_card` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '身份证号',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
  `class_id` varbinary(32) NULL DEFAULT NULL COMMENT '班级id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', 'zhangsan', '123456', '张三', 1, '372328199905120312', '2021-03-08 17:51:51', NULL, 0x32);
INSERT INTO `student` VALUES ('2', 'lisi', '123456', '李四', 1, '372328199905120312', '2021-03-08 16:40:32', '2021-03-08 16:44:10', 0x31);
INSERT INTO `student` VALUES ('3', 'wangwu', '147852', '王五', 0, '565566565656565656', '2021-07-29 17:15:33', NULL, 0x33);
INSERT INTO `student` VALUES ('4', 'tianliu', '147852', '田六', 0, '565566565656565656', '2021-07-29 17:15:33', NULL, 0x31);

 

 

需求：查询每个班级的名称以及对应的姓名为张三学生信息

下面三条SQL语句大家觉得 结果是什么样的？

SELECT * FROM `classes` t1 LEFT JOIN student t2 ON t1.id = t2.class_id AND t2.`name` = '张三'

SELECT * FROM `classes` t1 LEFT JOIN student t2 ON t1.id = t2.class_id WHERE t2.`name` = '张三'

SELECT * FROM `classes` t1 LEFT JOIN (SELECT * FROM student WHERE `name` = '张三') t2 ON t1.id = t2.class_id

答案

sql1 ：

sql2 ：

sql3 ：

 

 

sql1和sql3是正确的，sql3是对sql1的详细解释。left join 时  对右表记录条件查询时 应在ON后面拼接 and。

在left join语句中，左表过滤必须放where条件中，右表过滤必须放on条件中。