日常研发,链表查询用的比较多,很多查询条件都用到了子表的一些条件,比如:
学生表:student
分数表:grade
查询分数大于60分的学生…小于60分的就不要查询出来了
SELECt s.name,g.grade FROM student s LEFT JOIN grade g on s.id=g.id where g.grade>60
但有没有我们不想子查询的条件影响主表的结果的场景呢?
新建两张表商品表:
CREATE TABLE `product` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '商品名称', `factory_id` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '仓库id', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of product -- ---------------------------- INSERT INTO `product` VALUES (1, '苹果', '1a'); INSERT INTO `product` VALUES (2, '香蕉', '2a');
仓库-库存表:
CREATE TABLE `factory` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `factory_id` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '厂库唯一id', `stock` int(11) NULL DEFAULT NULL COMMENT '库存', `status` int(2) NULL DEFAULT NULL COMMENT '启用状态 0:无效 1:有效', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of factory -- ---------------------------- INSERT INTO `factory` VALUES (1, '1a', 10, 0); INSERT INTO `factory` VALUES (2, '2a', 12, 1);
当然我们这里假设一个上面只对应一个有效的仓库,为了做今天的测试
1.查询苹果的库存
SELECt p.id, p.`name`, f.stock FROM `product` p LEFT JOIN factory f ON p.factory_id = f.factory_id WHERe p.`name` = "苹果" AND f.`status` =1
结果:
显然这个结果不是我们想要的,这导致主表的数据都没有了
我们稍微修改一下:
SELECt p.id, p.`name`, f.stock FROM `product` p LEFT JOIN ( SELECt * FROM factory WHERe `status` = 1 ) f ON p.factory_id = f.factory_id WHERe p.`name` = "苹果"
结果:
这才是我们想要的
INSERT INTO `factory` VALUES (3, '1a', 15, 1);
这就从 两条库存记录当中(一条有效,一条无效),匹配上了上面对应有效库存的那一条数据了



