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;