现有两张表 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条件中。