一、数据源
1.user表
2.departments表
二、作为连接条件
案例1:只给主表加is_deleted = 0
select t1.id,t1.name, t1.is_deleted as 't1.is_deleted', t2.department_id, t2.department_name,t2.is_deleted as 't2.is_deleted' from user t1 left join departments t2 on t1.department_id = t2.department_id and t1.is_deleted = 0 order BY t1.id asc;
结果分析:3条记录,因为左连接把t1都查出来了,而在t1.is_deleted = 0的这个条件下,让t1.is_deleted = 1记录的不参与连接。
因此t1.is_deleted = 1记录的右边列没有关联数据。案例2:只给副表加is_deleted = 0
select t1.id,t1.name, t1.is_deleted as 't1.is_deleted', t2.department_id, t2.department_name,t2.is_deleted as 't2.is_deleted' from user t1 left join departments t2 on t1.department_id = t2.department_id and t2.is_deleted = 0 order BY t1.id asc;
结果分析:3条记录,因为左连接把t1都查出来了,而在t2.is_deleted = 0的这个条件下,让t2.is_deleted = 1记录的不参与连接。
因此t2.is_deleted = 1记录没有出现出现在结果集中。案例3:同时给主表和副表加is_deleted = 0
select t1.id,t1.name, t1.is_deleted as 't1.is_deleted', t2.department_id, t2.department_name,t2.is_deleted as 't2.is_deleted' from user t1 left join departments t2 on t1.department_id = t2.department_id and t1.is_deleted = 0 and t2.is_deleted = 0 order BY t1.id asc;
结果分析:3条记录,因为左连接把t1都查出来了,而在’ t1.is_deleted = 0 and t2.is_deleted = 0 '的这个条件下,
让t1.is_deleted = 1和t2.is_deleted = 1记录的不参与连接。
因此只有t2.is_deleted = 1记录没有出现出现在结果集中。
三、作为筛选条件
案例1:只给主表加is_deleted = 0
select t1.id,t1.name, t1.is_deleted as 't1.is_deleted', t2.department_id, t2.department_name,t2.is_deleted as 't2.is_deleted' from user t1 left join departments t2 on t1.department_id = t2.department_id where t1.is_deleted = 0 order BY t1.id asc;
结果分析:2条记录,因为左连接把t1都查出来了,而筛选条件t1.is_deleted = 0 把 't1.is_deleted = 1’的过滤掉了。
案例2:只给副表加is_deleted = 0
select t1.id,t1.name, t1.is_deleted as 't1.is_deleted', t2.department_id, t2.department_name,t2.is_deleted as 't2.is_deleted' from user t1 left join departments t2 on t1.department_id = t2.department_id where t2.is_deleted = 0 order BY t1.id asc;
结果分析:1条记录,把t2.is_deleted = 1的两条记录过滤掉了
案例3:同时给主表和副表加is_deleted = 0
select t1.id,t1.name, t1.is_deleted as 't1.is_deleted', t2.department_id, t2.department_name,t2.is_deleted as 't2.is_deleted' from user t1 left join departments t2 on t1.department_id = t2.department_id where t1.is_deleted = 0 and t2.is_deleted = 0 order BY t1.id asc;
结果分析:1条记录,把t1.is_deleted = 1 和 t2.is_deleted = 1的两条记录过滤掉了。
四、总结
①:is_deleted = 0 作为连接条件不会让记录条数减少,记录总数取决于主表和筛选条件,和连接条件没关系。
②:is_deleted = 0 作为连接条件会和副表相关的列值为null。
③:is_deleted = 0 作为筛选条件,会让记录条数减少。
五、如果你也想跑一下
-- user表及数据
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`department_id` int(11) NULL DEFAULT NULL,
`is_deleted` smallint(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `user` VALUES (1, '张翠', 1, 1);
INSERT INTO `user` VALUES (2, '李华', 1, 0);
INSERT INTO `user` VALUES (3, '王一', 2, 0);
-- departments表及数据
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
`department_id` int(11) NOT NULL COMMENT 'id',
`department_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门名字',
`is_deleted` smallint(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`department_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `departments` VALUES (1, 'IT', 1);
INSERT INTO `departments` VALUES (2, '人力资源', 0);