sql一关联多查询时否定筛选出现的问题的解决

问题：一方关联多方查询时执行否定筛选，结果包含未通过筛选的项。

我们规定一方为父，多方为子，我们希望子未通过筛选时，结果也不出现对应的父。

 

查询部门及部门下的所有员工。

select * from department left join employee on department.id = employee.did;

查询不是员工tom所在的部门及部门下的所有员工。

第一想法是：select * from department left join employee on department.id = employee.did where employee.name != 'tom';

但是结果如下，结果仍包含部门 1 ，当mybatis等处理结果时，就会为部门 1 创建实例。这不是我们想要的。

解决1：not in 的思想（这个很慢，直接看解决2）

not in 的思想是：先查出条件句的肯定，然后外部用not in筛选。

这里就是，先找出有员工tom的部门，然后筛选不是这个部门的所有部门及员工。

select *  from department left join employee on department.id = employee.did where department.id

not in (select department.id from department left join employee on department.id = employee.did where employee.name = 'tom');

not in 筛选id的时候使用最后一个一方的id，会更快一点。

例如：a  多对一  b  一对一  c  多对一  d  多对多  e

这个时候一直到 d 都是一方 所以 not in 时使用 d 的字段筛选。即...where d.column not in (select d.column from...

解决2：not in 的思想进阶

使用left join 代替 not in。

将not in 的子查询当做一个表，对其进行关联，然后筛选这个表字段为空的。

例如还是这个例子

select department.id from department left join employee on department.id = employee.did where employee.name = 'tom';

我们设这个表为 nodepartment 列为 id。结果应该只有一行一列 ‘1’。

则主表关联nodepartment后  department.id 为 2 的行 nodepartment.id这一列为null。所以主表条件 nodepartment.id is null就可以了。

在这里就是：

select *  from department left join employee on department.id = employee.did

left join 

 (select department.id as nodepid from department left join employee on department.id = employee.did

where employee.name = 'tom')  as nodep on department.id = nodep.nodepid

where nodepid is null;