SQL查询实现排除Table 1含Table 2关联人员的部门所有行的方法

需求规则 #

需对Table1执行过滤，排除所有符合以下条件的行：

• 该行所属部门下，存在至少1名员工出现在Table2的Supervisor字段或Subordinate字段中
• 只要部门有任意1名员工满足上述匹配条件，该部门的所有行全部排除
• 最终返回结果仅包含Table1的字段，不返回任何Table2相关数据

示例校验规则 #

对应给定示例数据：

• Table1中部门1的员工Tom匹配到Table2的Supervisor字段，因此部门1的所有行全部排除
• 最终仅返回Table1中部门2的所有行

通用SQL实现 #

写法1：NOT EXISTS 实现（兼容性更好，避免NULL值导致的NOT IN异常） #

SELECT t1.*
FROM Table1 t1
WHERE NOT EXISTS (
    SELECT 1
    FROM Table1 t1_inner
    INNER JOIN Table2 t2
        ON t1_inner.Employee = t2.Supervisor
        OR t1_inner.Employee = t2.Subordinate
    WHERE t1_inner.Department = t1.Department
)

写法2：CTE标记排除部门 实现（可读性更强） #

WITH exclude_departments AS (
    SELECT DISTINCT t1.Department
    FROM Table1 t1
    WHERE EXISTS (
        SELECT 1
        FROM Table2 t2
        WHERE t1.Employee = t2.Supervisor OR t1.Employee = t2.Subordinate
    )
)
SELECT t1.*
FROM Table1 t1
WHERE t1.Department NOT IN (SELECT Department FROM exclude_departments)

内容的提问来源于stack exchange，提问作者Thomas J. Huber